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):