Passing multiple values for a single parameter in Reporting Services

Solution 1:

Although John Sansom's solution works, there's another way to do this, without having to use a potentially inefficient scalar valued UDF. In the SSRS report, on the parameters tab of the query definition, set the parameter value to

=join(Parameters!<your param name>.Value,",")

In your query, you can then reference the value like so:

where yourColumn in (@<your param name>)

Solution 2:

This is what I use when passing a multi-select param to another multi-select param.

=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")

Solution 3:

This is one of the poor supported features in SQL Reporting Services.

What you need to do is pass all of your selected items as a single string to your stored procedure. Each element within the string will be separated by a comma.

What I then do is split the string using a function that returns the provided string as a table. See below.

ALTER FUNCTION [dbo].[fn_MVParam]
   (@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
  BEGIN
  DECLARE @chrind INT
  DECLARE @Piece nvarchar(100)
  SELECT @chrind = 1 
  WHILE @chrind > 0
    BEGIN
      SELECT @chrind = CHARINDEX(@Delim,@RepParam)
      IF @chrind  > 0
        SELECT @Piece = LEFT(@RepParam,@chrind - 1)
      ELSE
        SELECT @Piece = @RepParam
      INSERT  @Values(Param) VALUES(CAST(@Piece AS VARCHAR))
      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
      IF LEN(@RepParam) = 0 BREAK
    END
  RETURN
  END

You can then reference the results in the where clause of your main query like so:

where someColumn IN(SELECT Param FROM dbo.fn_MVParam(@sParameterString,','))

I hope this you find this solution to be of use. Please feel free to pose any questions you may have.

Cheers,John