PASSING VARIABLES TO SUB REPORTS

[crystalclear01]

All,
CR 9 ORCL 8
SCENARIO:
100 Patients total:
20 of 100 Seen by Doctor A only
30 of 100 Seen by Doctor B only
50 of 100 Seen by both Doctor A and B together.
(yes the records are duplicated in the DB, once showing with Dr. A and once showing with Dr. B)

Requirements:
1. Credit each doctor with patients seen by themselves.
2. Credit doctor B with all patients seen by both A & B

Results desired:
Report for Doctor A must contain ONLY 20 patients
Report for Doctor B should contain 80 patients

There are many other details which I'll spare you. The crux of this situation is that I'm able to accurately report for Doctor B by sorting out the 20 patients in his report by filtering Doctor A out in my select statement.

My question is how can I do this for Dr. A?
If I filter out Dr. B then Dr A gets undue credit for his 20 + 50 = 70. He should only get credit for 20.

So far I have been able to generate an array at the end of Dr. A's report containing all the charts he's seen.(all 80 of them)

I would like to pass each one of the elements of this array to a sub -or main???- report to filter them out in the selection criteria.

Can this be done?
Does this make sense?
Am I complicating things?

Deadline looming...Aaargh!!

Help, Help, Help...

 

[synapsevampire]

You've overcomplicating this.

You might create your own query to base the A doctor main report on (use the add command to paste in your sql), or better yet an oracle View which has something like:

select patients from table where patientid not in
(select patientid from table where doctorid = 'B')

The reverse could be used for doctor b in a subreport within the report footer, or you might use a union to combine them into a single rowset if you need to do some reporting on the combined data set.

Try to let the database always do the heavy lifting

-k

 

[lbass]

Or, for the long way around, if this is meant to be one report on both doctors, you could group on {table.doctor} and then create a formula {@AorB}:

if {table.doctor} = "B" then 100000 else
if {table.doctor} = "A" then 1 else 0

Then use variables to accumulate the sums:

//{@accum} to be placed in the group header or footer:
whileprintingrecords;
numbervar A;
numbervar B;

if sum({@AorB},{table.doctor}) < 100000 then
A := A + 1;
if sum({@AorB},{table.doctor}) >= 100000 then
B := B + 1;

//{@displayA} to be placed in the report footer:
whileprintingrecords;
numbervar A;

//{@displayB} to be placed in the report footer:
whileprintingrecords;
numbervar B;

-LB

 

[crystalclear01]

Sirs you are both very good, but I had not mentioned that the reports need to be displayed in crosstab form with percentages for each category and... there are many doctors A and many doctors B so I can't just group on Dr. A and Dr B...

I have the Crosstab with conditional color formatting and all that: it looks really cool, it's just that I have a small issue and that's that the numbers don't jive...

So back to my initial Q:
Can I pass the elements of my array (about 450 Chart_Id's) to another -sub or main report to filter them out... something like {table.chart_id} not in [123456, 23568, 77895, etc]

PLEASE tell me it can be done in a simple way, I've downloaded and looked intensely at the sample of passing array values from Crystal, however they only pass one value at a time. I need to pass all of em prior to fetching the data in the record select statement

Thanks.