In SQL Server Reporting Services, Subreports allows us to add one or more related reports to the Data region. Following are some of the advantages of SSRS subreports:
- Allows us to add Multiple related reports in one Report.
- Allows us to Nest or Insert One report inside the other.
- And, allows us to pass parameters (Parameter values) from main report to subreport
- If you place the subreport inside the table or matrix then subreport will be repeated for each row otherwise, it will be displayed once.
SSRS Subreports Example
In this example we will show you, How to create SSRS Subreports inside the Table Reports with example. For this, we are going to use the below shown report. Please refer the SSRS Table Reports article to understand the Shared Data Source and Dataset we used in this report.
Below screenshot shows you the second report and this is the report we are going to display inside the main report (subreport). Please refer SSRS Data Bars article to understand the Shared Data Source and Dataset we used in this report and we done some formatting as well.
If you observe closely, It’s just a normal report with Basic Parameter and its default value is Australia.
First, We are going to hide the Parameter because we are going to pass the parameter value from main report to this Subreport. To hide the parameter, Right click on the Parameter and select the Report Properties option. In the general tab, Please change the parameter visibility option from Visible to Hidden as shown below
Before we nest the Subreports, we need an Empty Row inside the table so select the Details Row from Tablix and right-click on it to open the context. From the context menu, please select the Insert Row option and select Inside Group – Below as shown below
Once you select Inside Group – Below option, a new Row will be added to the table
From the above screenshot you can observe that, newly created row is splitted into two cells but we need single cell only for adding subreports. For this, Please select the entire row and right-click on it and select the Merge Cells option as shown below
Now you can notice that, we have one long cell to hold the subreport. Lets add the subreport to this cell
We can add subreport by two ways:
First, We can drag and drop the Subreport from Toolbox to empty cell as shown below
Second, Right click on the empty cell and select the Insert option and then select Subreport option as shown below
Once you selected the Subreport option, an empty subreport is added to the table.
Now, we have to add our report to this subreport region so, Right click on the Subreport and select the Subreport Properties.. option from the context menu
Once you selected the Subreport Properties.. option, a new window will be opened to select the report. For now, we are selecting Polished Data bar report as shown below
After selecting the required report, We have to assign the parameters value(s) of a subreport (If any). To add the parameter, go to Parameters tab and click on Add button.
Under the name section, Please select the Parameter name of the sub report
Next, We have to assign proper value to the selected Parameter. Here, ParaCountry Parameter is expecting country names so we selected the [Sales Territory Country] column from the main report.
NOTE: If you don’t find the exact column in the main report, Use the fx button to create one.
Click Ok to finish creating SSRS Subreports in Table Report
Let us see the report preview by clicking preview tab.
As you can observe from the above screenshot, SSRS Subreport is perfectly nested inside the main report. Please don’t mind with color combinations.
Thank You for Visiting Our Blog