Hour 7. Sub Reports
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.
Sub Report in SSRS
Step 1
To add the report, right-click on "Reports", select "Add" and click on "New Item".
Step 2
Select "Report" and provide the report name.
Step 3
Add a data source to the report data, right-click on "Data Sources" and click on "Add Data Source".
Step 4
Provide a data source name and select shared data source reference.
Step 5
Add a dataset in the report data, right-click on "Datasets" and click on "Add Dataset".
Step 6
Provide a dataset name and select shared dataset.
Step 7
Right-click on the design surface, select "Insert" and click on "Table".
Step 8
Set "Country Row ID" as the column header and map the "CountryRowID" field for that column.
Step 9
Set "Country Name" as the column header and map the "CountryName" field for that column.
Step 10
Select and delete the third column.
Step 11
Now the design of the main report is ready with the columns "Country Row ID" and "Country Name".
Step 12
To check the report click on the "Preview" tab and we will have a country list.
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".
Step 14
Select "Report" and provide the report a name.
Step 15
To add a parameter, right-click on "Parameters" in Report Data and click on "Add Parameter".
Step 16
Provide a parameter name and select its visibility as visible.
Step 17
- Add a data source in report data.
- Right-click on "Data Sources" and click on "Add Data Source".
Step 18
Provide the data source a name and select data source reference.
Step 19
- Add a data set in the report data.
- Right-click on "Datasets" and click on "Add Dataset".
Step 20
Provide a data set name and select shared data set.
Step 21
- Open the report file.
- Right-click on the design surface, select "Insert" and click on "Table".
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.
Step 23
Map "StateName" in the second column.
Step 24
Select and delete the third column.
Step 25
Now the report design is ready.
Step 26
- Click on preview to check the report.
- Pass some parameter value.
Step 27
You can see the list of states for the country you passed in the parameter.
Step 28
- Add one more parameter in Report Data.
- Right-click on "Parameters" and click on "Add Parameter".
Step 29
Select "General", provide a parameter name and select its visibility to "Hidden".
Step 30
- Select "Default Values".
- Select the "Specify values" option and specify "0" as the value.
Step 31
Open "CountryMasterReport.rdl", right-click on "CountryRowID" and click on "Text Box Properties".
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".
Step 33
Right-click on the report design surface, select "Insert" and click on "Subreport".
Step 34
Right-click on the subreport area and select "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".
Step 36
Select "Parameters" and map the parameter for CountryRowID.
Step 37
Now our complete report 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.
Author
Akshay Patel
58
28.6k
7.7m