Step By Step SSRS in 11 Hours: Hour 7

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

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