Sum values for distinct count 2

[MarkfromChase]

I am (sadly enough) having a hard time figuring out how to create a formula that allows the summation of distinct count returned. If I do a sum of distinct count in the report footer it is distinct count amongst all of the values across the group rather than the total of distinct values for the report. In the formula editor I tried the following:

Sum (DistinctCount ({OPERATOR_ACTION_LOG_MIRROR.Account_id}))

And it gives the error message:

"The Summary / Running total field could not be created"

I am using version 8.0. This seems very simple (I could create this in MS Access in seconds with a formula), but doesn't want to work in crystal. Please help, thanks!

 

[vidru]

Just put the same formula in the Report Footer:
[tt]
DistinctCount ({OPERATOR_ACTION_LOG_MIRROR.Account_id}))
[/tt]
-dave

 

[synapsevampire]

Why is there a SUM involved?

Omit the sum, and just use:

DistinctCount ({OPERATOR_ACTION_LOG_MIRROR.Account_id})

To get the grand total.

To get it per group, use:

Sum (DistinctCount ({OPERATOR_ACTION_LOG_MIRROR.Account_id}), {table.groupfiled})

You can also just right click the OPERATOR_ACTION_LOG_MIRROR.Account_id field in the details and select insert->grand total->DistinctCount

This can also be done for group summaries.

-k

 

[MarkfromChase]

Thanks both synaspsevampire and vidru for your quick replies. I am still unfortunately not getting the report to work the way I want-the methods provided I have already attempted and of course produce the same results.

For example, the data looks as below:

group name Tot Worked Tot Unique Worked
Q1 147 95
Q2 3 3
Q3 167 77

Totals 317 155

I want the # in Tot Unique worked to be 175 rather than the 155-it is 155 due to a record being found in more than one group. How do I get the field that has the 155 in it to do just a calculation to add the summary operations already completed for each group? In MS ACCESS it would just be count of count and would work fine.

 

[synapsevampire]

OK, I understand.

Try the following:

In the Group Footer use:

whileprintingrecords;
numbervar SumOfDistinct:=SumOfDistinct+distinctcount({table.field},{table.group})

In the report footer use:
whileprintingrecords;
numbervar SumOfDistinct

-k

 

[lbass]

You can use a variable to do this. Create two formulas:

//{@accum} to be placed in the group header or footer:
whileprintingrecords;
numbervar sumdistcnt := sumdistcnt +
distinctcount({OPERATOR_ACTION_LOG_MIRROR.Account_id},{table.groupfield});

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

-LB

 

[MarkfromChase]

I would like to thank synapsevampire for the two formulas-they worked perfect!! You my friend are a Crystal Reporting GOD!!! Great help, thanks again!!

Thanks to lbass for the info too, synapsevampire beat you to the punch, but the code was the same and therefore would also qualify you too as a Crystal Reporting GOD!

Thanks again everyone!