Drop Down List Parameters in SSRS

The SSRS Drop Down list parameters allow the users to select a required value from the drop-down list and filter the Report data using the user-specified value. OR SSRS Drop Down parameters enables the users to Filter the Reports using the drop-down list Dynamically.

In this article, we will show you, How to add Drop Down List parameters in SSRS with an example.

We are going to use the below-shown report to explain, Drop Down List parameters in SSRS Reports. Please refer to Table Report article in SSRS.

If you observe the below screenshot, It was a normal Table report with Country, Full Name, Occupation, Yearly Income, and Sales Amount columns.

Drop Down List parameters in SSRS 2014

The below screenshot shows you the Shared Data Source and Dataset we used for this report.

Drop Down List parameters in SSRS 2014

The SQL Command we used in the above screenshot is:

SELECT Geo.[EnglishCountryRegionName] AS [Country]
      ,Geo.[StateProvinceName] AS [State]
      ,Geo.[City]
      ,Cust.FirstName +' '+ Cust.LastName AS [Full Name]  
      ,Cust.EnglishEducation AS Education 
      ,Cust.EnglishOccupation AS Occupation
      ,SUM(Cust.YearlyIncome) AS YearlyIncome
      ,SUM(Fact.SalesAmount)AS SalesAmount 
FROM  DimCustomer AS Cust
  INNER JOIN
      FactInternetSales AS Fact ON 
 Cust.CustomerKey = Fact.CustomerKey
 INNER JOIN
     [DimGeography] AS Geo ON
   Cust.GeographyKey = Geo.GeographyKey
 GROUP BY Geo.[EnglishCountryRegionName]
         ,Geo.[StateProvinceName] 
	 ,Geo.[City]
	 ,Cust.FirstName
         ,Cust.LastName  
         ,Cust.EnglishEducation 
         ,Cust.EnglishOccupation 
  ORDER BY [Country]

Drop Down List parameters in SSRS

Before we start creating the SSRS Drop down list parameters. Let us create one more dataset holding Distinct country names from [DimGeography] table in [AdventureWorksDW2014]

Drop Down List parameters in SSRS 2014

SQL Command we used in the above screenshot is:

SELECT [EnglishCountryRegionName] AS [Country]

FROM [DimGeography]

To add SSRS Drop Down List Parameters, right-click on the Parameters Folder in the Report Data tab, and select Add parameters..

Drop Down List parameters in SSRS 2014

Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties.

  • Name: Please specify the valid Parameter name as per your requirement. Here, We specified it as CountryParameter
  • Prompt: The text you specified here will be displayed as a label before the text box
  • Data Type: Country name is a Text data type so, we kept the default text unchanged
Drop Down parameters in SSRS 2014

If you creating normal Report Parameter then you can Click Ok to finish configuring it but for Drop Down List parameters, We have to select Available Values as shown in below screenshot.

If you can specify the values manually, you can select specify values option and enter the values manually. In this example, We would like to use the CountryName dataset we created earlier so, select Get Values from a query option and select the Dataset from the list.

Drop Down List Parameters in SSRS 2

Please specify the Value Filed and Label Field. Here, we have only one column so, both will be Country.

  • Value Filed: This value will be sent to query. The report will be filtered using this value.
  • Label Field: This value is shown to end-user.
Drop Down List Parameters in SSRS 3

Click Ok to finish configuring the Drop Down List parameters. Now, We have to apply filter condition on the dataset. Please refer Filter at Dataset Level in SSRS article to understand the creation of Filters.

Select the Dataset from the Report data tab and Right-click on it will open the context menu. Please select the Dataset Properties.. option from it

Drop Down parameters in SSRS 2014

In this example, We are going to display the records whose [Country Name] is equal to [Country Parameter] we just created so, Please select the Country as Expression and Operator as Like Operator and Value as Parameter name.

If you find any difficult to write the parameter name, Please click on the fx button. Next, select the parameter name from the GUI.

Drop Down parameters in SSRS 2014

Click Ok to finish configuring the SSRS Drop down list Filters at Dataset Level. Let’s Click on the Preview Tab to preview the Data.

Drop Down List parameters in SSRS 2014

From the above screenshot, you can observe that It is displaying Blank Report with Drop down list displaying all the country names present in the Dataset we created. In order to display the records, We have to select the Country Name from the Drop-down list and Press Enter.

For now, We are selecting Canada as Country Name.

Drop Down List parameters in SSRS 2014

Add Default Values to Drop Down List Parameters in SSRS

If you observe the above screenshot, It is displaying Blank report When you are previewing the report and asking us to select the value first. It will be annoying to display the blank report to end-user.

To resolve this situation, We have to assign a Default value to the SSRS Drop down list parameter. In this example, we are manually entering the United States as the default value to the parameter as shown in below screenshot.

Drop Down List parameters in SSRS 2014

If you preview the report now Instead of displaying Blank report. It will display all the records whose country name is united states and it will allow us to select the different value using the drop-down list.

Drop Down List parameters in SSRS 2014

NOTE: Drop down list parameters in SSRS will be very helpful to select the one value at a time from the drop-down list. In order to use Multiple values, Please refer SSRS Multi Value Parameter article