I was working on a Sql Server Reporting Services Report and needed to display a table inside of a Table. I was not able to find a blog entry on how to do it. So this was goal to create a report that looks like this
Step one lets create the sql query to get the data from the Northwind database
In this query we will get the Orders and Order Details to display in a sub table
Now that we have a query to get the data lets create an sql server reporting project in visual studio 2008
Add a new report to reporting project and use a datasource to the Northwind database
Select
o.orderid,
p.ProductName,
od.UnitPrice,
od.Discount,
od.Quantity,
o.CustomerID,
o.RequiredDate,
o.ShipAddress,
o.ShipName,
o.ShipCity,
o.ShipCountry,
o.ShipPostalCode from [Order Details] od
inner join Orders o on od.OrderID = o.OrderID
inner join products p on od.ProductID = p.ProductID
In the design the query part of the wizard use the query above.
Select Tabular for the report type.
In the Design the table part of the wizard put the orderid in the group by fields and the rest under details
In the Choose the Table screen make sure you enable drill down and sub totals
Finish the wizard and you should get something like this
Go ahead and change the report name to something like Northwind Orders
In the blue line with [orderid] go ahead and move the CustomerId, RequiredDate, ShipAddress, ShipName, ShipCity, ShipCountry, and ShipPostalCode to the same line. Adjust title of the column above the fields so you have something like this
Right click on the row with ProductName and select add row outside group above. In the bottom row give put the ProductName, UnitPrice, Discount, and Quantity fields. Delete any other fields in that row. You should have something that looks like this
In the row you added the Titles to right click and select row visibility. When the report is initially run hide the row and have so the display can be toggled by this report item and select orderid
Now when you run the report you should see a table under the main row. Hope this helps