sql reporting nested table/data

Question

This is the scenario. A customer(Customer table) is linked to an account(Account table). And within each account there are multiple items(Items table).

I need to show a report like below.

What is the best way to achieve this?

I have done the customer account table part since that is just a simple report.

1. How do I nest the table with the Items table?
2. How to I pass the parameter for query to get the Items? (items takes account ID as parameter)

Answer 1

You need to write a query which joins Account, Customer and Item table, and then grouping in Reporting Services: http://msdn.microsoft.com/en-us/library/bb630426.aspx

Answer 2

You should probably use grouping as Alexander sugested for a simple table, but I've also shown how to do subreports further on below.

How to do this using grouping:

Query something like this for grouping:

SELECT 
Account.AccountNo,
Account.CustomerName,
Item.Type,
Item.Qty
 FROM Account
LEFT JOIN Item
ON Item.AccountID = Account.AccountID

When you create your report, group as follows:

You'll need to edit the group properties to set a summary footer, etc..

How to do it with a subreport:

Below are instruction to get this report in RS2005 using a subreport rather than grouping (This would be practical if the subreport became more complex):

Create a subreport for items, taking parameter called accountno (that filters the items by account no):

In your main report, drop a Subreport component in the third column:

In the supreport component properties, General tab, set the subreport:

In the supreport component properties, Parameters tab, set the subreport:

Format to suit!

Answer 3

Another method is to put a Rectangle object inside your rightmost table cell. That will then give you a canvas upon which you can put in your matrix. Simply drop the Matrix inside the Rectangle in your rightmost table cell, and you should be started off in the right direction.