Print Page | Close Window

Distinct Count Sum

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15184
Printed Date: 01 May 2024 at 12:39pm


Topic: Distinct Count Sum
Posted By: ppie
Subject: Distinct Count Sum
Date Posted: 18 Dec 2011 at 2:15pm

Summing a Distinct Count Field

Using Crystal 11 I am trying to get a total of my grouping which I have a distinct count of computer names but in each group I have detail records with the same computer name.  I am grouping on a field name Application.  Distinct count working fine for each group but need to sum all distinct counts for each group in report footer. in the example below I am looking for help in how to get the result of 4 in report footer.

 

Example

Distinct Count    Name                                    Application = ABC

2                              Computer_Name_1

                                Computer_Name_2

                                Computer_Name_1

                                Computer_Name_1

                                                                                Application = CBA

2                              Computer_Name_3

                                Computer_Name_4

                                Computer_Name_3

                                Computer_Name_3

Total I am looking for is 4



-------------
Thank you


Replies:
Posted By: lockwelle
Date Posted: 19 Dec 2011 at 3:24am
shared variables:
3 formulas (standard)
reset (group header):
shared numbervar aTot:=0;
"" //hide the number 0 from displaying
 
increment (subgroup header or footer):
shared numbervar aTot := aTot + CountDistinct({table.field}, {group});
"" //hide the running total
 
display (footer):
shared numbervar aTot;
aTot
 
You might be able to use running totals, but that is DBlank's forte, and I don't know if they work with the aggregate function.
 
HTH

Posted By: ppie
Date Posted: 19 Dec 2011 at 3:53am
New to Crystal and I am not sure what you are saying.  Can you be more detail.

-------------
Thank you

Posted By: lockwelle
Date Posted: 19 Dec 2011 at 4:03am
CR can't sum aggregrate functions (like distictCount or countDistinct [I never remember the exact name] or Avg), so you need to do it yourself, and that is what I posted.  There is a feature in CR that is called Running Totals which does pretty much the same as the shared variables, but I don't really know how to use them...DBlank (another poster) is the expert in them, but I don't know if they will handle this situation (again because I don't use them).
 
Other than that...nope, that's pretty much it.
syntax of formulas and where to place them is what the original response had.
 
 

Posted By: ppie
Date Posted: 19 Dec 2011 at 6:57am
Getting 0 for the results.  I have done the following:
Created 4 Formula Fields
 
Fields         Formulas for each 
atot             Blank 
display         shared numbervar atot;
                   atot
increment     shared numbervar atot :=atot + distinctcount     ({MissingPatches_Clients.Computername}, {Patches.Description});
 
reset            shared numbervar atot:=0;
 
I placed Reset in Group Heade #1 Patches.Description - A
I placed increment in Group Footer #1 plus I tried in Report Footer a
I placed Display in Report Footer a
 
I am getting the value of 0 for Display.  Anu idea?


-------------
Thank you

Posted By: lockwelle
Date Posted: 19 Dec 2011 at 11:14am
don't you hate when everything looks correct, but you get the wrong value ;)
 
here's what I do, I build 'helper' formulas, that do a part of a formula and display it.  So, I would try making a formula that just looks at the distinctcount and making sure that it is displaying correctly.
 
Often you will find that what you thought was happening wasn't...especially if you hide the output.

HTH


Print Page | Close Window