Multi-detail reports using sub reports in SSRS

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:

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:

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:

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:

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:

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):

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):

Then select the Parameters folder and the Add Parameter option.

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.

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

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.

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.

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

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:

Right click on the subreport control and select Subreport Properties:

For the report, select the subreport we created previously:

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.

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

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:

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:

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:

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.

Eduardo Pivaral

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

• MSSQLTips Awards: Trendsetter (25+ tips) – 2020 | Author of the Year Contender – 2019 | Rookie of the Year – 2018