SSRS Master Datail SQL2008

Question

SSRS Master Datail SQL2008

peter_opglab

Valued Member

Points: 52
More actions
October 18, 2011 at 2:08 am

#261544

Hello,
is it possible to create a report with master detail record...
Example
Orderheader table and OrderDetail table
First show 1 record from header
Then show records from detail table from that order,
Then next orderheader record
Orderdetailrecords ....
and so on...
Thx

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

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

BeeDubya Right there with Babe Points: 722 More actions · Answer 1

Yes. You'll want to include the order header columns (OrderID,etc.) and order detail columns (ItemID,etc.) together in one query and then order by OrderID, ItemID.

OrderID ItemID

1 ..........100

1 ..........200

1 ..........300

2 ..........100

2 ..........200

2 ..........300

In report designer wizard, drag the OrderID and any other columns from Order Header into the Row Group box. Drag the ItemID and any other columns from the Order Details into the Details box. You should be home free from there.

peter_opglab Valued Member Points: 52 More actions · Answer 2

Hi,

i was succesful for creating the report, but.... i want a different layout...

for example

header.orderid header.client header.date header.address

detail.item detail.quantity detail.amount

header.orderid header.client header.date header.address

detail.item detail.quantity detail.amount

so it looks like 2 tables

1 record for header then detailrecord and so on

I see i can for sql2008 r2, but i use sql2008

thx

BeeDubya Right there with Babe Points: 722 More actions · Answer 3

The answer is with the row groups. If your report is already created then you should be able to right click on the details row (the little gray box next to the grid) and Insert Row Group. Then in the properties window you tie this new row group to the OrderID column. With the row group created you should see a new line above your details line. This new line where you would put all of your header fields in.

I went from SQL2005 to SQL2008R2 so I'm not sure of the SSRS differences between 2008 and 2008R2 but I hope this helps.