In SQL Server Reporting Services, Multiple parameters allows the users to Dynamically Filter the SSRS Reports using Multiple parameter value. It is similar to selecting Brand, Size and Price Range in E-commerce websites like Flipkart and Amazon. In this article we will show you, How to add Multiple parameters in SQL Server Reporting Services 2014 with example.
We are going to use below shown report to explain, Multiple 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, State, City, Full Name, Occupation, Yearly Income and Sales Amount columns.
Below screenshot will show you the, Data Source and Dataset we used for this report.
SQL Command we used in the above screenshot is:
T-SQL QUERY
Adding Multiple parameters in SSRS 2014
Before we start creating the Country parameter. Let us create one dataset in Report data Tab. First, We are creating one Embedded Dataset holding Distinct country names from [DimGeography] table in [AdventureWorksDW2014]
SQL Command we used in the above screenshot is:
T-SQL QUERY
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. Please provide valid Name, Prompt text and Data type for the Report parameter.
Allow Multiple values: If you want to allow the user to select more than one value, Please check mark this option. Please refer Multi-Valued parameters in SSRS article to understand the uses of multi values.
Next, 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, selectGet 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 Country parameter.
Next, We are creating one more Embedded Dataset holding Distinct State names from [DimGeography] table in [AdventureWorksDW2014]. SQL Command we used in the below screenshot is:
T-SQL QUERY
Next, We have to create one more parameter to hold the State information so, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.
Please provide valid Name, Prompt text and Data type for the Report parameter. Please check mark this Allow Multiple values option, If you want to allow the user to select more than one value.
In this example, We would like to use the StateDS dataset we created earlier so, select Get Values from a query option and select the Dataset from the list. Next, We are assigning [State Province Name] as Value Filed and Label Field.
Click Ok to finish configuring the State parameter.
Next, We are creating one more Embedded Dataset holding Distinct City names from [DimGeography] table in [AdventureWorksDW2014]. SQL Command we used in the below screenshot is:
T-SQL QUERY
Next, We have to create one more parameter to hold the City information so, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.
Please provide valid Name, Prompt text and Data type for the Report parameter. Please check mark this Allow Multiple values option, If you want to allow the user to select more than one value.
In this example, We would like to use the CityDS dataset we created earlier so, select Get Values from a query option and select the Dataset from the list. Next, We are assigning [City] column as Value Filed and Label Field.
We have successfully created three Datasets and three Parameters for Country Name, State Name and City.
Now, We have to apply filter conditions 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 present in[Country Parameter], [State Name] is present in [State Parameter] and [City Name] is present in [City Parameter] so, we are creating three filters for country, state and city. Here we are using Operator as IN Operator. 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 allowing us to select Multiple values from the Country names, States and Cities present in the Datasets we created. In order to display the records, We have to select the Country Name (s) from the list, State Name (s) from the list and City Name (s) from the list and Press Enter.
For now We selected the Australia Country, New South Wales State and Coffs Harbour and Darlinghurst Cities.
In this example, If you know all the states and City names in every country, you wont face any problem. What if you don’t know them?. In order resolve these situations, We have to useCascading parameters to filter the data.
Thank You for visiting Our Blog.
