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.

Example of multiple values filter in SSRS - assignment

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];

Dataset for multiple values filter in SSRS

As a result, in the Reporting Services project, I have two datasets.

SSRS 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.

SSRS parameter multiple values SSRS multiple values get values from query

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:

SSRS report 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.

SSRS multiple values parameter

SSRS multiple value parameter settings via expression

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.

SSRS multiple values result of the report

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.

5/5 - (1 vote)

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *