SSRS - How to add input parameters in the report

1.     Background

The purpose of this article is show how to create input parameters for the reports. Input parameters are helpful in limiting the data in the report as well as it helps the users to get the specific data that they required in the report.                             .

Below is the sample dataset & report in which we will add input parameters:
Dataset:

Report:

2.     Step by Step procedure to create input parameters in the report:

·         Go to the dataset and add the where clause for the T-SQL query:

·         Run the report and see whether any input parameter comes in the report or not:

Fig- showing text box for input parameter.

·         Give some value to the input parameter (Textbox) and see whether report is showing filtered data or not:

Fig- Report showing filtered data for FiscalYear 2010 only.

There is a chance of manual mistakes if we enter the data for input parameter. This issue can be solved by showing the parameters value in dropdown.

·         For showing the parameter’s value in the dropdown, we need a separate dataset that will populate the data for the parameter. Below is the dataset that will show distinct value of FiscalYear.

·         Press Ctrl+Alt + D or go to Report data pane then go to the Datasets folder then click on Add Dataset.

·         Map the newly created dataset (FiscalYear dataset) to the report. Click ok.

Here we can see that newly created dataset DS_FiscalYear has been added successfully.

·         Go to Parameters folders in Report Data pane, right click on the FiscalYear parameters and select parameter properties:

 

·         It will open the Report Parameters properties. Go to the Available Values &  set the below properties:
1. Dataset : DS_FiscalYear
2. Value Field: FiscalYear
3. Label Field: FiscalYear

Note: Label field will show descriptive values for the Dropdown and value field are the corresponding values that will pass in the query. In our case both is same as we have single column.

Click ok.

·         Now preview the report. It will parameter’s value in dropdown fashion:

·         Select any year in the dropdown and view the report:

Now report is showing data for only Fiscal Year- 2008

·         The above dropdown is a single valued dropdown by which we can select only single value that is not useful in some cases where we need to show data for multiple values. So there is need to change this parameter to Multi-valued parameters.

·         To convert the single valued parameter to multi valued parameter, right click on FiscalYear parameter and go to the properties, Check “Allow multiple Values”

·         Preview the report and see whether parameter is not multiple value or not:


Now we can see multi value dropdown for the FiscalYear parameter.

·         Run the report for some of the years and see whether data is coming correctly or not.

 

Report is showing the correct data as per input parameters values.

3.     Conclusion
By using the above steps, we can implement input parameters (single valued/multiple values) in the report.

----------------------------------------------------End of Article--------------------------------------------------