Distinct Count of an If-Then-Else Formula

Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide

Free Crystal Reports formula examples from KenHamady.com

To learn the techniques used in these formulas get:

The Expert's Guide to Crystal Reports Formulas
and
Expert Techniques for Crystal Reports I, II & III

Distinct Count of an If-Then-Else Formula:

If you want to know how many records meet a criteria, you use an If-Then-Else formula as described in formula 10 and sum the formula.

However, if you need to do a distinct count of a field in these records, you run into a problem. The records that don't meet the criteria have an else value that also gets counted. The result is that your distinct count is usually one higher than it should be. Some CR users assume that if they simply skip the else line of the formula, that CR will use a NULL value as the else, and then the NULL wouldn't be counted. However, CR does not use an else of NULL by default. It will use the default value for the data type of the THEN value. That usually means and empty string value ("") or a zero for numerics. But here is a way to fool CR into returning a NULL value as your ELSE value:

1) Create a formula called @null and save it without any contents

2) Create an if-then-else formula:

                         if {criteria.field} = "X"   //whatever your criteria is
                         then {table.ID}            //the field you are distinct counting
                         else {@null}               // the formula field you created above
                                      // if your THEN field is a number use Val ({@null}) or ToNumber ({@null}) as your ELSE

3) Do distinct counts of the if-then-else formula.

(Thanks to Jacques Sauvageau of Wilson Banwell PROACT for suggesting the VAL / TONUMBER. Saves a couple of steps over my original method. )