SSRS: Adding a Single Selection Parameter List

In SSRS you can easily present users with database-driven single selection drop-down lists as parameters to pass to the stored procedure running your report. In the following steps I’ll go through the process of adding a single selection drop-down list to your report.

1. First you need to add a new DataSet to your report. You will use this Dataset to populate your single selection drop-down list.
2. In this DataSet, either directly write your select statement or call a stored procedure. In either case, you’ll want to return a dataset with at least two fields:
   1. The list of hidden values to assign to your drop-down list
   2. The list of labels to show the user in the drop-down list.
3. Now you will need to create a new parameter to add to your list of Parameters. You can see your list of Parameters in the Report Data tab on the left hand side bar. If this tab is not visible, then press the key combination:
   Ctrl + Alt + D
4. Right-Click the Parameters section and select to: Add Parameter
5. Give your Parameter a hidden Name and also a descriptive Prompt label that will appear beside the drop-down list
6. Under Data Type make sure Text is selected and that none of the check-box options are selected. In particular make sure that Allow Multiple Values is not selected. This is important in order to force a single-selection drop down list.
7. Under Available Values, check the radio button to Get values from a query.
8. Then select the Dataset that you just created in the Dataset dropdown list.
9. Finally, select the Value field column name and the Label field column name to use from your dataset and click Ok to create your new parameter.
10. Now you need to assign the selected value from your new Parameter select list to the parameter being passed to your main report. To do so:
    1. In the Datasets section, right-click the Dataset that runs your main report and click the option Dataset Properties.
    2. Click on the Parameters category in the left hand menu bar. You’ll now see a list of: Parameter Names and Parameter Values.
    3. Click the fx button next to the parameter that you want to pass the user’s selection from your new drop-down list
    4. Set the expression to the value of your parameter drop-down list. Do this something along the lines of:
       =Parameters!TheNameOfYourParameter.Value
    5. Then to clean up your report, delete the auto-generated parameter that you have just replaced and are no longer using in your list of parameters

Congratulations, that’s all there is to adding a single selection drop-down list parameter to your SSRS Report.

If you happen to want a multiple selection drop-down list, just make sure to check the option to Allow Multiple Values on the main Parameter creation screen. Note that allowing multiple parameters to be selected also comes with a Select All option at the top of the list.

Also note that if you want an option selected by default, you can do so by selecting the default value in the Parameters Creation/Editing window (under Default Values on the left-hand menu bar)