Sending multivalue from SSRS to a stored procedure.

One, of the many, features that I wish they would add to SSRS; is an easier way to send in multivalue selected parameters to a stored procedure.

The best solution to this problem, is to use a function that converts a string of delimited values into a table.

 Here are the quick low down steps:

1) Go to your SSRS dataset properties -> Parameters -> (select the multivalue parameter function [fx]) and change the parameter to send in a sting of delimited values. For example,

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

2) Create the function that splits and creates a table:

CREATE FUNCTION dbo.fn_charlist_to_table
(
@list ntext,
@delimiter nchar(1) = N','
)
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2
  BEGIN
    SET @chunklen = 4000 - datalength(@leftover) / 2
    SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
    SET @textpos = @textpos + @chunklen
    SET @pos = charindex(@delimiter, @tmpstr)

    WHILE @pos > 0
      BEGIN
        SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
        INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
        SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
        SET @pos = charindex(@delimiter, @tmpstr)
       END
      
    SET @leftover = @tmpstr
  END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))

RETURN
END
GO

3) In your stored procedure, call the function. For example:

Select * from measurment where
measurement.MeasurementID in (select [str] from fn_charlist_to_table(@MeasurementID,Default))