Help with Distinct Count

[goduke]

I have a simple report where I first group on System and then I group on Environment. I use a distinct count to get a distinct count of all the accounts within each Environment. Then at the end of the report, I am trying to get a grand (distinct) count, but my numbers dont match up because I have some accounts that can be in both groups. So it only counts that account once for the entire report. Here is an example:

System: PeopleSoft
DEV Distinct Count = 2
CERT Distinct Count = 1
PROD Distinct Count = 3
System: Essbase
DEV Distinct Count = 4
CERT Distinct Count = 1

Total = 10

I am trying to get 11, but I get 10 because I have an account that is in both PeopleSoft DEV and Essbase DEV. How can I use a formula to just add up the distinct counts so that I will get 11?

 

[lbass]

You didn't show the account field, so your example isn't quite as clear as it could be, but the solution is to use a variable:

//{@accum} to be placed in an environment group section:
whileprintingrecords;
numbervar dcnt := dcnt + distinctcount({table.acct},{table.environment});

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar dcnt;

-LB

 

[goduke]

I didn’t show the account field because it is in the details section, and I have it suppressed so I can just see totals for the groups. I see now where that might have been clearer. Sorry about that...

Nevertheless, the variables worked great! Thank you so much for the quick reply and the solution!

 

[jello212]

LBass,

Is it possible to use the above variables in a crosstab?

 

[lbass]

Please start a new thread and explain in detail what you are trying to do, including the row, column, and summary fields you plan for the crosstab, and why you think you will need to use variables.

-LB

 

[jello212]

I was doing something very similar to the initial poster in that I was getting distinct counts for each subset and wanted the total to be a total of those distinct counts.

For example:
Jan Feb Mar Total
Dr Smith 10 20 30 60
Dr Jones 11 21 31 63

But instead of showing 60 and 63, respectively, the totals may show something like 49 and 55 because several patients were seen in multiple months and were only being counted 1x in the total column.

I have since solved this by counting a concatenation of the patient_id and the month. But, I saw this thread and was wondering if the formulas would work. I've been able to incorporate them into standard reports but not cross tab reports.

 

[fsreport]

hi
create a cross tab report
collumn are month
row doc name
and field to summarise is the number value do a sum

fsreport