Passing multiple values for a single parameter in Reporting Services

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

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

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

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

As of MSSQL 2016 - with compatibility level 130, you can utilize String_Split() to parse your joined parameter from SSRS. Say you wanted to populate a parameter from a query in SSRS and then pass that param to a stored proc or SSRS Shared Dataset:

1. Add two datasets to your SSRS report, one that returns a list of values and labels to display in your parameter and one that has the actual data you're looking to filter. Each of these datasets can be a stored proc or shared dataset or embedded query.
2. Create a parameter in SSRS that is NOT on the dataset you want to filter. Let's call it Customer
3. Set Customer param to allow multiple values and set up Available Values tab with the dataset, labels, and values you want to display from the query.
4. Right click the dataset you're looking to filter and add a parameter that IS defined in the stored procedure. Let's call it CustomerList.
5. Click the expression button next to the value field for this parameter and do Join(Parameters!Customer.Value, ",")
6. In your stored proc or shared dataset, utilize string_split to break the comma-delimited @CustomerList param into an array: Customer.CustID in (select value from string_split(@CustomerList, ',') where value = Customer.CustID)

this worked for a distinct set of strings ( e.g. "START", "END", "ERROR", "SUCCESS" )

1)define a Report-Parameter ( e.g. @log_status ) and check "Allow multiple values"

2) define a dataset
3) open the dataset-properties window
3a) in the Query-Tab enter your query: e.g.

select * from your_table where (CHARINDEX(your_column, @log_status,0) > 0)

3b) in the Parameters-Tab enter your Parameter, e.g.
Parametername: @log_status ; Parametervalue: <<Expr>>
3c) for the Expr click on the "fx"-Button and enter:

=join(Parameters!log_status.Value,",")

finished! ( it's similar to Ed Harper's solution, but sorry to say this didn't work for me )

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.

What you also can do is add this code in your stored procedure:

set @s = char(39) + replace(@s, ',', char(39) + ',' + char(39)) + char(39)

(Assuming @s is a multi-valued string (like "A,B,C"))

If you want to pass multiple values to RS via a query string all you need to do is repeat the report parameter for each value.

For example; I have a RS column called COLS and this column expects one or more values.

&rp:COLS=1&rp:COLS=1&rp:COLS=5 etc..

The below solution worked for me.

1. In the parameter tab of your dataset properties click on the expression icon (!http://chittagongit.com//images/fx-icon/fx-icon-16.jpg [fx symbol]) beside the parameter you need to allow comma delimited entry for.

2. In the expression window that appears, use the Split function (Common Functions -> Text). Example shown below:

=Split(Parameters!ParameterName.Value,",")