Counting Null in Distinct Count 1

[jwrjwr54]

I have an if-then formula:

if certain selection criteria are met
then {project_name}

The proper results are displayed, but a Null value is included. The field {project_name} does not have a null value in the database.

1.
2. ABC Project
3. DEF Project

Distinct count of this formula shows 3 instead of 2 (counting the blank - not sure if this is a null; I've tried to exclude by isnull, "", and " " - none of these work.

Any ideas on how to do a distinct count and not count the null? I've searched, but can not find a solution.
Thanks

 

[lbass]

Nulls are not counted by distinctcount, so first create a formula {@null} by opening and closing a new formula without entering anything. Then create a formula like this:

if trim({table.projectname}) = "" or
<not your other criteria> then
{@null} else
{table.projectname}

Then insert a distinctcount on this formula.

-LB

 

[jwrjwr54]

Perfect - many thanks