Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Distinct Count Sum Post Reply Post New Topic
Author Message
ppie
Newbie
Newbie


Joined: 18 Dec 2011
Location: United States
Online Status: Offline
Posts: 5
Quote ppie Replybullet Topic: Distinct Count Sum
    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
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
ppie
Newbie
Newbie


Joined: 18 Dec 2011
Location: United States
Online Status: Offline
Posts: 5
Quote ppie Replybullet 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
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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.
 
 
IP IP Logged
ppie
Newbie
Newbie


Joined: 18 Dec 2011
Location: United States
Online Status: Offline
Posts: 5
Quote ppie Replybullet 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
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.