Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

wrong distinct count return from running total

Status
Not open for further replies.

fsreport

Programmer
Mar 23, 2007
128
US
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
 
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
 
If counts etc. don't add up, the chances are you've got a null value somewhere. You might need
Code:
not isull({CALSCHED.SCHTYPE})

If that's not it, I suggest you separate the tests as formula fields, and make them 'boolians', e.g.
Code:
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.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top