Multi-Value Parameters and Stored Procedures - SSRS

Overview

This article walks through how create an SSRS (SQL Server Reporting Services) report with a multi-value parameter submitted to a stored procedure. Since a stored procedure cannot accept an array as parameter, the technique is to concatenate all the selected values, delimited by a comma, and submit the resulting string as a single parameter to the stored procedure. The stored procedure then parses out the individual values and filters the records appropriately.

Walk-Through

1. Create your report with the desired multi-value parameter.

2. Write the stored procedure using the following as an example. For any stored proc parameter that corresponds to a multi-value report parameter, make the data type VARCHAR and be sure the length is large enough so that if every value is selected, truncation won't occur.

{copytext|StoredProc}
CREATE PROC dbo.uspMultiValueTest
    (
     @Input varchar(1000) -- Make sure this size is big enough!
    ) AS BEGIN
    
/*--- Declarations ---*/
declare 
     @doc       xml
    ,@s         varchar(1000)
    ,@Delimiter varchar(2)

/*--- @Values table ---*/
/* This table will have one row for each value in the @Input parameter,
    which holds all the value from the multi-valued report parameter */
declare
     @Values table (ID VARCHAR(100))

/*--- Inits ---*/
select
     @Delimiter     = ',' 
    ,@s             = replace(@Input, '&', '&')
    ,@s             = '<x><v>' + replace(@s, @Delimiter, '</v></x><x><v>') + '</v></x>'
    ,@doc           = convert(xml, @s)

/*--- Load @Values table ---*/
insert into 
    @Values (ID)
select
     v  = T.c.value('v[1]', 'varchar(100)')
from
    @doc.nodes('x') T(c)

/*--- Main Query ---*/
SELECT 
    result = Name
FROM
    dbo.Facilities 
WHERE 1=1
    /* Filter by the multi-value parameter */
    AND FacilityId IN (SELECT ID FROM @Values)
    
end

3. In your report, navigate to the Data tab and edit properties for the dataset of interest.

Edit Dataset Button

Edit Dataset Button


4. On the Parameters tab, for the appropriate parameter in your stored procedure, specify =JOIN(Parameters!MyReportParameter.Value,",")

Dataset Properties Dialog, Parameters tab

Dataset Properties Dialog, Parameters tab