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
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
Step 18
Provide the data source a name and select data source reference.
select data source reference
Step 19
Step 20
Provide a data set name and select shared data set.
select shared data set
Step 21
Step 22
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
Step 27
You can see the list of states for the country you passed in the parameter.
states for the country
Step 28
Step 29
Select "General", provide a parameter name and select its visibility to "Hidden".
Specify values
Step 30
Step 31
Open "CountryMasterReport.rdl", right-click on "CountryRowID" and click on "Text Box Properties".
Map the parameter
Step 32
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
Next >> Hour 8: Report with style