sql server reporting services: Parent/Child Record Display Problem -- sql server reporting services

You're in the

sql server reporting services

group:

Parent/Child Record Display Problem

Mike Harbinger
7/23/2005 4:16:33 PM

sql server reporting services:

I am having trouble correctly showing multiple child tables for a sub-parent
table as follows-

A customer can have many Orders
An Order can have many Parts
An Order can also have many Invoices

This is what I want to see:

Cust Nbr
101

Order Nbr
101-201

Part Nbr
201-301
201-302

Inv Nbr
201-401
201-402

I have tried using 2 groups and 2 details which did not work and then tried
4 groups arranged as follows:

Cust Nbr = Group1
Order Nbr = Group2
Part Nbr = Group3
Inv Nbr = Group4

This is what I am getting where all the Invoices are repeating for each
Part:

Cust Nbr
101
Order Nbr
101-201

Part Nbr
201-301

Inv Nbr
201-401
201-402

Part Nbr
201-302

Inv Nbr
201-401
201-402

Any help, much appreciated!

Re: Parent/Child Record Display Problem

Bruce L-C [MVP]
7/23/2005 8:04:08 PM

In this case you might be best off using subreports. Read up on them in
books on line.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Parent/Child Record Display Problem

Mike Harbinger
7/25/2005 6:26:30 PM

Bruce

Thanks for your reply but I found a way to do this without using a
subreport.
1) Under the parent group where you have multiple children, add as many rows
as there are children rows.
2) (Indent one column for the rest of this to show the nested relationship)
3) For each, drag/drop in a new table from the tool box
4) Select the first new table and remove the header, footer and detail rows
5) Add a new group to the new table
6) Set your group-on item to your group-on child column
7) Repeat for as many children as you have for this parent
8) To make the report more readable you can add individual column headers
for each child by inserting an additional row for each child group; use an
expression to toggle the col-header visibility property since you don't want
it to show if there are no children.

Thanks again and hope this helps somebody else
Mike

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn