Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Drop Down List Parameters in SSRS

by suresh

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

Placed Under: SSRS

  • Learn SSRS in 28 Days
  • Create New SSRS Project
  • Create a New SSRS Report
  • SSRS Query Designer
  • SSRS Shared Data Source
  • SSRS Shared Dataset
  • SSRS Embedded Dataset
  • SSRS Embedded Data Source
  • SSRS Lookup – combine datasets
  • SSRS Report Items
  • SSRS Table Report
  • SSRS Table using report wizard
  • Format SSRS Table Report
  • SSRS Matrix Report
  • SSRS Matrix with report wizard
  • Display Image in SSRS Report
  • Add Rectangle to SSRS Report
  • Add TextBox to SSRS Report
  • Create a SSRS List Report
  • Grouping in SSRS Matrix Reports
  • Grouping in SSRS Table Reports
  • Create SSRS Report using Views
  • SSRS report with store procedure
  • Add Total and Subtotal to Report
  • Add Headers & Footers to Report
  • SSRS Global References
  • Export Report to PDF, XML, Excel, Word
  • Format SSRS Table Report
  • Format Textbox Fonts & Bg Color
  • Format Numbers in SSRS Report
  • Format SSRS Report Date, Time
  • Add Row Numbers to Report
  • Headers Visible While Scrolling
  • Repeat Headers on Each Page
  • SSRS Sorting
  • SSRS Interactive Sort
  • Filters at Dataset Level in SSRS
  • Filters at Tablix Level in SSRS
  • Page Breaks in SSRS Report
  • Custom Code in SSRS Report
  • SSRS Calculated Fields
  • SSRS Dashboard Reports
  • SSRS Document Map
  • SSRS Drill Through Reports
  • SSRS Drill Down Reports
  • SSRS Drill Down Matrix Report
  • SSRS Go To URL Action
  • SSRS Subreports
  • SSRS Report Parameters
  • SSRS Cascading Parameters
  • SSRS Multiple Parameters
  • SSRS Multi Value Parameter
  • SSRS Drop Down List Parameters
  • SSRS Charts
  • SSRS Area Chart
  • SSRS Bubble Chart
  • SSRS Column Chart
  • Format SSRS Column Chart
  • SSRS Data Bars
  • Data Bars in SSRS Matrix Reports
  • Format SSRS Data Bars
  • SSRS Funnel Chart
  • SSRS Linear Gauges
  • SSRS Radial Gauges
  • Format SSRS Gauges
  • SSRS Indicators
  • Format SSRS Indicators
  • SSRS Line Chart
  • SSRS Pie Chart
  • Format SSRS Pie Chart
  • SSRS Pyramid Chart
  • SSRS Range Chart
  • SSRS Radar Chart
  • SSRS Scatter Plot
  • SSRS Sparkline
  • SSRS Stacked Bar Chart
  • SSRS Configuration Manager
  • Change Report Manager Title
  • SSRS Deploying Reports in BIDS
  • Deploy using Report Manager
  • SSRS Report Manager Shared DataSource
  • SSRS Cached Reports
  • SSRS Linked Reports
  • SSRS Report Parts
  • SSRS Report Snapshot
  • SSRS Report History Snapshot
  • SSRS Report Subscription
  • SSRS Security
  • Upload File to Report Manager
  • Install SSRS Report builder
  • Report Builder – Create a Report
  • Report Builder – Create a Dataset
  • Report Builder- Create Bar chart
  • Report Builder – Create Pie Chart
  • Report Builder – Create report without wizard

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy