Distinct Count counting nulls 1

[MaumeeScott]

I need to do a distinct count on a formula using the distinct count summary function. The problem is that there are potential nulls in this formula so that my distinct count is off by 1 where there are nulls.
(formula looks like this: if field x = Y then field z)
I can't use a conditional running total (which works fine) because I need the result to print in the group header. I can print sub-totals and summaries in the group header, but not the running totals. Thank you.

 

[synapsevampire]

If you want to eliminate the nulls from the report entirely, go into the Report->Edit Record Selction->Formula and use:

not(isnull({table.field}))

If you need the nulls in the report, then the report is performing correctly, a null is a distinct value.

Consider using an additional field to determine if there are any nulls, and subtracting 1 if there are for that group:

details formula:
if isnull({ITEM_FIND_OBSRV.UPDATETIME}) then
1
else
0

Right click it and select insert summary->sum

Create a group header formula which references both of your standard summary fields to produce the corrected count, as in:

if Sum ({@nl}, {AUDITED_ITEM.AUDIT_NM}) > 0 then
Count ({AUDITED_ITEM.ITEM_IDENTIFIER},{AUDITED_ITEM.AUDIT_NM})-1
else
Count ({AUDITED_ITEM.ITEM_IDENTIFIER},{AUDITED_ITEM.AUDIT_NM})

Replace the field names with yours.

-k