My First Cross Tab 2

[stussy]

Hi

I need a report that will show the distinct user id's that have logged into a computer in a given period. I've tried various types of cross tab, but they keep returning a horrendous number of records, and I'm sure it could be neater. In essence it is returning a row for every user, whether or not they logged into that particular pc on that day. what I would like to see is the users that logged in that day.

3 fields are being used - date, user ID and computer. The ideal view would be like this:

[Date1]

Computer... PC1 PC2 PC3 PC4 PC5

Users logged in 1 4 7 8 9
2 5 10 11 12
3
[Date2]

Computer... PC1 PC2 PC3 PC4 PC5

Users logged in 4 2 7 14 9
1 5 6 11 10
3

Any ideas gratefully received!

Cheers

Mike

 

[beanbrain]

You might try grouping by date and putting the cross-tab in the group footer. This will give you a distinct cross-tab for each date. When setting up the date be sure to pay particular attention to the "section will be printed by" drop down at the end of the group dialog box. When grouping by date you can choose to goup by;
[ol]
[li]day[/li]
[li]week[/li]
[li]two weeks[/li]
[li]half month[/li]
[li]month[/li]
[li]quarter[/li]
[li]half year[/li]
[/ol]
and so on.

 

[lbass]

I agree. Put the crosstab in a group header or footer for a date group. Use {table.computer} as the column field and don't use a row field. Add distinctcount of {table.userID} as your summary field.

-LB

 

[stussy]

Cheers for the replies, and I've realised I didn't explain myself 100% clearly. The crosstab per date side is working perfectly, thanks for that.

The numbers for 'staff logged in' above are actually User ID's - what we need to see are the individual numbers that have logged into that machine that day. Therefore for any day there could be one person logged into machine 1, 3 into machine 2, and 10 into machine 3. Some days machine 2 may not be used, so there would be no entries for it. At the moment I have a line for each staff id, then a 1 or a 0 if they logged in, which is what makes the report longer than necessary. The neatest way would show each machine number at the top, then just the id's that have logged in that day as a list underneath.

Hope this is clearer and cheers again!

 

[lbass]

A crosstab might or might not be the best solution. Try using no row field and for the summary, use "NthSmallest". Add this summary multiple times up to the maximum number of users per day, incrementing N by 1 each time. The problem with this approach is that space will be reserved for each summary.

See my suggestion in thread767-945738 for an alternative approach.

-LB

 

[stussy]

Thanks again lbass. The nth smallest is a very neat solution, and has fixed it 99%. One more request if I may! I've added the nth 10 times, which should cover me for most days' worth of staff. However where there are less than ten logons, it repeats the last ID until it reaches ten. I've tried the standard 'supress if duplicate' to no avail. Is there any easier way of tidying this last bit?

Cheers

Mike

 

[stussy]

Ahh. Correction, it's not working quite right. The nth smallest does not seem to return the right values. On one PC, I can see the logon numbers 42, 172, 97 and 53. However crystal shows ten entries showing 172. Obviously not the smallest of that range! Anything I could be doing wrong?

 

[lbass]

I think you are picking up on multiple logins of the same ID. You can check this in the detail section. Not sure why the largest number would appear first--are you sure you didn't choose NthLargest? To correct the duplication, you could try going to Database->check "Select Distinct Records".

-LB

 

[stussy]

Genius. If I could give two stars I would. Thanks, you've made my life a lot easier, which I guess is why we're all here in the first place.

Cheers

Mike

 

[beanbrain]

Stussy:

There I gave LBass anther star for you. He's good isn't he?

 

[stussy]

He's exceptional - very helpful!