SSRS- How to create SubReport

1.     Background

The aim of the article is to describe a way of creating SubReport using SQL Server Reporting services. Sub Report is very useful when we need to show the detail data with respect to summary data from any report. Sub Report is also very useful when we need to show some of the reports into a single report.

2.     What is Sub Report?

• A SubReport is a report item that displays another report inside the body of a main report.
• It is basically used to embed a report within a report.
• Any report can be used as a SubReport.
• We can pass the parameter into SubReport from main report.
• We can place a SubReport in the main body of the report, or in a data region.
• If you place a SubReport in a data region, the SubReport will repeat with each instance of the group or row in the data region.

3.     Scenario for creating Sub Report:

         I. Here we are going to create report and its sub report based on the state wise population and city wise population data of India:

• Main Report: It will show state wise aggregated (summary) Population details.
• Sub Report: It will show city wise population based on state as input parameter from Main Report.
• Sub Report: Chart Report that shows city wise Population based on state as input.

Below is the table data that we are going to use in all our reports:

SELECT        

     [State],

     City,

     [City Population],

     Male,

     Female,

     Sexratio,

     Literacy

FROM [Population]

         II. Below is the dataset used for the main Report:

It is a simple T-SQL SELECT statement

Below is the data output from dataset query:

           III.            Main report will looks like below one:

4.     Steps to create Sub Report in the form of Drill through

     I. Here we are going to create a sub report which shows city wise population details based on state detail passed from main Report. So we need a input parameter for State name in the sub report:

                     II.  Below is the dataset details which shows that we are showing we are using state as input parameter :

                   III.  Below is the sample report based on CityPopulation dataset:

                IV.  To add “City-wise” report as SubReport to main report (state-wise) , go to main report, right click on the Population text box and select Textbox properties:

              V. Go to Action then ‘Select to report’ option and specify the report shows city population. Add an input parameter for State:

Click Ok.

                   VI.  Preview the main report:

 VII. Once we click on the Population data on main report(e.g for Karnataka state), It will redirect to the SubReport that will show city wise details based on input state (Karnataka):

                     

Now we are able to redirect a report from a main report.

5.     Steps to create SubReport as Embedded Report within Main Report

            I. Here we will use another report(CityPopulationChart) as Sub report that is Chart report which shows top 5 most population area. 

                  

             II. For embedding a report in a main report, Right clcik on the report layout of Main Report and select SubReport:

           III.  It will add the data region for Sub report in the main report:

           IV.  Right click on the Sub Report region and select SubReport Properties:

             V.  Select the report that need to embed into the main report:

           VI.  Now main report layout looks like below one:

         VII. Preview the report

Reference - http://www.sqlcircuit.com/2012/11/ssrs-how-to-create-subreport.html