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

By:   |   Comments (8)   |   Related: > Reporting Services Development


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:

   ,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?


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
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, September 28, 2021 - 12:45:22 PM - Jose Back To Top (89289)
This tip is helpful.

Is there an alternative to using subreports? I have the same issue, but would prefer to use grouping because subreports are known to hurt performance and slow down reports. I can't seem to achieve this with grouping. Any ideas?


Thursday, April 8, 2021 - 12:28:08 PM - KK Back To Top (88498)

I have a question: I have two reports one is main report and the other one is sub report, basically I want to implement drill through feature in ssrs. I was able to do that with out a parameter. But when I using a parameter on the first report it is filtering the data according to the parameter selected in the dropdown. When I click on the number in the main report it is sending an error message as paramter2 is missing a value. Actually I'm using two parameters in second report, one parameter is for action and second parameter is the same parmater I'm using in report one. Can any one please provide me some inputs?


Tuesday, February 16, 2021 - 10:52:58 AM - Corey Gerhardt Back To Top (88246)
How would you tackle having each sub report in their own row instead of column with each sub report row on it's own page?

I have a training report grouped by month. It has 2 sub reports, LessonsByMonth and DrillByMonth, all reports with an EmployeeID parameter. I'd like to have each sub report on its own page which seems to be complicated then I would think.

For example Group = January. Display Lessons. Display Drills but starting on its own page.
Then Group = February. Display Lessons. Display Drills but starting on its own page

I've tried many different grouping properties but I get strange things happening like a blank page in the middle of the Drills report.

I've checked my page margins and such and made sure my sub reports's widths are smaller than the main report page width just to be safe.

I'm using SSRS 2012.

Friday, September 25, 2020 - 2:10:58 PM - Mike Back To Top (86536)
Can you sort on main report column?
it is breaking subreport when you try to sort after it is loaded.
Any tricks?

Monday, July 13, 2020 - 11:37:37 PM - Troy W. Back To Top (86135)

My sub reports returns multiple pages, how do I force the main report to go to a new page based on the sub reports page break?

Monday, March 4, 2019 - 3:49:08 AM - Dan Back To Top (79185)

 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 (78264)

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 (78263)

 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. 

get free sql tips
agree to terms