wrong distinct count return from running total

[fsreport]

Hi
CR 9.0
MS SQL
i have check in the report option
select distinct records
select distinct records for browsing
this report that contains 2 group only
gr1 channel
gr2 UU_Ref

my records return from the report sql is a sample

Channel***SCHTYPE****SCHNOTES****SCHSTART******Status
IAD*******Conference**ABC*******2004/04/24******C
IAD*******Conference**ABC*******2004/04/24******C
IAD*******Conference**ABCD******2004/04/24******C
IAD*******Conference**ABCD******2004/04/24******C
IAD*******Conference**ABCDF*****2004/04/24******C


my running total is like so
********************************
field to summarize: SCHNOTES
type of summary: Distinct count

Evaluate: @ formula
not({CALSCHED.SCHTYPE} in ["BranchMeeting","BreakfastMeeting","D","U","One-on-One","FARefer","FAEnt","FATraining","ClientEntertainment",
"ClientSeminar"])
and {CALSCHED.SCHSTART} in {?Start_Date} to {?End_Date} and
{CALSCHED.SCHSTATUS} = {?Activity_Status}

Reset on change of group UU_REF
**********************
for some reason it shows 2 but looking at the record return
there 3

Cant figure whats the problem

fsreport

 

[fsreport]

More info supplied
found out there are error in database table
and they corrected it


Now there saying the evaluate formula in the running total must be like this

Evaluate: @ formula
not({CALSCHED.SCHTYPE} in ["BranchMeeting","BreakfastMeeting","D","U","One-on-One","FARefer","FAEnt","FATraining","ClientEntertainment",
"ClientSeminar"])
and {CALSCHED.SCHSTART} in {?Start_Date} to {?End_Date} and
{CALSCHED.SCHSTATUS} = {?Activity_Status}

and
{SCHREF} must be the same value
and
{SCHNOTES} must the the same value

i cant say
{SCHREF} = {SCHREF}
{SCHNOTES} = {SCHNOTES}
since i have no clue is there exactly the same value



this a tough one :-(




fsreport

 

[Madawc]

If counts etc. don't add up, the chances are you've got a null value somewhere. You might need

not isull({CALSCHED.SCHTYPE})

If that's not it, I suggest you separate the tests as formula fields, and make them 'boolians', e.g.

not({CALSCHED.SCHTYPE} in ["BranchMeeting","BreakfastMeeting","D","U","One-on-One","FARefer","FAEnt","FATraining","ClientEntertainment",
"ClientSeminar"])

Put this beside the data, see if it says True or False where you expect it to.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10