Distinct Count

[tkaz]

I am using CR 11 and SQL Server 2005 DB.

I am working on a report that looks at today, yesterday, WTD, MTD, YTD, and TODate volumes by plant. On today's date, I need to count the number of loads shipped.

Seemed simple enough to say if RUNDATE = CurrentDate then RTTicket.ID, slap into the details and do a distinct count for two groups: product and plant.

It works, but is counting the null as a distinct because the date criteria is in the period YeartoDate.

Does anyone have a suggestion?

 

[lbass]

Create a formula {@null} by opening and saving a new formula without entering anything. Then change your formulas to add the {@null} default as in:

if RUNDATE = CurrentDate then
{RTTicket.ID} else
tonumber({@null})

If the ID field is a string, remove the tonumber().

-LB

 

[tkaz]

Thank you so much! It appears to have done the trick!