Issues using multiple parameters in SSRS report (stored procedure)

Question

I've read countless posts on this topic but I can't seem to get any of the recommendations to apply to my particular situation (which isn't different than others...)

I have an SSRS report. Dataset 1 is using a stored procedure and in the where clause I have

    and (@param is null or alias.column in 
    (select Item from dbo.ufnSplit(@param,',')))

I borrowed the dbo.ufnSplit function from this post here: https://stackoverflow.com/a/512300/22194

    FUNCTION [dbo].[ufnSplit]
    (@RepParam nvarchar(max), @Delim char(1)= ',')
 RETURNS @Values TABLE (Item nvarchar(max))AS
 --based on John Sansoms StackOverflow answer:
 --https://stackoverflow.com/a/512300/22194

   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(Item) VALUES(@Piece)
       SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
       IF LEN(@RepParam) = 0 BREAK
     END
   RETURN
   END

In dataset 2 I am getting the values that I want to pass to dataset 1

    select distinct list from table

My parameter for @param is configured to look at dataset 2 for available values

My issue is that if I select a single value from my parameter dropdown for @param, the report works. If I select multiple values from the dropdown, I only return data for the first value selected.

My values in dataset 2 do not contain any ,'s

Did I miss anything for fail to provide enough information? I'm open to criticism, feedback, do's and don'ts for this, I've struggled with this issue for some time, and by no means a SQL expert :)

Cheers, MD

Update So SQL Profiler is showing me this:

exec sp... @param=N'value1,value2 ,value3 '

Questions are: 1. Shouldn't every value be wrapped in single quotes? 2. What's with the N before the list? 3. Guessing the trailing spaces need to be trimmed out

Use Profiler to see what SQL command is being passed from SSRS to SQL Server. Then you will know whether the problem is in your proc or your report. The code you've shown so far looks right, so the problem is probably in some other part of the proc or report. — Tab Alleman

StevenWhite StevenWhite · Accepted Answer · 2016-06-15T18:39:28

When you select multiple values from a parameter dropdown list they are stored in an array. In order to convert that to a string that you can pass to SQL you can use the Join function. Go to your dataset properties and then to the Parameters tab. Replace the Parameter Value with this expression:

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

It should look like this:

Now your split function will get one comma separated string like it's supposed to. I would also suggest having the split function trim off spaces from the values after it has separated them.

Issues using multiple parameters in SSRS report (stored procedure)

2 Answers