Conditional Distinct Count in Crystal Reports

Question

I have a dataset like this:

ID    PersonID    ClassID    Attended    Converted
1     1           1          1           0
2     1           1          1           1
3     1           1          1           1
4     2           1          1           1
5     3           2          0           0
6     3           2          1           1
7     4           2          1           0

I'm building a report that groups by ClassID (actually I'm using a parameter that allows grouping on a few different cols, but for simplicity here, I'm just using ClassID). I need to do a calculation in each group footer. In order to do the calculation, I need to count records with PersonIDs unique to that group. The catch is, in one case, these records also need to match a criteria. EG:

X = [Count of records where Converted = 1 with distinct PersonID]
Y = [Count of records where Attended = 1]

Then I need to display the quotient as a percentage:

(X/Y)*100

So the final report would look something like this:

ID    PersonID    Attended    Converted
CLASS 1 GROUP
1     1           1           0
2     1           1           1
3     1           1           1
4     2           1           1

Percent= 2/4 = 50%

CLASS 2 GROUP
5     3           0           0
6     3           1           1
7     4           1           0

Percent= 1/2 = 50%

Notice in Class 1 Group, there are 3 records with Converted = 1 but 'X' (the numerator) is equal to 2 because of the duplicate PersonID. How can I calculate this in Crystal Reports?

Solution

I had to create a few different formulas to make this work with the help of this site.

First I created a function called fNull as suggested by that site, that is just blank. I was wondering if just typing null in its place would do the job but didn't get to testing it. Next I created formulas to evaluate if a row was attended and if a row was converted.

fTrialAttended:

//Allows row to be counted if AttendedTrial is true
if {ConversionData.AttendedTrial} = true
then CStr({ConversionData.PersonID})
else {@fNull}

fTrialsConverted:

//Allows row to be counted if Converted is true
if {ConversionData.Converted} = true
then CStr({ConversionData.PersonID})
else {@fNull}

Note that I'm returning the PersonID if attended or converted is true. This lets me do the distinct count in the next formula (X from the original question):

fX:

DistinctCount({@fTrialsConverted}, {ConversionData.ClassID})

This is placed in the group footer. Again remember @fTrialsConverted is returning the PersonID of trials converted (or fNull, which won't be counted). One thing I don't understand is why I had to explicitly include the group by field (ClassID) if it's in the group footer, but I did or it would count the total across all groups. Next, Y was just a straight up count.

fY:

//Counts the number of trials attended in the group
Count({@fTrialsAttended}, {ConversionData.ClassID})

And finally a formula to calculate the percentage:

if {@fY} = 0 then 0
else ({@fX}/{@fY})*100

The last thing I'll share is I wanted to also calculate the total across all groups in the report footer. Counting total Y was easy, it's the same as the fY formula except you leave out the group by parameter. Counting total X was trickier because I need the sum of the X from each group and Crystal can't sum another sum. So I updated my X formula to also keep a running total in a global variable:

fX:

//Counts the number of converted trials in the group, distinct to a personID
whileprintingrecords;

Local NumberVar numConverted := DistinctCount({@fTrialsConverted}, {@fGroupBy});

global NumberVar rtConverted := rtConverted + numConverted;    //Add to global running total

numConverted;   //Return this value

Now I can use rtConverted in the footer for the calculation. This lead to just one other bewildering thing that took me a couple hours to figure out. rtConverted was not being treated as a global variable until I explicitly added the global keyword, despite all the documentation I've seen saying global is the default. Once I figured that out, it all worked great.

Comments

[Hariharan Anbazhagan]

Grouping is wrong if value of ClassID is "1" in 5th Row. It should be "2" I hope !!

[xr280xr]

Good catch. I've updated the source data. Thank you

[MPaul]

+1 for a nicely detailed solution! Thanks for saving me hours of headaches

[xr280xr]

@MPaul This was a pretty specific problem. I'm glad there was someone else it could help!

[Anonymous]

Thanks a lot for this solution!