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 the main report to the subreport
- If you place the SSRS 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 an example.
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 know the Data Source & Dataset we used in this report and we have done some formatting as well.
If you observe closely, It’s just a normal report with Basic Parameter and its default value in Australia.
First, We are going to hide the Parameter because we are going to pass the parameter value from the 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 in SSRS, 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 the newly created row is split into two cells but we need a 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. Let’s add the subreport to this cell
There are two ways to add subreport in SSRS:
The First Approach to add Subreports in SSRS
First, We can drag and drop the Subreport from Toolbox to empty cell as shown below
The Second Approach to add Subreports in SSRS
Right click on the empty cell and select the Insert option and then select the 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
Selecting the Subreport Properties.. option opens a new window 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 parameter’s 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 subreport
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 SSRS subreports report preview by clicking the 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