Passing multi-value parameter to another parameter

Question

I have a report with 2 parameters - drop down selections of Buildings (@buildings) and Departments (@departments). When a Building is selected, the list of Departments is limited to Departments within that Building.

This is straightforward when the report parameter is not set to single-selection - the query parameter of Department is set to @building, so the above works.

However, a new requirement is to be able to multi-select Buildings. I've amended my background queries to use Building in (@building) rather than @building = Building and changed the Building query parameter of the main report to =String.Join(Parameters!Building.Value, ",") so everything passes through correctly.

However changing the Building query parameter for the Department parameter to this makes the Department drop-down box appear disabled. Setting the parameter to =Parameters!Building.Value makes the list work, but only when a single Building is selected - it appears as an empty list beyond that.

How can I set up a parameter to take a multi-value parameter as an argument?

Edit: Full details

Main report query:

create proc dbo.GetReport (
    @buildings varchar(max), @departments varchar(max)) as
select <columns>
from dbo.MainReport
where Building in (@buildings) and Department in (@departments)

Main report parameter settings:

@buildings: =Join(Parameters!Buildings.Value, ",")
@departments: =Join(Parameters!Departments.Value, ",")

Buildings parameter query:

create proc dbo.GetBuildings as
select <columns> from dbo.Buildings

Departments parameter query:

create proc dbo.GetDepartments(
    @buildings varchar(max))
select <columns> from dbo.Departments 
where Building in (@buildings)

Departments parameter setting:

// This will make the Departments drop-down disabled
@buildings: =Join(Parameters!Buildings.Value, ",")
// So will this
@buildings: =Split(Join(Parameters!Buildings.Value, ","), ",")
// This will only work when only one building is selected
@buildings: =Parameters!Buildings.Value

You handle it in the query for the Department parameter the same way you handle it in the query for the main report, or any other query. — Tab Alleman, Apr 27 '15 at 18:49
It sounds like it should be working. Did you update the query that you are using for your Department parameter to use **IN** Parameters!Building.Value? — Hannover Fist, Apr 27 '15 at 23:34
I've added the full details of the queries / parameter settings in my report — Kai, Apr 28 '15 at 08:45

score 1 · Accepted Answer · edited May 23 '17 at 11:50

You cannot handle Multi-valued parameters from SSRS in stored procedures in this way (with the IN() clause). That method only works when you generate your SQL query in SSRS (without calling a stored procedure).

To use a Multi-valued parameter in a stored procedure, you must call a split function on the parameter in the stored procedure, and JOIN to it to get the results.

The answer in this question spells it out in even more detail.

Passing multi-value parameter to another parameter

1 Answers1