A way around the subreport? 2

[shaunhubbs]

Hi all,

I am using CR9 to create a report that contains the same information twice, just sorted differently the second time through. I have a main report and then a subreport to accomplish this, but it runs very slowly as each time it hits the subreport in the group footer it implements the entire subreport and only shows the information that is linked. But if I have 200 main reports, I then seem to have 200 full instances of the subreport, but again, only maybe one of the pages of the whole report showing based on the linking.

Does anyone know a smarter way to do this so I don't have 200 instances of the subreport being run fully? Any way that you can figure getting all of the information into the subreport?

Thanks in advance for any help.

- Shaun

 

[lbass]

You could use a parameter to determine the sort for the field on the main report so that the data only appears once in the desired order (with no subreport).

-LB

 

[shaunhubbs]

I need the data to appear twice in the same report. The first time through it is sorted by location of the item. The second time through it is first split into categories and then within the categories is sorted by description of the item.

- Shaun

 

[Turkbear]

Hi,
Sound like you need 2 reports...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[shaunhubbs]

That is my thought exactly. =D

What I was attempting to do was have the two reports automatically collate. That is really the whole point. Since they are similar I was hoping to have both reports roll off of the printer at the same time and didn't want anyone to have to manually put them together.

Anyone else think of anything?

- Shaun

 

[lbass]

If this is the entire report showing twice, then the subreport should be unlinked and placed in the report footer--or at least only linked on parameters.

-LB

 

[shaunhubbs]

That would then print all of my 1st report and then all of my 2nd report. The whole point of the report is to collate when they are the same surgery. The first one is the pick list that they use to pick the things to put on a cart to send to the operating room and the second one is for the nurses to then fill out what they actually used for costing. We could have them print off the pick list and then the worksheet and then have someone collate them each and every day, but we are trying to avoid that as there are hundreds of pieces of paper daily. =(

Anyway, thanks for the suggestion, but not really what we are looking to do.

I just can't seem to figure out how to not run the entire report each time it hits the specific GROUP FOOTER (where the SUPREPORT is stored).

Essentially I was hoping that there was a way to do this:
1) Have the pick list run once
2) Have the worksheet run once
3) Collate based on the surgery

But again, each time it hits that subreport (the worksheet) it seemingly runs the whole thing and only displays the corresponding info...?

- Shaun

 

[lbass]

The entire subreport isn't running each time. The link creates a parameter in the record selection of the subreport so that it is limiting the subreport to that particular criterion. However, the subreport does execute for each group.

-LB

 

[shaunhubbs]

That sounds like exactly why it is running so slow as it is in the group footer. I was thinking of trying to strategically put information in groups to get a dulpicate report, but it doesn't seem possible.

 

[rnd]

You can join the same table with an alias to the first table and link them with the alternative field that you are using to link the sub report ( I think that is what you are doing - I could be wrong in making that assumption). Make the main information as the Group and get the data from the first table. For the linked information get the data from the aliased table and put them in the details section. You can sort the details the way you want to and all those details will be pertaining to that group (surgery for example). The surgery can be in its own alpha sort or what ever and the details could be in its own patient name alpha sort or what ever you need.
It won't work unless there is an eligible alternate field to join and you use that alternate field.
Example:
Table1.SurgeryID = Table2.SurgerID (wrong join.Same info duplicated)
Table1.PatientID = Table2.PatientID (may be correct join)

 

[lbass]

Another option would to use "Add Command" as your datasource and create two datasets by using a Union All, with one field to distinguish the two sets, as in:

Select
1 as dataset, table.`surgery`, table.`location`, table.`category`,table.`itemdesc`
From
`table` table
Union All
Select
2 as dataset, table.`surgery`, table.`location`, table.`category`,table.`itemdesc`
From
`table` table

Then you could insert a group on surgery, followed by a group on dataset. Then create a formula {@grp3}:

if {command.dataset} = 1 then {table.location} else
if {command.dataset} = 2 then {table.category}

Insert a group on {@grp3} and then create a sort formula {@sort}:

if {command.dataset} = 1 then "" else
if {command.dataset} = 2 then {table.itemdesc}

Then add this as your sort field (report->sort records). This probably will be a faster approach.

-LB

 

[shaunhubbs]

Good feedback. Thanks guys. I will look into using those methods to speed up the report after my much needed vacation. =D

- Shaun