Introduction
This article shows how to create a report that expects a parameter, in other words, a parameterized report. We pass CountryRowId as the parameter and we get all the states of that country.
SSRS Report with Parameter
Step 1
- In order to create a shared dataset, right-click on "Shared Datasets" and click on "Add New Dataset".
Step 2
- Provide an appropriate name of dataset.
- Select the data source.
- Select the "Stored Procedure" radio button and provide the Stored Procedure name.
Step 3
- Now let's add the report. Right-click on "Reports", select "Add" and click on "New Item…".
Step 4
- Select Report and provide an appropriate report name.
Step 5
- To add a parameter, right-click on "Parameters" in Report Data and click on "Add Parameter…".
Step 6
- Provide a parameter name and change its visibility to visible.
Step 7
- Add a data source in the report data.
- Right-click on "Data Sources" and click on "Add Data Source…".
Step 8
- Provide a data source name and select a data source reference.
Step 9
- Add the data set to the report data.
- Right-click on "Datasets" and click on "Add Dataset…".
Step 10
- Provide the data set name and select shared data set.
Step 11
- Open the report file.
- Right-click on the design surface, select "Insert" and click on "Table".
Step 12
- It will add a table to the report design surface.
- Set "State Row ID" and "State Name" in two different column headers.
- Map "StateRowID" in the first column.
Step 13
- Map "StateName" in the second column.
Step 14
- Select and delete the third column.
Step 15
- Now the report design is ready.
Step 16
- Click on preview to check the report.
- Pass a parameter value.
Step 17
- You can see the list of states for the country you passed in the parameter.
