The Multi-valued Parameters problem in Reporting Services

  • Why not take it a step further, like this:

    =Iif(Parameters!CategoryName.Count = 1, "'" & Parameters!CategoryName.Value(0) & "'", "'" & Join(Parameters!CategoryName.Value, "','") & "'")

  • Alex,

    I also want to comment that, while I provided an alternate method, I do appreciate the article and the method it presents. As always "It depends" is the answer.

  • Alex,

    Do you have an example of using the WHILE loop in a stored proc? I have found the query parameters section in my report for this part:

    "=Join(Parameters!CategoryName.Value, "'") ."

    but my stored proc change is where I am having trouble and am more comfortable using the while loop (I am still a beginner :-D)

    --Background- I am trying to build a report with cascading drop downs and am using multi-value string parameters. I can run the report correctly when using one value but when I choose multiple values my report returns nothing.

  • Wow, that is a lot of work. I've been using multi-value parameters for a while now. I created a split function on the SQL Server that I use for every multi-value parameter. It works with one, two, or select all:

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SSRS_SplitMultiValueParams]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    DROP FUNCTION [dbo].[udf_SSRS_SplitMultiValueParams]


    CREATE FUNCTION [dbo].[udf_SSRS_SplitMultiValueParams]

    /* This function is used to split up multi-value parameters */


    @ItemList NVARCHAR(4000),

    @delimiter CHAR(1)





    DECLARE @tempItemList NVARCHAR(4000)

    SET @tempItemList = @ItemList


    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)

    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)


    IF @i = 0

    SET @Item = @tempItemList


    SET @Item = LEFT(@tempItemList, @i - 1)

    INSERT INTO @IDTable(Item) VALUES(@Item)

    IF @i = 0

    SET @tempItemList = ''


    SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

    SET @i = CHARINDEX(@delimiter, @tempItemList)




    Then, I pass in the multivalue parameter to the stored procedure, just like I would pass in any parameter. Inside the stored procedure, I add it wherever I need it with the following:

    where metric1 in (select item from dbo.udf_SSRS_SplitMultiValueParams(@metric1, ','))

    Works great for me, and since it is technically a table, you can join to it instead of using IN, for performance consideration. I always use an int field instead of a string for the value field in the parameter, and give something meaningful to the users under the label. This cuts down on the size of the string sent into the database, as well as dealing with strings containing commas.

    And if you have so many items in the MV parameter that you run up against the 4000 char limit, then you should re-think your use of the multi-value parameter, and maybe segment it by making it dependant on another parameter, as sort of a "grouping" item.

    ..if this is helpful, let me know!

  • We're using this function

    CREATE FUNCTION [dbo].[fn_ParseParam]

    (@RepParam nvarchar(4000), @Delim char(1)= ',')

    RETURNS @Values TABLE (Param nvarchar(4000))AS


    DECLARE @chrind INT

    DECLARE @Piece nvarchar(4000)

    SELECT @chrind = 1

    WHILE @chrind > 0


    SELECT @chrind = CHARINDEX(@Delim,@RepParam)

    IF @chrind > 0

    SELECT @Piece = LEFT(@RepParam,@chrind - 1)


    SELECT @Piece = @RepParam

    INSERT @Values(Param) VALUES (LTRIM(@Piece))

    SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)

    IF LEN(@RepParam) = 0 BREAK




  • Alex,

    Nice article and thanks for taking the time to write this up.

    And while SSRS's multi-valued approach is a bit laking, I think we all would choose having this option over nothing at all.

    The more you are prepared, the less you need it.

  • I always just write a UDF that splits the comma seperated values, and then use 'IN' instead of '=' in my compare.

    UDF looks like this.

    /****** Object: UserDefinedFunction [dbo].[Split] Script Date: 04/21/2009 13:47:48 ******/





    CREATE FUNCTION [dbo].[Split]


    @List nvarchar(2000),

    @SplitOn nvarchar(5)


    RETURNS @RtnValue table


    Id int identity(1,1),

    Value nvarchar(100)




    While (Charindex(@SplitOn,@List)>0)


    Insert Into @RtnValue (value)


    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))


    Insert Into @RtnValue (Value)

    Select Value = ltrim(rtrim(@List))




    then you can call the udf like so.





    ThemeID IN (SELECT Convert(INT, VALUE) FROM dbo.Split(@p_ThemeID,','))

    Technical conquests for the nerd in all of us

  • Got it to work! Now I can get three reports on the server that I have been waiting to go live for quite a while. Thanks to everyone for your help :w00t:

  • For internal SQL only - Yes.

  • Yes, I think it's much ado about nothing. It is what it is for now! I usually just parse it out in my stored proc like this:

    set @Industry = @Industry + ','

    while charindex(',',@Industry,1) > 0


    set @IndustryStringLength = len(@Industry)

    set @MyEndPosition = charindex(',',@Industry,1)

    set @MyIndustry = substring(@Industry,1,@myendposition-1)

    set @Industry = substring(@Industry,@myendposition+1,@IndustryStringLength)

    insert into @TempIndustry Select @MyIndustry


    Put it into a temp table and then use something like "Where IndustryCode In (Select Industry from @TempIndustry)" for my actual select query.

    It's a pain, but it works!

  • Where I work, we resolved this issue by using a function that returns a table. So, the report passes the comma delimited string of values to the stored procedure which in turn calls the function in the WHERE clause.

    Within the procedure the string is passed to a function that contains a looping structure that essentially converts the delimited string into a table and returns that table (the table consists of a single column of the values that made up the string).

    Then in the proc we use the IN statement with the function. Here's an example.

    Let's say the report passes the parameter value 'Red,Blue,Green,Yellow' to a proc.

    We have a proc such as this (psuedocode)

    CREATE PROC spGetFabricsByColor @color


    SELECT Fabric, Style,Pattern

    FROM Fabrics

    WHERE Color in (select Results from dbo.FN_ReportString(@color))

    The function FN_ReportString returns a table consisting of a single column (Results) as follows:







  • Thank you very much, Simon. It works. Next question please how to exlude or make invisible All in multi-valued parameters. Thank you. Irina.

  • I'm actually wondering how to determine if ALL values are selected ?

    I don't want to pass anything to the procedure if ALL is selected. Its just to much overhead and it destroys the query plan if you are adding all the entries into the query vs omitting it completely.

    Let me know if you all find a solution.



  • I would like to know the answer for this question also. I would like to exclude "select all" options from some reports if I know IN class is going to be big.

    If you add values to default value of parameter and no set of available values was defined then no "Select All" value appears in drop down list. But if you have available value from the query?

    Best regards, Irina.

  • There is no way I know of to hide ALL. Instead you have to limit the number of values passed to the SP.

    I answered this earlier in the thread

    You can limit to 5 by having a second parameter that is based on the first (the multi value parameter).

    Set the query to use the second parameter and set the default value for the second parameter to something like.

    =iif(Parameters!lastname.Count>5,"-1",Join (Parameters!lastname.Value ,","))

    Where lastname is the parameter the user selects. This results in the query being executed with an invalid value. You can then in the report display a message saying they need to select < 5 options.

    Simon Sabin
    SQL Server MVP

Viewing 15 posts - 31 through 45 (of 70 total)

You must be logged in to reply to this topic. Login to reply