The Drop Down list parameters in SSRS allows the users to select required value from the drop down list and then, SSRS will filter the Report data using the user specified value. OR Drop Down parameters allows the users to Dynamically Filter the SSRS Reports using drop down list. In this article we will show you, How to add Drop Down List parameters in SQL Server Reporting Services with example.
We are going to use below shown report to explain, Drop Down List parameters in SSRS Reports. Please refer SSRS Table Report article to understand the creation of Table report. If you observe the below screenshot, It was a normal Table report with Country, Full Name, Occupation, Yearly Income and Sales Amount columns.
Below screenshot will show you the, Shared Data Source and Dataset we used for this report.
The SQL Command we used in the above screenshot is:
T-SQL QUERY
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 parameter. Let us create one more dataset holding Distinct country names from [DimGeography] table in [AdventureWorksDW2014]
SQL Command we used in the above screenshot is:
T-SQL QUERY
SELECT [EnglishCountryRegionName] AS [Country] FROM [DimGeography]
In order to add Drop Down List Parameters in SSRS, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.
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 label before the text box
- Data Type: Country name is a Text data type so, we kept the default text unchanged
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.
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. Report will be filtered using this value.
- Label Field: This value is shown to end-user.
Click Ok to finish configuring the Drop Down List parameters. Now, We have to apply filter condition on 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
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 and select the parameter name from the GUI.
Click Ok to finish configuring the Filters at Dataset Level. Lets Click on Preview Tab to preview the Data.
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 the Canada as Country Name.
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 blank report to end-user.
To resolve this situation, We have to assign Default value to the Drop down list parameter. In this example, we are manually entering United States as default value to the parameter as shown in below screenshot.
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 drop down list.
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 value, Please refer SSRS Multi Value Parameter article
Thank You for visiting Our Blog.
