SSRS

I’ve been creating SQL Server Reporting Services (2005) reports for an independent client of mine for several years, after spending six months writing them for a Microsoft Certified Partner back in 2006.

If you ever need to work with multi-valued parameters in SSRS 2005, you need to know about the following, life-saving page:

Passing multi-value parameter in stored procedure (SSRS report).

In case of sql query as well as stored procedure, all selected values of the multi-value parameter will be passed as ‘,’ (comma) separated. So we should use IN clause in WHERE to impose the desired condition.

Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values as different-2 parameters (as they all are ‘,’ separated). And thus if we do multiple selection (or SelectAll) error would be there, though it will work fine if we select only one value.

error(s): Must Declare Scalar variable @parameter OR number of parameters being passed are more than expected….

Now if preview the report it will work fine for single value of color, but if we select multiple colors from colors parameters, it will give error. To pass the complete list of selected values of @color parameter as a single value/string we should use a function JOIN().

In data set properties of AdventureWorks dataset, click on parameters tab and use JOIN() funtion as:

=JOIN(Parameters!Color.Value, “,”)

This function will join all the color values and will a give a comma separated values thus a single value as a whole will be passed to the SP….

To use this list of comma separated values in the stored procedure we need to split them in a proper way as they are as : ‘value1, value2, value3′, but we need them as ‘value1′, ‘value2′, ‘value3′ to use them in IN clause. So we need to have a UDF function which will take this string of values and will return all them splitted. And then use them in SP as:WHERE @Color IS NULL OR Color IN (SELECT * FROM SplitList(@Color, ‘,’))

where SplitList is a UDF which takes the list and the de-limiter as input parameters and splits all the values.

The function I always use for splitting is one I found in a book years ago:

CREATE FUNCTION [dbo].[fn_MVParam](@RepParam nvarchar(4000),
   @Delim char(1)=',')
RETURNS @Values TABLE (Param nvarchar(4000)) AS
   BEGIN
   DECLARE @chrind INT
   DECLARE @Piece nvarchar(4000)
   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 (@Piece)
         SELECT @RepParam = RIGHT(@RepParam, LEN(@RepParam) - @chrind)
         IF LEN(@RepParam) = 0 BREAK
      END
   RETURN
END