In previous articles, I have published several tutorials on Reporting Services (you can find them in the reporting services – SSRS category). These tutorials provide detailed guidance on creating a basic report using SSRS, applying parameters and filters to limit report output, and similar topics.
Designing more advanced and visually appealing reports requires additional experience and practice, which is beyond the scope of this tutorial. However, one area I have not covered in depth is multi-value parameters. While I demonstrated filtering a report using a single-value parameter, SSRS also supports a multiple-value parameter, which allows users to select multiple values. Implementing this is more complex because passing the parameter into the SQL query executed by Reporting Services requires additional handling.
Let’s go through an example step by step. In this example, I will create a report that uses multiple filters.
Report Data Source
The dataset for this example report consists of a list of geographically segmented customers from the AdventureworksDW2016CTP3 sample database. A sample dataset has been prepared for demonstration purposes.
Setting up SSRS Data Source and Dataset
Instructions for configuring the dataset can be found here. Additionally, an auxiliary dataset will be created to provide the list of values for the filter (parameter), as shown below. Since the report will be filtered by region, the dataset will be defined 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, the Reporting Services project contains two datasets.
Creating an SSRS Multiple-Value Parameter – REGION
Instructions for creating a parameter are provided here. For the available values, select the “filtr_region” dataset, and for the default values, select the same dataset. Default values ensure that all options are selected when the report is first run.
Preparing a Simple SSRS Report to Display a Table
For simplicity, the report will display only a table containing values from the main dataset. A preview is shown below:
Connecting the Parameter to the Dataset
If the “allow multiple values” option is not enabled, only a single value can be passed to the dataset, as explained in the 5th tutorial on SSRS parameter settings. However, when multiple values are selected and separated by semicolons, additional steps are required:
- Add a WHERE clause to the main Customers dataset with the condition Region IN @REGION (our parameter) – see the first image.
- Use an SSRS expression to correctly format the parameter for the SQL query. In the “Parameters” tab, use the following expression:
=SPLIT(JOIN(Parameters!REGION.Value,”,”),”,”) – this generates a comma-separated list of selected parameter values, which the SQL query can process.
Functional SSRS Report
Once configured, the filter will function correctly. For example, if Australia and Canada are selected as regions, only customers from these two regions will be displayed.
An alternative method to pass multiple parameter values to a SQL query is via a database function. You can create a table-valued function that parses the parameter values into a table, which can then be INNER JOINed with the dataset to automatically filter the results.