Distinct Count Problem

[Former Member]

I have a problem where a distinct count of key field is coming back 1 higher than my test sql statement. I am trying to get a total number of records based on several criteria and every time it has one more than it should.

Here is one of my formulas

Here is TOTAL_PENDING_APPLICATION_SW

if {WR_MAIN.WATER_CODE}='S' and {WR_MAIN.RECORD_TYPE}='A' and {WR_MAIN.STATUS_CODE}='N' then {WR_MAIN.PERMIT_NUMBER} else ''

Here is SUM_TOTAL_PENDING_APPLICATION_SW

DistinctCount({@Total_Pending_Applications_SW})

This comes back with 161 and the SQL comes back with 160. The same can be said for other criteria and formulas.

So, I created a sql expression field in crystal that uses my SQL and it comes up with the correct value. I guess, I could use those instead of formulas, but that has to be a huge bug with formulas, or I am not doing something correct.

(Select Distinct(count(*)) from wr_main where water_code='S' and record_type='A' and status_code='N')

It almost seems like Crystal is starting with 1 instead of 0 when doing a DistinctCount

[Former Member · Accepted solution]

If you use the running total expert to do a distinct count on the permit number field with the following condition ({WR_MAIN.WATER_CODE}='S' and {WR_MAIN.RECORD_TYPE}='A' and {WR_MAIN.STATUS_CODE}='N') and evaluate on each record and reset never what is the result?

[Former Member · Accepted solution]

The total is correct. I probably should be using running totals, but they were giving me a problem at first and another forum suggested using the formula way.

Thanks

Kevin

[Former Member · Accepted solution]

I suspect the reason the formula is not the same is due to conditional suppression, group selection or a null value somewhere; just a thought.

[former_member184995]

Hi Kevin,

I moved your thread to the Crystal Reports Design forum as they typically have more knowledge of these matters.

Jason