How to pass multi-value parameters to a SQL query in SSRS?

Question

1

In SSRS, allowing users to select multiple options from a parameter drop-down list is a common requirement. How can you properly pass a multi-value parameter into a T-SQL dataset query?

Solution:

When you enable "Allow multiple values" in parameter properties, SSRS passes a comma-separated list of selected values to the underlying dataset.

Use the IN operator inside your SQL query dataset definition:

SELECT 
    ProductID, 
    ProductName, 
    Category 
FROM Sales.Products 
WHERE Category IN (@CategoryList)

SSRS automatically formats the parameter variable @CategoryList so SQL Server can process multiple selected items.

asked by Manish Sharma , modified by Manish Sharma in SSRS

1 Answer

Write Your Answer

Answer 1

Passing multi-value parameters to a SQL query in SQL Server Reporting Services (SSRS) allows users to select multiple options from a drop-down list and filter report data accordingly. Here is a step-by-step guide on how to configure and use multi-value parameters in SSRS SQL queries.

1. Using the IN Operator in Inline SQL Queries

The simplest way to handle multi-value parameters in SSRS is by using the SQL IN clause directly in your dataset's inline query text. When SSRS detects a multi-value parameter inside an IN clause, it automatically expands the parameter array into a comma-separated list of parameters at execution time.

Example SQL Query:

SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department
FROM
    dbo.Employees
WHERE
    Department IN (@Department)

2. Step-by-Step Configuration in Report Builder / Visual Studio

Step 1: Create the Report Parameter: Right-click on Parameters in the Report Data pane and select Add Parameter. Name it (e.g., Department).
Step 2: Enable Multi-Value: On the General tab of Parameter Properties, check the box for Allow multiple values.
Step 3: Set Available Values: Navigate to the Available Values tab and bind it to a dataset that provides the list of options or define specific values.
Step 4: Update Dataset Query: Ensure your dataset query uses WHERE Column IN (@ParameterName) as shown in the example above.

3. Passing Multi-Value Parameters to Stored Procedures

When working with Stored Procedures, SQL Server does not automatically convert SSRS multi-value parameters into an array. To solve this, you can convert the parameter array into a single comma-separated string using an SSRS expression and split it inside SQL Server.

Step A: Pass Joined String in Dataset Parameter Mapping

In your Dataset Properties, under the Parameters tab, set the value of your SQL parameter using the JOIN function:

=Join(Parameters!Department.Value, ",")

Step B: Parse the String in T-SQL using STRING_SPLIT (SQL Server 2016+)

CREATE PROCEDURE dbo.GetEmployeesByDepartment
    @DepartmentList NVARCHAR(MAX)
AS
BEGIN
    SELECT
        EmployeeID,
        FirstName,
        LastName,
        Department
    FROM
        dbo.Employees
    WHERE
        Department IN (
            SELECT value 
            FROM STRING_SPLIT(@DepartmentList, ',')
        )
END