Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Subreports in SSRS – Querychat

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.

Table: Students

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:

          1. General

                    a. Define the name of the subreport

                    b. Select a report that will be used as a subreport

Figure 5: Defining General tab information

          2. Visibility

                    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

          3. Parameters

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

          4. Borders

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc