How to Create Master Child Reports

Question

How to Create Master Child Reports

Vikas Parikh

Old Hand

Points: 360
More actions
December 26, 2005 at 3:49 am

#169947

Hi,
I have to Stored Procedures:
1) Returns the all the master records of SalesOrders
2) Return detail records of all master records returned by first.
Now I did not find how to define a relationship in these two datasets from two diff. queries.
I want to create one report multiple sales orders.
How can I achieve it?
Please suggest.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Severson SSC Enthusiast Points: 121 More actions · Answer 1

I to have pulled my hair out trying to find a way to create a master child report. My work around is as follows. I use a cursor to capture the master record, write the record to a temporary table, then pass the id to a nested cursor calling the detail, write the detail to the same temporary table. I add an extra Counter column in the temporary table which is nothing more than a row counter, (1…2…3…4…) used when selecting all the records from the temporary table to keep them in sequential order.

When all records are processed, select the temporary table and order by the Counter column, the master records will be listed with the detail for each of the master records in the recordset. I’ve had to create a couple of reports using this method.

Stuart Johnson Mr or Mrs. 500 Points: 552 More actions · Answer 2

The way I did this is to set up the report with the master stored Proc as the data source. Then use a subreport for the child report. You can set up the subreport to take a parameter from the master data set. This is then passed to the data set for the child report. To make this work efficiently I had to pre process the data for the child report so that they system was only doing a select from a table for the child data. If your data sets are not too large you won't have to do this.

Stuart

Vikas Parikh Old Hand Points: 360 More actions · Answer 3

Problem in using sub-report is that for each master record sub report and its stored procedure is executed once.

For a larger number of records it is very slow.

any other way to achieve it?

Stuart Johnson Mr or Mrs. 500 Points: 552 More actions · Answer 4

I had to pre process the child data so that the subreport is only doing a select out of a table instead of processing the result set. This made it very efficient. I don't know of any other good way to do this.