In the past, I have written several tutorials on reporting services (you can find them in the reporting services – SSRS category). I have gone into great detail on how to create a simple report using SSRS, how to limit the report’s output using parameters/filters, and similar topics. More complex and attractive reports are a matter of experience and practice, and I can’t help you much with that. However, there is one thing I haven’t focused on enough. I did introduce the possibility of limiting the report’s output with a parameter, but only for a single value parameter. SSRS has the option of a multiple value parameter, which means you can select multiple values. In such a case, the solution is more complex because passing the parameter into the SQL query executed from Reporting Services becomes more challenging.
Multiple Values Filters in Reporting Services – How to Do It?
Let’s go through an example step by step. In this example, I want to create a report that utilizes multiple filters.
Report Data Source
The data for a simple report will be a list of geographically divided customers from the AdventureworksDW2016CTP3 sample database. I’ve put something together there for demonstration purposes.
Setting up SSRS Data Source and Dataset
Instructions for the dataset can be found here. Additionally, I will prepare another auxiliary dataset that will serve as a list of values for the filter (parameter), see below. Since I want to filter by region, I will set up the dataset as distinct regions:
SELECT
DISTINCT [geo].[EnglishCountryRegionName]
FROM [AdventureworksDW2016CTP3].[dbo].[DimCustomer] [cust]
JOIN [AdventureworksDW2016CTP3].[dbo].[DimGeography] [geo]
ON [cust].[GeographyKey] = [geo].[GeographyKey];
As a result, in the Reporting Services project, I have two datasets.
Creating an SSRS multiple value Parameter – REGION
How to create a parameter is explained here. For the available values, I select the “filtr_region” dataset, and for the default values, I also choose this dataset. Default values mean that when the report is run, all values will be selected by default.
Preparing a Simple SSRS Report to Display a Table
I’m keeping it simple and displaying only a table with the values from the main dataset that I prepared. Here’s a preview:
At This Point, We Have a Problem Connecting the Parameter to the Dataset
If we don’t have the “allow multiple values” option enabled, the solution is simple because we can pass only one value from the parameter, as I explained in the 5th part about SSRS parameter settings. However, how do we do it when there are multiple values separated by semicolons in the parameter?
- Follow the images, first add a WHERE clause to the main Customers dataset and put the condition Region in @REGION (our parameter) – 1st image.
- Then, you need to deal with passing the parameter into the SQL query in the correct format. This is done using SSRS Expression, which is set in the “Parameters” tab, where you write:
=SPLIT(JOIN(Parameters!REGION.Value,”,”),”,”) – this will create comma-separated selected parameter values, which will then be processed by the SQL query.
Functional SSRS Report
Now the filter should work, and if you select, for example, Australia and Canada as regions, only customers from these two regions should be displayed.
Another way to pass values from a parameter to an SQL query is through a function. In the database, you can create a table function that parses the parameter values into a table, and then you can INNER JOIN that table in the dataset, automatically limiting the result.
