The Multi-valued Parameters problem in Reporting Services

  • I have been able to use multi-value parameters in a report and pass them to a stored procedure without doing any heavy lifting like this. You need to create a split function shown below and use the function and the IN statement in the WHERE clause and your done.

    Report Parameter

    Name: FilterRegionId

    Data type: Integer

    Prompt: FilterRegionId

    Hidden: unchecked

    Internal: unchecked

    Multi-value: checked

    Allow null value: unchecked

    Allow blank value: checked (disabled checkbox)

    Available values: Non-queried

    Default values: Non-queried

    Stored Procedure

    Parameter: @FilterRegionId AS varchar(max)

    WHERE Clause: WHERE ORG.OrganizationId IN(SELECT Item FROM dbo.Split(ISNULL(@FilterRegionId, ORG.OrganizationId), ','))

    Split Function

    ------------------------

    CREATE FUNCTION [dbo].[Split]

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

  • Alex

    I am so glad you posted this article. It pertain specifically to an issue I am trying to resolve in two reports I am finalizing today. I am going to try what you have suggested here but am bot familar yet with CTE, the WHILE loop, or as Simon mentioned in the reply post a Split function but I will try each and hopefully come up with a solution....Thanks for the help!

    Christina

  • But it feels so dirty doing string manipulation in a database to me with all that iterative processing... tally tables would be nicer, but quite a mindset jump.

  • In real project I never used comma. I am using any non-keybord character, for example ASCII = 135. The comma was used for simplisity.

  • Most of my fellow audience miss the point. For some reason your focus comes to how to parse a string, however this article how prevent array passing to stored procedure.

  • I think I found it somewhere on the net... sorry to original author for not including the credits...

    but I found it's quite easy to use Table-valued function to handle the multi-parameters.

    You can create the function with this code:

    CREATE FUNCTION [dbo].[fn_MVParam] (@RepParam nvarchar(max), @Delim char(1)= ',')

    RETURNS @Values TABLE (Param varchar(1024))AS

    BEGIN

    DECLARE @chrind INT

    DECLARE @Piece nvarchar(1024)

    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

    And then within the stored procedure which accepts parameter (or one of them) @MultiValuedParam add this to your data retrieval:

    ... INNER JOIN dbo.fn_MVParam( @MultiValuedParam , ',') pt on pt.Param = ColumnNameToMatchValues

    Seems to be pretty efficient.

  • Irina, I will research this problem for you. I believe that you need to check array count, Then throw an error when count greater then expected value.

  • Thank you very much, by using store procedure I can store in temporary table only first 5 values. But I need to do without store procedure. I need to change XML code in report property because using our environment it is much better(performance) to send query to data warehouse.

  • Using any one of the functions provided in this discussion that returns a table containing the multi-value selections, its a simple query to check the count..

    SET @PARMCNT = (SELECT SUM(1) FROM Reporting.dbo.ParmSplit(@ParmValues,','))

    For user convenience, I'd rather have the check done on the interface side while they are selecting the values, or right when they click on "view report".

  • We created a table function (below) that parses the values out. All you need to do then is inside of the where clause: value IN (select * from dbo.PassingMultiSelect_Parameters(@Parameters,',')). We now use this on several of our automated reports with no issues.

    CREATE FUNCTION [dbo].[Passing_MultiSelect_Parameters] (

    @MultiSelectParameter nvarchar(max),

    @Separator nvarchar(10)

    )

    RETURNS @resultTable TABLE (result nvarchar(max))

    AS

    BEGIN

    DECLARE @separator_position INT

    DECLARE @array_value nvarchar(max)

    SET @MultiSelectParameter = @MultiSelectParameter + @Separator

    WHILE patindex('%' + @Separator + '%', @MultiSelectParameter) 0

    BEGIN

    SELECT @separator_position = patindex('%' + @Separator + '%', @MultiSelectParameter)

    SELECT @array_value = left(@MultiSelectParameter, @separator_position - 1)

    INSERT @resultTable

    VALUES (Cast(@array_value AS nvarchar(max)))

    SELECT @MultiSelectParameter = stuff(@MultiSelectParameter , 1, @separator_position, '')

    END

    RETURN

    END

    GO

  • Alex Grinberg (4/21/2009)


    Most of my fellow audience miss the point. For some reason your focus comes to how to parse a string, however this article how prevent array passing to stored procedure.

    Alex, I guess we are missing the point of why you do not want to pass an array to the stored procedure. Passing a array to the stored procedure works very well and seems much more straight forward in practice. I will give your article another go over to see if I am missing something but I feel you took a pipe wrench to adjust a watch. We are just showing you that you may work with what SQL Reporting Services provides. I would not be surprised if I overlooked something and did not need to create my own split function. If there is a system function which does the same thing I would hope someone would point that out as well.

    Thanks for writing the article as many people have probably struggled with this as well.

  • You can limit to 5 by having a second parameter that is based on the first.

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

    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,"-1",Join (Parameters!lastname.Value ,","))


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • I ran into this issue and needed a solution which could handle a large amount of values for the multi-value parameter, so I transferred the string into a #temp table and linked the #temp table to my query. Copy & Paste the below code in SQL Server Management Studio to see the template i've been using for this.

    --------------------------------------------------------------

    -- Multi Value Parameters(MVP) Temp Table For Stored Proc --

    --------------------------------------------------------------

    USE [Enter Database Name]

    --------------------------------------------------------------

    -- Used ONLY For Testing In Management Studio --

    --------------------------------------------------------------

    /*

    DECLARE

    @MvP varchar(MAX)

    SET @MvP = '1234567,7654321' --Example String For Testing--

    */

    --------------------------------------------------------------

    -- Used To Create Procedure --

    --------------------------------------------------------------

    /*

    GO

    CREATE PROCEDURE [dbo].[Enter Procedure Name]

    @MvP varchar(MAX)

    AS

    */

    --------------------------------------------------------------

    -- Converts String To Column In Table --

    --------------------------------------------------------------

    CREATE TABLE #tempMVP

    (tMVP varchar(20))

    DECLARE

    @MVP1 varchar(50),

    @MVP2 varchar(MAX),

    @LOC int

    SET @MVP2 = @MvP + ','

    WHILE len(@MVP2) > 0

    BEGIN

    SET @LOC = charindex(',',@MVP2)

    SET @MVP1 = left(@MVP2,@LOC-1)

    SET @MVP2 = substring(@MVP2,@LOC+1,len(@MVP2))

    INSERT INTO #tempMVP VALUES (@MVP1)

    END

    --------------------------------------------------------------

    -- Start of Select Query--

    --------------------------------------------------------------

    BEGIN

    SELECT

    temp.tMVP AS [MVP]

    --------------------------------------------------------------

    -- NOTES--

    ----

    -- Add other fields from your joined tables as needed --

    --------------------------------------------------------------

    FROM

    #tempMVP AS temp

    --------------------------------------------------------------

    -- NOTES--

    ----

    -- Add other tables joined to #tempMVP table--

    -- EXAMPLE: LEFT JOIN [Table_Name] ON [temp].[tMVP] =--

    -- [Table_Name].[Field_Name]--

    ----

    -- Or use a SubQuery to show all entered values for the MVP --

    -- EXAMPLE: LEFT JOIN (SubQuery) AS SubQuery ON--

    --[temp].[tMVP] = SubQuery.[Field_Name]--

    ----

    --------------------------------------------------------------

    ORDER BY

    [MVP]

    END

    DROP TABLE #tempMVP

    --------------------------------------------------------------

    -- END --

    --------------------------------------------------------------

  • richteel (4/21/2009)


    Alex Grinberg (4/21/2009)


    Most of my fellow audience miss the point. For some reason your focus comes to how to parse a string, however this article how prevent array passing to stored procedure.

    Alex, I guess we are missing the point of why you do not want to pass an array to the stored procedure. Passing a array to the stored procedure works very well and seems much more straight forward in practice. I will give your article another go over to see if I am missing something but I feel you took a pipe wrench to adjust a watch. We are just showing you that you may work with what SQL Reporting Services provides. I would not be surprised if I overlooked something and did not need to create my own split function. If there is a system function which does the same thing I would hope someone would point that out as well.

    Thanks for writing the article as many people have probably struggled with this as well.

    I had another look at the article and I see that there may be a difference in that I have used multi-value parameters with integer values whereas it looks like you are using string values. I can see that there is a potential for an issue when using string values and a different approach may be needed.

    Thanks again

  • Great article, Alex! lots of useful tips and information, thanks. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 16 through 30 (of 70 total)

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