Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Multi-detail reports using sub reports in SQL Server Reporting Services


By:   |   Updated: 2018-09-19   |   Comments (3)   |   Related: > Reporting Services Development

Problem

You use paginated .RDL reports and host them using SQL Server Reporting Services, if you have scenarios with single master-detail tables, you just have to group your table by the master data and the details will appear automatically, even using the report wizard, but what if you have a master table with multiple detail tables? An example I can tell you right away is AlwaysOn Availability Groups, in this case your “master” will be the availability group, and for the details, you can have multiple replicas and multiple databases (if you are designing tables to store this information for your organization, I don’t recommend grouping this info in a single table), and for different industries there are countless examples as well.

We will use 3 test tables for this tip, one header table and two detail tables, as follows:

base tables to use for this tip

Using a LEFT JOIN query, we will show the data in both tables for each header:

SELECT M.Descr
   ,D1.Descr_detail AS Desc1
   ,D2.Descr_detail AS Desc2
FROM A_main M
LEFT JOIN A_detail_1 D1 ON M.ID_master = D1.ID_master
LEFT JOIN A_detail_2 D2 ON M.ID_master = D2.ID_master

Creating a sample report with no grouping will show us the data:

sample data no grouping

As you quickly can see, there are some duplicate values since a cartesian product is performed for both detail tables.

But what if we use grouping? Can the problem be solved if we group the header rows? Let's take a look using grouping:

data displayed using table grouping

Even when we can see data more clearly, the cartesian product for both detail tables remains, so how do we create a clean report to display multiple detail tables?

Solution

Using the SQL Server Reporting Services sub report feature and a parameterized query we can achieve this.

A Subreport is a control available in the SSDT toolbox as you can see here:

subreport control location

Its function is to display another report within the current report, giving you more customization capabilities for your reports.  For our tip we will use this control to show each detail table as a different sub report.

Let us start with the master or principal report, for this report just include the header table (A_main) in a table control:

header data table

Include 2 empty columns, one for each detail table (A_detail_1 and A_detail_2), it will look something like this (you can change the column names if you want):

header data and empty columns

Now we will proceed to create the subreports.

Creating a SSRS subreport

Create one report for each detail table, you can create it as you like, as any normal report, but after creating it (via blank report or template), create a report parameter by accessing the Report Data tab (Ctrl + Alt +D):

accessing report data tab

Then select the Parameters folder and the Add Parameter option.

add report parameter

For the name, put any name you want as long it is descriptive, then choose the correct data type from the dropdown and put the parameter visibility to Hidden, then click OK.

configure report parameter

Now the parameter is created, this will be what is used to link to our main report.

parameter created

For the parameter to work, you must parameterize the query to filter it by the id on the master table, so let's use this query for the first table:

SELECT ID_detail
   ,Descr_detail
FROM A_detail_1WHERE ID_master = @master_ID

For the SQL parameter use any name you want. Remember the name because you will use it later.

adding the query with a parameter

Then, go to the parameters tab and you will see the parameter is already there, if it does not exist, just type it, and for parameter value, select the parameter we created previously, then click OK to save the dataset.

dataset configuration with a parameter

Then, create the subreport layout as you want, for this example just a simple table with no title:

subreport created

Save the subreport and then return to the main report.

Linking the SSRS subreport to the SSRS main report

Once you are in the main report, drag a subreport control to the empty column we created earlier:

sub report configuration

Right click on the subreport control and select Subreport Properties:

sub report configuration

For the report, select the subreport we created previously:

subreport configuration

Then go to the Parameters tab, select from the dropdown the report parameter you created, and for the value, the field that contains the key to filter, and then click OK.

subreport parameter configuration

At this point you could test your report to see that everything is working:

sub report test 1

It seems to be working. Repeat the same process for the other detail (create another sub report and the respective parameter), we will use another color to differentiate them, and this is the second subreport layout:

layout of the second subreport

Note that I have used another parameter name, to show you can name them as you want.

Then we proceed to add and configure the second sub report in our main report, as we did earlier:

second subreport addition

And now the moment of the truth, we want to make sure that each value displays the correct details and that the values are not duplicated:

final report execution

We succeeded on displaying the data the right way, now you can work on customizing the format and layout to suit your needs.

Next Steps
  • Sub reports are not embedded on the main report, they are linked, so always copy/deploy subreports to your production server for the report to work. This is also important if you modify/delete subreports, the changes will be visible at the next execution.
  • Since the sub reports used are another report, you can customize then as you wish, just be aware of the rendering and performance constraints you might have.
  • SQL Server Data Tools (SSDT) is the tool provided by Microsoft to develop Reporting Services, Analysis Services and Integration Services solutions in a Visual Studio like environment, you can download the latest version from here.


Last Updated: 2018-09-19


get scripts

next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, March 04, 2019 - 3:49:08 AM - Dan Back To Top

 How to hide (supress) it if there is no data returned? It will leave a blank row in the tabix, looks not good.


Thursday, November 15, 2018 - 1:53:43 PM - Eduardo Pivaral Back To Top

Hi Tammy,

to view your dataset, open the report in design mode, then go to view > Report data

you will be able to locate your dataset under the "datasets" folder


Thursday, November 15, 2018 - 1:03:05 PM - Tammy Robinson Back To Top

 Thank you for the great suggestions. I am having trouble seeing the Dataset properties in order to parameterize the query. I don't see the Query Builder for the Dataset that will assing the parameter. 


Learn more about SQL Server tools