Master Details Report in SSRS 2008

Problem

How can we create a Master Detail or Summary Detail report in SSRS 2008?

Solution

1. Add a New Report in the report solution & name it as “MasterDetails” report.
2. Create a data source.
3. Create 2 datasets as “dsMaster” & “dsDetails”.
4. To create these 2 datasets, find some master detail table, e.g., AccountType ? Master Table & Accounts ? Detail table.
5. First create the query for Master as below, dsMaster.
   SQL

   SELECT ID, AccountType, Description FROM AccountType

6. Then create the query for Details, dsDetails.
   SQL

   SELECT AccountID, DateCreated, Income, Expenditure FROM Accounts
   WHERE AccountType = @AccountType.

7. Once we do this, @AccountType parameter will be added in our parameter list as shown in the figure below:

   

8. Right click on the “AccountTypeID” parameter
   1. On the General Tab of this parameter, go to the “Select parameter visibility” & select “Hidden” from the list. (This step is done so that when we view our report, this parameter won’t be visible in the parameter list. The value for this parameter is handled internally which is the next step to be done.)
   2. Now go to the “Default Values” Tab & select “Specify values”

      

   3. Click on the Add button, a dropdown will be visible.
   4. Now click on the “fx” button & write the below expression for the default value for the parameter.

      =-1 

   5. Now click on “Ok”, this will set our parameter. (This step will assign a default value to our parameter. If you fail to do this step, an error will be raised as parameter is not assigned any value.)
9. Now on the Design areas, add 2 tables, one for Master & other for Details.
10. Select the dataset as “dsMaster” for the first table, this table will show our Master table.
11. Select the dataset as “dsDetails” for the second table, this table will show our Details table.
12. Now on the Master table, click on the column detail where you want to do a drill through.
13. In our example, I have selected this for “Description”.
14. Right Click on the Description textbox, go to the “Text Box Properties”.
15. On the “Text Box Properties”, go to the “Action” tab.
16. On the “Action” tab, select “Go to report” hyperlink, after this go to “Specify a report” section, there select the Current report on which we are working (in our e.g., it is “MasterDetails”).
17. Now map all parameters which are required to load this report, add it using the “Add” button.
18. In our case, we have only 1 parameter as “AccountTypeID”, select it from the drop down list.
19. For the value for this parameter, select the ID from the drop down list, this is from the 1^st dataset, i.e. “dsMaster”. Please refer to the below figure for the settings:

    

20. Now click on Ok.
21. Now go to the “Font” tab & in the Effect area select “Underline”, this will help the user to identify that there is a link on this cell.
22. Once this is done, the next step is to handle the visibility of the “Details” table.
23. This can be done as follows, 1^st map the “Details” table with the “dsDetail” data set.
24. Now right click & select the “Tablix properties”.
25. Go to the “Visibility” tab of the “Tablix properties”.
26. Here go to the “When the report is initially run” section & select “Show or hide based on an expression.
27. Once you select this, a text box will become visible. Now click on the “fx” button & write the following expression:
    SQL

    =IIF(Parameters!AccountTypeID.Value <> -1, False, True)

28. This expression helps us to control the visibility of the 2^nd table i.e. “Details” table.
29. Please note: When you are handling the visibility of the 2^nd table, make sure the parameter which you are selecting for controlling the visibility should have a value which is not there in our DB.
30. Now click on the “Ok” button.
31. Now preview the report, it will only show the “Master” table first. But when you click on the “Description” cell, the “Details” table will become visible.
32. In this way, we can implement the “Master Details” report in SSRS 2008.