Report Design
 Crystal Reports Forum : Crystal Reports .NET 2003 : Report Design
Message Icon Topic: counting unique records in a group Post Reply Post New Topic
Author Message
prd7825
Newbie
Newbie


Joined: 18 Nov 2009
Online Status: Offline
Posts: 2
Quote prd7825 Replybullet Topic: counting unique records in a group
    Posted: 18 Nov 2009 at 12:07pm
Can anyone help with counting distinct records within 2 groups?  Here's the problem I'm trying to resolve:
 
- I have one group, grouped by {BoardPayments.ContractID}
- I have a second group within {BoardPayments.ContractID} grouped by {BoardPayments.RateID}
- I want to get a total count of Distinct {BoardPayments.ClientID} within each {BoardPayments.RateID} group and then get a total count of those distinct {BoardPayments.ClientID} for the {BoardPayments.ContractID} group.
 
If I try and get a distinct count of {BoardPayments.ClientID} in the Footer of the {BoardPayments.ContractID} group, the count may be wrong because there may be a {BoardPayments.ClientID} in 2 different {BoardPayments.RateID} group so I'd want to count both of them.
 
I've tried to use Running Totals and Formulas but I keep getting errors like "This field cannot be summarized".  Here's what I've done so far:
 
Formula 1
WhilePrintingRecords;
Shared NumberVar sumClientID := 0;
 
Formula 2
WhilePrintingRecords;
NumberVar sumClientID;
sumClientID := DistinctCount ({BoardPayments.ClientID}, {BoardPayments.RateID})
 
Formula 3
***This is where I need the help. I've tried doing a Count of Formula 2 but that doesn't work.  Any ideas?********
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 18 Nov 2009 at 5:20pm
Instead of counting just your ClientID in the ContractID group, do a distinct count of the concatenation of ClientID and RateID.  If Client ID and RateID are both string fields, you can do this:
 
DistinctCount({BoardPayments.ClientID}+{BoardPayments.RateID}, {BoardPayments.ContractID}))
 
If they're numeric, you'll want to convert them to strings first because you want to concatenate the values, not add them together.
 
-Dell
IP IP Logged
prd7825
Newbie
Newbie


Joined: 18 Nov 2009
Online Status: Offline
Posts: 2
Quote prd7825 Replybullet Posted: 18 Nov 2009 at 8:41pm
Amazing...such a creative yet simple solution, and the best part is, it works perfectly!  Thank you so much for the response! Smile
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.035 seconds.