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]

    go

    CREATE FUNCTION [dbo].[udf_SSRS_SplitMultiValueParams]

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

    (

    @ItemList NVARCHAR(4000),

    @delimiter CHAR(1)

    )

    RETURNS @IDTable TABLE (Item VARCHAR(50))

    AS

    BEGIN

    DECLARE @tempItemList NVARCHAR(4000)

    SET @tempItemList = @ItemList

    DECLARE @i INT

    DECLARE @Item NVARCHAR(4000)

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

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

    WHILE (LEN(@tempItemList) > 0)

    BEGIN

    IF @i = 0

    SET @Item = @tempItemList

    ELSE

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

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

    IF @i = 0

    SET @tempItemList = ''

    ELSE

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

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

    END

    RETURN

    END

    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

    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 (LTRIM(@Piece))

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

    IF LEN(@RepParam) = 0 BREAK

    END

    RETURN

    END

  • 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 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[Split]

    (

    @List nvarchar(2000),

    @SplitOn nvarchar(5)

    )

    RETURNS @RtnValue table

    (

    Id int identity(1,1),

    Value nvarchar(100)

    )

    AS

    BEGIN

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

    Begin

    Insert Into @RtnValue (value)

    Select

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

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

    End

    Insert Into @RtnValue (Value)

    Select Value = ltrim(rtrim(@List))

    Return

    END

    GO

    then you can call the udf like so.

    SELECT

    col1,

    col2

    WHERE

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

    Technical conquests for the nerd in all of us
    www.tavislovell.com

  • 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

    Begin

    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

    End

    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

    AS

    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:

    Results

    ----------

    Red

    Blue

    Green

    Yellow

  • 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.

    Thanks,

    Raul

  • 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

    http://sqlblogcasts.com/blogs/simons

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

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