DISTINCT COUNT TOTALS AND DISTINCT COUNT GRAND TOTALS

[a112]

HI, I HAVE A PROBLEM WITH CRYSTAL REPORTS 8. I HAVE A REPORT THAT GIVES ME A DISTINCT COUNT ON A UNIQUE NUMBER.
IT GIVES ME A SUBTOTAL PER GROUP AND A GRANDTOTAL. THE TROUBLE IS WHEN I CHECK THE GRANDTOTAL OF THE SUBTOTALS AGAINST THE REPORT THEY ARE DIFFERENT. CAN YOU GIVE ME AN IDEA OF WHAT'S HAPPENING AND A POSSIBLE SOLUTION TO REMEDY THIS. THE FIELD I'M WORKING WITH IS CALCID. IT TRACKS THE NUMBER OF OCCURENCES OF THAT NUMBER. Thanks a112

 

[kenhamady]

First, you should know that a Crystal grand total is not a total of the subtotals. It is a total of all of the details. That is why these can be different.

If the field is truly unique, they would be the same. However, it appears that you might have duplicates. I will assume that the Grand total is lower than the sum of the subtotals. That means that on ID is in more than one group.

To prove this, do a Grand Total that is a regular count of that column. You will notice that the count is not the same as the distinct count. It would be if the field was unique.

To find the duplicate, change the group to be on the ID, create a count, and select groups with a count >1. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[rodbac]

Also- not so loud... we can hear you just fine...

 

[a112]

Hi, Once again, I want to thank you for your help with answering my question. I need to ask one small question,
can you add DistinctCount keyword to the evaluate formula
so that the formula can look at the distinct values only of the field called Indexnumber and total only those distinct values.

The field called indexnumber has duplicate values. To sum it up, I need the Evaluate formula to perform running totals ( only once) on distinct values only. Also if its possible, can you have a formula to perform subtotals. If this is possible, what would be the formula. Below is the formulas I'm working with.As always, you assistance is greatly appreciated. thanks.

a112

Initialize formula

WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal:= 0

Evaluate formula

WhilePrintingRecords;
NumberVar RunningTotal;
If (OnFirstRecord or {Indexnumber}<>
Previous ({Indexnumber}) then
RunningTotal:= RunningTotal + 1;

Display formula

WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal;

 

[kenhamady]

You don't need to write running total formulas. CR8 has an automatic running total feature that will do distinct counts, evaluate when the field changes, and reset once per group to make a subtotal. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/