Sum of a Distinct Count? 1

[JD5241]

On XI and SQL database...
I have a column on a report that is a distinct count, and I need it to add up at the end of the report. Just dragging a copy of the field (DistinctCount(table.field)) into the footer isn't the answer (at least it's not working in this case), and you can't do a sum on a count, i.e., Sum(DistinctCount(table.field)).

An example of what I'm looking for:
Record Count
1 5
2 5
3 10

Total: 20

Is there an easy way to get that column to add up? Thanks.

"I have no idea what's going on." -Towelie

 

[jdemmi]

Have you tried using a running total?

-- Jason
"It's Just Ones and Zeros

 

[JD5241]

Yes, the Running Total expert won't let me put the Distinct Count field in, says it's an "Invalid field selection"... and if I try to use a running total to get a distinct count of the field, it's dropping numbers off.

"I have no idea what's going on." -Towelie

 

[IdoMillet]

it's not working in this case" is king of vague. If different froups have the same value, do you wish to have it counted multiple times? If not, then a grand distinctcount should work.

Otherwise, you would need to use a global variable and a formula (placed in the Group Footer) that adds the group value to the variable. Another formula can then present the variable value in the report footer.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[glenninflorida]

As IdoMillet mentioned:

When using aggregate functions such as COUNT, SUM, ..., I have had to create a global variable to capture the running total like this:

//in your distinctcount formula
shared numberVar RunningTotal;
numberVar MyResult;

MyResult := (DistinctCount(table.field));

RunningTotal := RunningTotal + MyResult;
MyResult;


Then, create a Grand Total formula:

//MyCountGrandTotal
shared numberVar RunningTotal;

-Glenn
"Go Gators!

 

[JD5241]

I had tried that earlier and couldn't get it to work, but checking it against your example, I had made it more complicated than it should have been... I just added the group to the field being counted: distinctcount(table.field),{@group}) and it seems to be working fine now. Thank you!

"I have no idea what's going on." -Towelie

 

[JD5241]

I'm posting this here rather than start a new thread...

How would something like this work for subtotals and grand totals? It works fine using it in one group with one total, but I'm having difficulty figuring out the grand total and subtotals for 2 groups. I've tried putting the running total formula and grand total formula in the footer with different groups specified in the formula, but the numbers didn't add up.

Example, grouped by store then records:

[Store 1]
Records Count
1 3
2 5
3 2
Store 1 SubTotal: 10

[Store 2]
Records Count
3 4
4 1
5 5
Store 2 SubTotal: 10

Grand Total: 20

"I have no idea what's going on." -Towelie

 

[lbass]

You have to create a separate variable for the subtotal you want displayed, and you need a reset formula for it. You also need different display formulas. If you only have two groups and you want one group subtotal (based on the inner group) and the grand total, then you should have these formulas:

//[@resetgrp} to be placed in the store group header (GH#1):
whileprintingrecords;
numbervar storetot := 0;

//{@accum} to be placed in the group header or footer (GH#2 or GF#2) where you have the distinctcount:
whileprintingrecords;
numbervar storetot := storetot + distinctcount({table.amt},{table.group#2field});
numbervar grtot := grtot + distinctcount({table.amt},{table.group#2field});
storetot

//{@displaystoretot} to be placed in the GF#1 footer:
whileprintingrecords;
numbervar storetot;

//{@displaygrtot} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;

-LB

 

[JD5241]

That's a keeper! I've been able to use that example in a couple other reports. Thanks!

"I have no idea what's going on." -Towelie