CODEWITHSUNDEEP
reporting-services
Kai
Kai

Reputation: 2168

Passing multi-value parameter to another parameter

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

Upvotes: 1

Views: 1093

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 1

Related Questions