Using Main report as a container report 2

[Alundil]

Hi all -

Oracle 8i
CR 9

I am trying to insert two subreports into a main report where the main report is pretty much a 'container' only.

Example:

Subreport #1 "subrpt 24hours out"
Here is the SQL statement for this subreport - designed to pull records where the scheduled procedure date is the 'currentdate + 1'.

 SELECT "V_SCH_RECORD"."CANCELLED_TF", "V_SCH_RECORD"."SERVICE", "V_SCH_RECORD"."ROOM_NAME", "V_SCH_RECORD"."PROCEDURE_DATE_DT", "V_SCH_RECORD"."CASE_CONFIRMATION_NUM", "V_SCH_RECORD"."SURGEON", "V_SCH_RECORD"."MSP_SECOND_SURGEON", "V_SCH_RECORD"."MSP_THIRD_SURGEON", "V_SCH_RECORD"."MSP_SECOND_SURGEON_SERVICE", "V_SCH_RECORD"."MSP_THIRD_SURGEON_SERVICE", "V_SCH_RECORD"."MSP_FOURTH_SURGEON", "V_SCH_RECORD"."MSP_FOURTH_SURGEON_SERVICE", "V_SCH_RECORD"."MSP_FIFTH_SURGEON", "V_SCH_RECORD"."MSP_FIFTH_SURGEON_SERVICE", "V_SCH_RECORD"."MSP_SIXTH_SURGEON", "V_SCH_RECORD"."MSP_SIXTH_SURGEON_SERVICE"
 FROM   "SISI"."V_SCH_RECORD" "V_SCH_RECORD"
 WHERE  "V_SCH_RECORD"."CANCELLED_TF"='0' AND ("V_SCH_RECORD"."PROCEDURE_DATE_DT">=TO_DATE ('08-02-2005 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "V_SCH_RECORD"."PROCEDURE_DATE_DT"<TO_DATE ('09-02-2005 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND "V_SCH_RECORD"."ROOM_NAME" LIKE 'ACB%'

This returns the desired result when run separately.

Subreport #2: "subrpt 48hours out"
Here is the SQL statement for this subreport - designed to pull records where the scheduled procedure date is between 'currentdate + 2' and 'currentdate + 3'.

SELECT "V_SCH_RECORD"."CANCELLED_TF", "V_SCH_RECORD"."SERVICE", "V_SCH_RECORD"."ROOM_NAME", "V_SCH_RECORD"."PROCEDURE_DATE_DT", "V_SCH_RECORD"."CASE_CONFIRMATION_NUM", "V_SCH_RECORD"."SURGEON", "V_SCH_RECORD"."MSP_SECOND_SURGEON", "V_SCH_RECORD"."MSP_THIRD_SURGEON", "V_SCH_RECORD"."MSP_SECOND_SURGEON_SERVICE", "V_SCH_RECORD"."MSP_THIRD_SURGEON_SERVICE", "V_SCH_RECORD"."MSP_FOURTH_SURGEON", "V_SCH_RECORD"."MSP_FOURTH_SURGEON_SERVICE", "V_SCH_RECORD"."MSP_FIFTH_SURGEON", "V_SCH_RECORD"."MSP_FIFTH_SURGEON_SERVICE", "V_SCH_RECORD"."MSP_SIXTH_SURGEON", "V_SCH_RECORD"."MSP_SIXTH_SURGEON_SERVICE"
 FROM   "SISI"."V_SCH_RECORD" "V_SCH_RECORD"
 WHERE  "V_SCH_RECORD"."CANCELLED_TF"='0' AND ("V_SCH_RECORD"."PROCEDURE_DATE_DT">=TO_DATE ('09-02-2005 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "V_SCH_RECORD"."PROCEDURE_DATE_DT"<TO_DATE ('11-02-2005 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND "V_SCH_RECORD"."ROOM_NAME" LIKE 'ACB%'

This also runs correctly if sun as a separate report.


The problem arises when I try and insert them in a main report. I am sure that there is a simple step that I am missing or just placing them in the wrong report area.

I am linking the main and sub reports on 'V_SCH_RECORD.PROCEDURE_DATE_DT'.

So far, I've tried placing them in the detail section (details a, and details b). No records come through in the subreport.

Tried creating 'fake' groups (ie: group1 would be based off of a formula:

whilereadingrecords;
1

and group2 would be based off of

whilereadingrecords;
2

Once this has been done, I tried inserting the subreports into the group headers - both would then return 0 records even though it would show '"lots" of read records.

Once those attempts didn't work correctly, I tried changing the formulas that I was using for the groups to the following:
group 1 and subrpt 24hours out

{V_SCH_RECORD.PROCEDURE_DATE_DT} = currentdate + 1

group 2 and subrpt 48hours out

{V_SCH_RECORD.PROCEDURE_DATE_DT} in currentdate + 2 to currentdate + 3

Same issue as before - I see the records being read but no records being diplayed

Any help is appreciated. Thank you.



-- Andy

 

[synapsevampire]

If the main report is returning no rows, and you link to a subreport, then naturally the subreports will return nothing as there's nothing to link to.

You speak of the subreports, but say nothing about the main report...

What's in the record selection formulas for the main and subreports?

If you don't need to link subreports, don't do so, linking will slow things down. And try having the main report return at least one row.

-k

 

[Alundil]

The subreports themselves do not need to be linked and aren't linked. I was under the asumption that they needed to link to something in the main report at least. Is that correct?

Also, if I need to have something in the main report -- I am at a loss as to what that something might be as all of the info that I am trying to display is currently in the subreport itself.



-- Andy

 

[lbass]

No, they don't have to be linked. Try placing the subreports in separate report header sections without any links to the main report.

-LB

 

[synapsevampire]

Are you saying that the subreports are or are not linked to the main report, one statement says that they don't need to be and aren't, the next says that you thought they had to be.

Perhaps you meant that the subreports aren't linked to each other or some such.

Place the subreports in the report header or report footer, and you may have to make sure that the main report returns at least one row (you don't need to display the row).

-k

 

[Alundil]

ok - I've tried a few changes and can now get the first subreport to display, but the second subreport does not display -- just blank. I've included code snippets and screenshots to help yall visualize the problem.

Here is what I've done: (the subreports have been inserted into Report Header A and Report Header B

Main Report layout

Main report Select Expert statement

{V_SCH_RECORD.PROCEDURE_DATE_DT} in currentdate + 1 to currentdate + 2

Main Report SQL statement

24hour subrpt layout

24hour subrpt Select Expert statement

{V_SCH_RECORD.CANCELLED_TF} = "0" and
{V_SCH_RECORD.PROCEDURE_DATE_DT} = currentdate + 1 and
{V_SCH_RECORD.PROCEDURE_DATE_DT} = {?Pm-V_SCH_RECORD.PROCEDURE_DATE_DT}

24hour subrpt SQL statement

48hour subrpt layout is almost identical to that of the 24hour subreport
48hour subrpt Select Expert statement

{V_SCH_RECORD.CANCELLED_TF} = "0" and
{V_SCH_RECORD.PROCEDURE_DATE_DT} = currentdate + 2 and
{V_SCH_RECORD.PROCEDURE_DATE_DT} = {?Pm-V_SCH_RECORD.PROCEDURE_DATE_DT}

48hour subrpt SQL statement

Here are the subreport links for both the 24 and 48 hour versions
24 hours

48 hours

Hopefully this helps explain the problem a bit better than I did in previous posts

Thanks,



-- Andy

 

[lbass]

Remove the subreport links and remove the record selection from the main report. If you want to limit the records in the subreports themselves, do it in the record selection formula area of each subreport.

-LB

 

[Alundil]

lbass - did that

only now, nothing at all is displayed. Zero records read and nothing displayed.

If I go back and remove the table from the database expert of the main report, then I cannot even 'run'/'refresh'/'preview' the report.



-- Andy

 

[lbass]

Try going to file->report options and uncheck "Suppress Printing if no records".

-LB

 

[Alundil]

ok, that doesn't seem to have done anything either --

Both subreport run and display records correctly if they are run by themselves

the problem arises if I try and add them to the main report



-- Andy

 

[lbass]

Then add a field to the main report and suppress it. You do not need any selection criteria in the main report or any subreport links.

-LB

 

[Alundil]

LB

This is the craziest thing -- I created a new completely blank report and re-added the two subreports to two new report header sections and everything worked just fine. Almost like there was some sort of cached sql statement or select statement left over in the main report even though I could not see anything.

Very strange -- but anyways, your suggestion about placing the subs in different report header sections without any main report select statements just as you said it should.

Thank you very much......



-- Andy