Step By Step SSRS in 11 Hours: Hour 7

This article shows how to create a sub-report in SQL Server Reporting Services (SSRS). SSRS is a part of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).

Introduction

 
First, we create the first report with a country list then create another one for the state list based on the parameter passed, in other words, country row id and then display the state report as a sub-report.
 
Contents

Sub Report in SSRS

 
Step 1
 
To add the report, right-click on "Reports", select "Add" and click on "New Item".
 
New Item 
 
Step 2
 
Select "Report" and provide the report name.
 
Report 
 
Step 3
 
Add a data source to the report data, right-click on "Data Sources" and click on "Add Data Source".
 
Data Sources 
 
Step 4
 
Provide a data source name and select shared data source reference.
 
source reference 
 
Step 5
 
Add a dataset in the report data, right-click on "Datasets" and click on "Add Dataset".
 
Add Dataset 
 
Step 6
 
Provide a dataset name and select shared dataset.
 
select shared dataset 
 
Step 7
 
Right-click on the design surface, select "Insert" and click on "Table".
 
Table 
 
Step 8
 
Set "Country Row ID" as the column header and map the "CountryRowID" field for that column.
 
CountryRowID 
 
Step 9
 
Set "Country Name" as the column header and map the "CountryName" field for that column.
 
Country Name 
 
Step 10
 
Select and delete the third column.
 
column 
 
Step 11
 
Now the design of the main report is ready with the columns "Country Row ID" and "Country Name".
 
main report 
 
Step 12
 
To check the report click on the "Preview" tab and we will have a country list.
 
review 
 
Step 13
  • Now let's create a state report with the parameter CountryRowID.
  • To add a report, right-click on "Reports", select "Add" and click on "New Item".

    Add
Step 14
 
Select "Report" and provide the report a name.
 
provide report 
 
Step 15
 
To add a parameter, right-click on "Parameters" in Report Data and click on "Add Parameter".
 
Add Parameter 
 
Step 16
 
Provide a parameter name and select its visibility as visible.
 
visible 
 
Step 17
  • Add a data source in report data.
  • Right-click on "Data Sources" and click on "Add Data Source".

    Add Data Source
Step 18
 
Provide the data source a name and select data source reference.
 
select data source reference 
 
Step 19
  • Add a data set in the report data.
  • Right-click on "Datasets" and click on "Add Dataset".

    Datasets
Step 20
 
Provide a data set name and select shared data set.
 
select shared data set 
 
Step 21
  • Open the report file.
  • Right-click on the design surface, select "Insert" and click on "Table".

    Insert
Step 22
  • It will add a table on the report design surface.
  • Set "State Row ID" and "State Name" in two separate column headers.
  • Map "StateRowID" in the first column.

    State Name
Step 23
 
Map "StateName" in the second column.
 
second column 
 
Step 24
 
Select and delete the third column.
 
delete the column 
 
Step 25
 
Now the report design is ready.
 
Now report design 
 
Step 26
  • Click on preview to check the report.
  • Pass some parameter value.

    check report
Step 27
 
You can see the list of states for the country you passed in the parameter.
 
states for the country 
 
Step 28
  • Add one more parameter in Report Data.
  • Right-click on "Parameters" and click on "Add Parameter".

    Add one more parameter
Step 29
 
Select "General", provide a parameter name and select its visibility to "Hidden".
 
Specify values 
 
Step 30
  • Select "Default Values".
  • Select the "Specify values" option and specify "0" as the value.

    CountryMasterReport
Step 31
 
Open "CountryMasterReport.rdl", right-click on "CountryRowID" and click on "Text Box Properties".
 
Map the parameter 
 
Step 32
  • In the text box properties select "Action".
  • Select "Go to report" in Enable as an action.
  • Specify "CountryMasterReport".
  • Map the parameter with "CountryRowID".

    Action
Step 33
 
Right-click on the report design surface, select "Insert" and click on "Subreport".
 
Subreport 
 
Step 34
 
Right-click on the subreport area and select "Subreport Properties".
 
Subreport Properties 
 
Step 35
 
Select "General", provide the appropriate name.
 
Select the report that we want to display as a subreport, in other words "RptCountryDetailReport".
 
RptCountryDetailReport 
 
Step 36
 
Select "Parameters" and map the parameter for CountryRowID.
 
map the parameter for CountryRowID 
 
Step 37
 
Now our complete report design is ready.
 
design is ready 
 
Step 38
  • Click on the "Preview" tab and you can see the country list.
  • Now click on any of the country row ids and the state list is shown as a sub report.

    Preview