Counting problem 1

[FishKiller]

I need help with distinct counting or something like it. I have a sql2000 view pulling data into crystal report 9 latest sp. The data is a list of examdate,examid,patientid,resourceid

I currently have it grouped by examdate > resource > patientid then the details is the exam id.

I need to perform a total "special exam" count.

EX: Date=10/11/2005
Resource=R1
PatientID=1
details= 78813
78816CTW

PatientID=2
details= 78813

PatientID=3
details= 78816CT
78813

All three have 78813 in the result this should be labeled and counted as "1- PET"

the first one listed is "78813 and "78816CTW" which should be labeled as "1- PETCTW"

Patientid-3 is showing "78816CT and "78813" which should be labeled as "1- PETCT"

The total returned would look like this

PET PETCT PETCTW
1 1 1


Please help

 

[lbass]

You could create a formula like the following {@exams}:

if {table.examID} = '78813' then 1 else
if {table.examID} = '78816CTW' then 1000 else
if {table.examID} = '78816CT' then 100000

Then create a second formula {@ptlabel} to be placed in the PatientID group footer:

whileprintingrecords;
numbervar PET;
numbervar PETCTW;
numbervar PETCT;

if sum({@exams},{table.patientID}) in 1 to 1000 then
PET := PET + 1;
if sum({@exams},{table.patientID}) in 1000 to 99999 and
remainder(sum({@exams},{table.patientID]),1000) <> 0 then
PETCTW := PETCTW + 1;
if sum({@exams},{table.patientID}) > 100000 and
remainder(sum({@exams},{table.patientID]),100000) in 1 to 999 then
PETCT := PETCT + 1;

In whichever group footer (or report footer) you want to evaluate this, you would place these display formulas:

//{@displPET}:
whileprintingrecords;
numbervar PET;

//{@displPETCTW}:
whileprintingrecords;
numbervar PETCTW;

//{@displPETCT}:
whileprintingrecords;
numbervar PETCT;

You would need to have reset formulas in the corresponding group header that set each variable to zero. If you want these subtotals at each of the different group levels, you will need to use different variables for each group level.

This assumes that you really meant that a result containing only 78813 should be labeled "1-PET", since that was what you showed in your sample results.

-LB

 

[FishKiller]

It works like a charm!!! Can I use the {@displpetct} formulas which are in the footer in a crosstab or charts? I have added 15 different combinations using your formula sample AGAIN THANKYOU!!!

 

[lbass]

Not easily. Take a look at:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_charting_print_time_formulas.pdf

...for charting. As for the crosstab, you could create a manual crosstab.

-LB