counting unique records in a group
Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
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=8400
Printed Date: 05 May 2024 at 4:19am
Topic: counting unique records in a group
Posted By: prd7825
Subject: counting unique records in a group
Date 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?********
|
Replies:
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: prd7825
Date 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! 
|