Passing multiple values for a single parameter in Reporting Services

Question

I have several Multi-Select parameters in my report. I am trying to find a way to pass in multiple values for a single parameter in the web query string? If I pass in a single value, it works fine.

The report runs fine selecting multiple choices for a single param. My trouble lies in the web query string.

Answer 1

Hi,

This is one of the poor supported features in 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

Answer 2

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>)

Answer 3

Just a comment - I ran into a world of hurt trying to get an IN clause to work in a connection to Oracle 10g. I don't think the rewritten query can be correctly passed to a 10g db. I had to drop the multi-value completely. The query would return data only when a single value (from the multi-value parameter selector) was chosen. I tried the MS and Oracle drivers with the same results. I'd love to hear if anyone has had success with this.

Answer 4

I'd use the udf to split the CSV into a temp table/table variable and join on the table... but within a stored procedure.

Also, depending on the expected length of the CSV, I'd use a numbers table too. The fastest pure SQL solution for shorter strings.

Quicker to parse the CSV then iteration and can be optimised far more than an IN clause.

Answer 5

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

=SPLIT(JOIN(Parameters!.Value,","),",")

Answer 6

ORACLE:

The "IN" phrase (Ed's Solution) won't work against an Oracle connection (at least version 10). However, found this simple work-around which does. Using the dataset's parameter's tab turn the multi-value parameter into a CSV:

    :name =join(Parameters!name.Value,",")

Then in your SQL statement's WHERE clause use the instring function to check for a match.

    INSTR(:name, TABLE.FILENAME) > 0

Answer 7

John Sampson and Ed Harper have great solutions. However, I was unable to get them to work when dealing with ID fields (i.e. Integers). I modified the split function below to CAST the values as integers so the table will join with primary key columns. I also commented the code and added a column for order, in case the delimited list order was significant.

CREATE FUNCTION [dbo].[fn_SplitInt]
(
    @List       nvarchar(4000),
    @Delimiter  char(1)= ','
)
RETURNS @Values TABLE
(
    Position int IDENTITY PRIMARY KEY,
    Number int
)

AS

  BEGIN

  -- set up working variables
  DECLARE @Index INT
  DECLARE @ItemValue nvarchar(100)
  SELECT @Index = 1 

  -- iterate until we have no more characters to work with
  WHILE @Index > 0

    BEGIN

      -- find first delimiter
      SELECT @Index = CHARINDEX(@Delimiter,@List)

      -- extract the item value
      IF @Index  > 0     -- if found, take the value left of the delimiter
        SELECT @ItemValue = LEFT(@List,@Index - 1)
      ELSE               -- if none, take the remainder as the last value
        SELECT @ItemValue = @List

      -- insert the value into our new table
      INSERT INTO @Values (Number) VALUES (CAST(@ItemValue AS int))

      -- remove the found item from the working list
      SELECT @List = RIGHT(@List,LEN(@List) - @Index)

      -- if list is empty, we are done
      IF LEN(@List) = 0 BREAK

    END

  RETURN

  END

Use this function as previously noted with:

WHERE id IN (SELECT Number FROM dbo.fn_SplitInt(@sParameterString,','))

Answer 8

John Sampson

Thank you very much and it helps alot.

Shapoor