In most relational databases there are several one-to-many relationships. For example, in a database that stores information about customer orders, there might be an Orders table and an OrderDetails table. The Orders table may store information regarding each particular order that a customer made and the OrderDetails table may store information about each item that was purchased for a given customer order.
Since these relationships do exist, often users want to select a particular record from the one table and then display the many records. For example, rather than showing all the OrderDetails on a report, instead a better option would be to display all the Orders on a report. When the user selects an order, the orders corresponding OrderDetails will be displayed. In this article I will outline the steps needed to create a Master/Detail report using SSRS 2008.
Let’s first look at the setup of the master data and its objects on the report. First, you will place a table on the report and its source data will be a list of Orders. In this article I will use the AdventureWorks database as the datasource. The following is the T-SQL query that will be used for the master data:
FROM Sales.SalesOrderHeader soh
Create a report that contains a Table item. In addition create a datasource that points to the AdventureWorks database and a dataset with the above query as the source. Add each field in the dataset as a column in the table. Your report should resemble the following:
Now that the master data is in place, let’s create the structure for the Detail data. First, add another dataset to the report, using the following query as the source:
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p
ON sod.ProductID = p.ProductID
sod.SalesOrderID = @OrderID
Then add a Table item directly below the master data table. Add each field in the dataset as a column in the new table. Your report layout should resemble the following:
By including a parameter in the source query for the detail data, this causes SSRS to automatically create a report parameter. You will need to change two of the attributes of the parameter. Go to the parameter properties and check the Allow null values check box and ensure that the Hidden radio button is selected. Next you will need to assign the following expression to the Hidden attribute property of the Detail table so that it is only displayed when needed. Use the following expression:
=IIF(Parameters!OrderID.Value > 0, False, True)
Display Detail Data
The final step is to the set the parameter value in the report with a value from the Master data set. To accomplish this, right click on the SalesOrderID column in the Master data table and select Textbox Properties. In that window select Action from the right menu pane. Select the Go to report radio button under the Enable as hyperlink section. Then in the Specify a report drop down list, select the report that you are creating now. Next click the Add button in the parameters section, select the OrderID from the parameter drop down list and specify the SalesOrderID as the parameter value. Use the following screen shot as a template:
Click OK when you are done. Then for presentation purposes return to the master data table and underline the SalesOrderID and change the Text Color to Blue. To add a little more flare to the look and feel of the report select all the columns in the Detail row of the master data table and in the properties window for the BackgroundColor attribute paste the following expression:
=IIF(Parameters!OrderID.Value = Fields!SalesOrderID.Value, "Yellow", "White")
Your final report, after selecting an OrderID from the master data table should be similar to the following screen shot:
Each time you click one of the OrderIDs from the master data table, the row will be highlighted yellow and the detail data table will refresh reflecting only the details for the selected OrderID. Download the complete example here.
As always, if you have any questions or comments regarding this article please feel free to email me at firstname.lastname@example.org.