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

• Hour 1: What is SSRS and determine whether it is installed
• Hour 2: Report using wizard
• Hour 3: First SSRS report
• Hour 4: Report with parameter
• Hour 5: Custom template
• Hour 6: Drill down report
• Hour 7: Sub report
• Hour 8: Report with style
• Hour 9: Report with chart
• Hour 10: Report deployment
• Hour 11: RDL file to ASP.Net

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.

Next >> Hour 8: Report with style