Click here to Skip to main content
15,868,000 members
Articles / All Topics
Article

Master Details Report in SSRS 2008

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
19 Oct 2011CPOL3 min read 45.4K   9   4
How to create a Master Detail or Summary Detail 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:

    image001.jpg

  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”

      image002.jpg

    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 1st dataset, i.e. “dsMaster”. Please refer to the below figure for the settings:

    image003.jpg

  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, 1st 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 2nd table i.e. “Details” table.
  29. Please note: When you are handling the visibility of the 2nd 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHow to indicate master selected row? Pin
dareno218-Sep-14 2:46
dareno218-Sep-14 2:46 
AnswerRe: How to indicate master selected row? Pin
dareno218-Sep-14 21:57
dareno218-Sep-14 21:57 
GeneralNice Article Pin
Maruthu_Rockz24-Apr-14 11:44
Maruthu_Rockz24-Apr-14 11:44 
QuestionThis is superb article . I loved It !!! Pin
priyanka18863-Mar-12 8:56
priyanka18863-Mar-12 8:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.