Dear Safo2000:
I have to do these types of reports all the time to show Master - Detail releated Tables. What I have discovered works really well without subreports and without unions is as follows.
Insert a group in the order of display on what is unique (primary key) for each joined table. Make the join type from the Main Parent table to the *child* tables a left outer join so that data for the *main* table will show if there are no children on a particular join.
Suppress the details section.
Suppress Group Footers for each group except Group 1.
Place the data to show for each table in it's associated group. If you need header columns, Do an insert section below and put column headers in the A section and Data in the B Section. Create your column headers for that section's data and then Format Section/Suppress No Drill Down X-2 Formula Editor (This Group Table.Primary Key <> minimum(This Group Table.Primary Key,Prior Group Table.Primary key)
For each child group, Format the Section, and choose the Suppress No Drill Down x-2 formula editor and enter:
isnull(This Group Table.primary key)
or
(Prior Group table.primary key) <> maximum(Prior Group Table table.primary key, Parent Table.Primary key)
What this does is suppress printing of the group until the group above it finishes printing:
This results in exactly what you requested:
GH1A (Column Header Maintenance Request - if desired Prints at start of Manintenace Request)
GH1B (Data for Maintenance Request)
GH2A (Column Header for Tool Code - Prints Once for Each Maintenance Request, if Desired)
GH2B (Tool Code Data for Maintenance Request) Repeats as necessary until all Tool Code Data for this maintenance request is done
GH3A (Column Header for taskcode. Prints Once for Each Maintenance Request, if Desired)
GH3B (Data for TaskCode - Repeats for each taskcode related to Maintenance Request)
GH4A Data for Invcode - Repeats for each invcode related to Maintenance Request)
GH4B (Data for InvCode - Repeats for each taskcode related to Maintenance Request)
GH5A(Data for LaborCode - Repeats for each LaborCode related to Maintenance Request)
GH5B(Data for LaborCode - Repeats for each LaborCode related to Maintenance Request)
That is all you have to do. If you need to show totals and such, just play with suppression formula to make data print in an additonal section at the end of a group ... as desired.
Once again, I have to do these all the time for example Change Request, Change History, Change Approvers, Change Assessments and so on to gather all the information that relates to a change. If you just keep in mind that you link from the Main Table to all the children as a left outer join on the foreign key that relates the child to the main table and then Group Each Table on each Table's primary key, and then use suppression formulas as indicated above ... it works flawlessly.
It does take a little time to set up, but no subreports and now messing with the SQL statement.
Best Regards,
ro
Rosemary Lieberman
rosemary@microflo.com,
Microflo provides expert consulting on MagicTSD and Crystal Reports.