Cross-tabs/Count/Distinct Count/Totals

[PrograMist]

Need assistance on the following issue please...

In my report I have the following categories (fields):

machinename (distinct count = 20,223)
OS for each machine (count = 20, 223)
processor (count = 20,283) -- the count of processors is always 60 items more, because some machines have more than one processor. So, it also actually changes the count on OS field to 20,283 (since I can not use the distinct count for OS field)

I have the data grouped by operating system and then by processor. Then, I am trying to create cross-tabs for each group. The cross-tab for processor looks good. However, the cross-tab for the operating system reflects incorrect totals. It needs to have counts as 1 OS per 1 distinct machine name and instead it's counting 1 OS per 1 processor.

Would someone have any suggestions on how I can modify this report so that each cross-tab reflects the right totals for each category? Sorry to say, I can not break it down into 2 reports -- it has to be all in one... Plus, I have other fields with the same problem... But, I think, if I could just get some help on this example, I would figure out the rest.

Thank you so much,

PrograMist

 

[JohnEck]

This is going to sound stupid but do two distinct counts of Machine name. Since each Machine has one OS if you do two counts and then alias the second one as an OS count you will get the results you want.

 

[synapsevampire]

Version of Crystal? Where are the cross-tabs?

You shouldn't need groups at all, use 2 different cross-tabs in separate report footers.

Not knowing how you've designed your report, it's hard for anyone to describe how to modfy it, but unless you have another requirement in this report which you haven't shared, there's no reason to have any Groups.

-k

 

[PrograMist]

Thank you both so much for replying. I do need groups in order to create charts. The design of my report is something like this:

first, I pull from database table "machine name", distinct count of which is 20,223

next to it, I pull "OS" field from another linked table -- count is the same, because there is only one OS per machine

then, when I pull "processor" field into the report (from the third linked table), the count of this category is 20,283 and it changes the count of OS records respectively (since I am not doing the distinct count on OS)

Then, i create a cross tab for processors, which looks something like this: Total
1.6GHz to 1.9GHz 5,094
2GHz and above 1,297
450MHz to 595MHz 3,494
600MHz to 975MHz 6,672
976MHz to 1.5MHz 1,845
Below 450MHz Standard 1,882
Total 20,284

and one for the OS:

Cat. Total
Microsoft Windows 2000 Professional 13,120
Microsoft Windows 95 20
Microsoft Windows 98 31
Microsoft Windows NT Workstation 7,060
Microsoft Windows XP Home Edition 2
Microsoft Windows XP Professional 51
Total 20,284

The totals in the OS table are incorrect, because it is counting records as they printed on the report: 1 OS next to each 1 processor (1OS per processor). And instead I would like it to reflect the true number of 1 OS per 1 machine, which there are only 20,224 (60 units less than processor units)

Does this make any sense? could I e-mail my report to someone who would like to help? I am using Crystal Reports, the Developer's edition, version 9. I am new to this software and would appreciate very much any assistance or hints that would lead me in the right direction...

Thank you,

PrograMist