Distinct Count of field between subreports 1

[Alundil]

Ok yall - this one might be a doozt (if at all possible).

CR9
Oracle 8i

I have a main report (no data or tables or select statements) that contains two subreports.

The first subreport list a selection of case #s ({V_NI_RECORD.CASE_CONFIRMATION_NUM}) along with some other fields.

The second subreport lists a selection of case #s ({V_SCH_RECORD.CASE_CONFIRMATION_NUM}) along with some other fields.

Each subreport is pulling data based on the {*.PROCEDURE_DATE_DT} being within the range of currentdate - 7 to currentdate.

There are occasions when the two subreports will both list a duplicate case # from time to time. I am trying to get a count of the unique Case #s from both subreports and display as a total on the main subreport.



-- Andy

 

[lbass]

You would have to have the subreports in group sections (not the report header or footer) so that the main report could accumulate the count. Let's say you create a group on {table.caseconfirmationnumber} in the main report, and that you place the first subreport in the group header, and the second in the report footer. In each subreport create a formula {@sharedno}:

whileprintingrecords;
shared stringvar confirmno := totext({V_NI_RECORD.CASE_CONFIRMATION_NUM},"0000");//add as many 0's as
//the maximum number of digits in the number

In the second subreport {@sharedno} would look like:

whileprintingrecords;
shared stringvar confirmno := totext({V_SCH_RECORD.CASE_CONFIRMATION_NUM},"0000");

These subreport formulas must be placed on the subreport canvas. Then in the main report, create a formula {@confirmno} and place it both in a group header_b section (below the first subreport which is in GH_a, and a group footer_b section which is below the subreport in GF_a:

whileprintingrecords;
shared stringvar confirmno;
numbervar counter;
stringvar x;

if instr(x, confirmno) = 0 then
(x := x + confirmno + ", ";
counter := counter + 1);
counter;

Then create a display formula for the report footer:

whileprintingrecords;
numbervar counter;

-LB

 

[Alundil]

LB -

Thank you for that. In reading it, I understand exactly what you have outlined. However, it also made me realize that I did not ask the corect question. Never fear though, I am sure that I'll wind up using your solution sooner rather than later

On to the question (restated): The scenario is the same. Current results look something like this:
(first subreport - Schd_Rec)
--Case_# Name Schd_Case_Start
--9999996 Jane Doe 02/05/2005
--9999997 John Doe 05/05/2005
--9999998 John Smith 05/06/2005
--9999999 Jane Smith 05/07/2005
(second subreport - NI_Rec)
--9999991 John Johnson 02/08/2005
--9999999 Jane Smith 05/07/2005

Is there a way that I can hide/suppress/select out those case # records that appear in both the 1st and 2nd subreport?



-- Andy

 

[lbass]

Are you saying that you would want Jane Smith not to appear in EITHER subreport? Or that you would only want her to show up in the first one?

-LB

 

[Alundil]

that is correct - I'd like to only see her one time (if possible)



-- Andy

 

[lbass]

In the first subreport, create this formula:

//{@accum} to be placed in the detail section;
whileprintingrecords;
shared stringvar caseno;

if instr(caseno, totext({table.case#},0,"")) = 0 then
caseno := caseno + totext({table.case#},0,"")+", ";

Then in the second subreport (which must be placed in a section below the one in which the first subreport is located), go to the section expert->details->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar caseno;

instr(caseno, totext({table.case#},0,"")) > 0

-LB

 

[Alundil]

ok - so in these formulas, I'd need to place the subreports in group header sections can I leave them in the Report Footer A and B respectively (as I currently have them)?

Thanks



-- Andy

 

[lbass]

Yes, you can leave them in separate report footer sections, since all formulas are being used within the subreports.

-LB

 

[Alundil]

LB -- sorry I didn't get back to you sooner. Just wanted to let you know that your solution worked out very well indeed.



-- Andy