Subreports are very useful tool in creating a reports in SSRS. They allow us to display one report in another. This functionality is very useful if we want to display graphs/tables developed in one report as a part of another, more complex report. This way we don’t have do duplicate reports we want to use multiple times, we just call it from many other reports.
In order to explain the usage of subreports, we will first display a basic report that contains data related to Students table. Afterwards, we will create a “parent” report where we will call a basic report and use it as a subreport.
Example of basic report
We have created a report which displays the data from Students table. In this example, this report will be taken as “basic” report, meaning that it will be a report which will be incorporated into another report.
This report has one report parameter called Department ID, and data will be displayed according to the selected parameter value:
Figure 1: Basic report structure
As we can see, currently all the students from Computer Science department (Department ID=1) are being displayed in the report.
Now we want to incorporate this report into another one that has a bit more complex structure.
Creation of subreport and its usage
A subreport item acts something like a data region: it is a region in a “parent” report that holds data that are defined elsewhere. The “parent” report which will hold a subreport within its structure in the following way: At the beginning of this report we will have some basic information about the Computer Science department, and below the text we will have table of students studying in that department.
The “parent” report without a subreport being incorporated looks like this:
Figure 2: Preview of “Parent” report without incorporated subreport
The process of incorporating basic report into a “parent” report is the following:
1. Accolate Subreport item in Toolbox
2. Drag-and-drop Subreport item in the body of “parent” report
Figure 3: Adding subreport item in report body
3. Right-click on subreport item and click “Subreport Properties…”
Figure 4: Defining subreport properties
There are four tabs of subreport properties that needs to be defined:
a. Define the name of the subreport
b. Select a report that will be used as a subreport
Figure 5: Defining General tab information
a. Here we can set display options of the subreport:
i. If we want the subreport to be initially displayed, we select “Show” radio button
ii. If we want the subreport to be initially hidden, we select “Hide” radio button
iii. If we want the subreport to be initially shown/hidden based on some expression that we define, we select “Show or hide based on an expression” radio button and in the box below we define the expression to be used
iv. If we want a subreport to be displayed by toggling a report item, we check that checkbox and select desired report item
In our case, we want the subreport to be initially displayed, so we select “Show” radio button.
Figure 6: Defining Visibility tab information
Here we can set the subreport parameters by defining the name and the value of the parameter.
In our example, we will not use any subreport parameter, so we will not define any.
Figure 7: Parameters tab information
Here we can set the border color, style and width options.
Figure 8: Border tab information
After we have gone through all these steps, we click OK, save the changes and go to Preview mode. We will see the following:
Figure 9: Presentation of subreport usage
As we can see, our basic report is here integrated into another report presenting the data correctly.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.