The Multi-valued Parameters problem in Reporting Services

  • Alex Grinberg-230981

    SSC Eights!

    Points: 896

    Comments posted to this topic are about the item The Multi-valued Parameters problem in Reporting Services

  • Olivier Moreau

    Valued Member

    Points: 67

    great tips!

    you can also convert the array send by reporting services with this code in your stored procedure.

    SET @MyArray= Char(39) + Replace(@MyArray,',',Char(39)+','+Char(39)) + Char(39)

    if you choose the following value in RS : XXX,YYY,ZZZ

    RS send it like this to the stored procedure : 'XXX,YYY,ZZZ'

    With this code, the array is transform as 'XXX','YYY','ZZZ'

    You can now use it in the query.

    Olivier Moreau (Neos-SDI)

    http://www.omrbi.com

  • Simon Sabin

    SSCrazy Eights

    Points: 8142

    You don't have to change the XML, when RS passes the parameter it automatically converts the array of selected items to a comma delimited list.


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • Simon Sabin

    SSCrazy Eights

    Points: 8142

    This is very dangerous as it allows SQL Injection, and as parameters can be passed by URL this is very very risky.

    Using a split function is the safer option.


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • Jon Spink

    SSCarpal Tunnel

    Points: 4010

    I've always used this code in my stored procs:

    charindex(',' + rtrim(fieldname) + ',',',' + @paramlist + ',') > 0

  • Samuel Vella

    SSCertifiable

    Points: 7995

    Nice article, with some practicle advice on manually editing the report XML

    Your last comment though:

    As a final word, I would also like to add that .NET knowledge is required if you want to be a successful SQL Server 2005 (and up) DBA.

    Is this just restricted to DBA's? Surely Database Developers could benefit from this too.

    Some of us are commited to development and don't see a DBA role as a step up the career ladder.

    (personally, I went up my career ladder by going back into development)

  • i.jones

    SSC Enthusiast

    Points: 176

    Our solution was to

    1) persist the parameters in the database

    2) pass in reference GUID as a parameter to the report

    3) stored procedure is called with said GUID

    4) stored procedure pulls parameters using GUID

    So, the multivalue params are already in a table, so no need to do any nasty splitting of strings.

  • hayden-673486

    SSC Enthusiast

    Points: 118

    I would not consider the problem calling a stored procedure where the parameter was set to Multi-value option an SSRS bug.

    Yes true, it is not a bug - however, many would say the design is still flawed - by the simple fact that SSRS lets developers/DBAs choose to call a stored procedure as opposed to raw sql, and then not provide proper functionality by the application to cater for multi-valued parameters.

    Don't get me wrong, I think SSRS 2005 is a fantastic product and use it everyday - however, I hope this particular issue is improved in later versions of SSRS - is it in 2008 ?. Having to maintain SSRS reports with the many types of hackery required to make this fundamental requirement work is a bit of a pain.

    Also...

    I've also experienced headaches when just checking to see if a specific value in a multi-value parameter list is actually selected, and then apply conditions to particular report objects based on that multivalue item being selected - You can use a VB function in the report to achieve this. As per the link below:

    http://www.theruntime.com/blogs/thomasswilliams/archive/2008/09/29/hiding-and-showing-columns-based-on-a-parameter-in-reporting.aspx

    that method enables you to explictly use the parameter value as opposed to the index of the array. However, I would hope that, again, this is improved in later versions of SSRS.

    🙂

    Good article, glad to see this is being addressed by many others too.

    Cheers

  • Jon Spink

    SSCarpal Tunnel

    Points: 4010

    another problem is that when 'Select All' is selected, you have to deal with a massive string which slows down the performance of your proc, rather than ignoring the condition altogether.

  • Keith Dunn

    SSC Veteran

    Points: 299

    We solved this using a generic function in the Code area that converts a multi-value parameter list to XML, passing the XML to the sproc and then using a generic Transact-SQL table function to convert to a table. It is not that much code and it covers all situations pretty well. By converting the list to a table that can be joined, it eliminates any performance problems associated with large lists.

    Here is the code in case it will help anyone else with this common problem:

    This goes in the Code area under Report Properties. Note that it also works with parameters that are not multi-valued.

    Function StrParmToXML(ByVal obj As parameter) As String

    Dim strXML as String

    Dim i As Integer

    strXML = ""

    If obj.IsMultiValue then

    For i = 0 To obj.count - 1

    strXML = strXML & ""

    Next

    Else

    strXML = strXML & ""

    End IF

    strXML = strXML & ""

    Return strXML

    End Function

    Here is the SQL table function (SQL 2005 and up)

    CREATE function [dbo].[rf_ParameterTable] (@parmxml xml) RETURNS TABLE

    AS

    RETURN

    select

    parmxml.item.value('@value', 'varchar(max)') as parm_value,

    parmxml.item.value('@label', 'varchar(max)') as parm_label

    from @parmxml.nodes('//parameter/item') as parmxml(item)

    )

  • Jack Corbett

    SSC Guru

    Points: 184360

    As Simon mentioned the parameter is passed as a comma-delimited list by default so I would just split the list in the stored procedure into a table and join on it. If you have a Numbers/Tally table splitting a string is pretty fast unless it is an extremely large list and there is an article being written that will address this as well.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Irina Ostrovskaya

    Ten Centuries

    Points: 1113

    I would like to know how to exclude "All" from multi-valued parameter and restrict users to choose not more than 5(for example) values in parameter list.

    If somebody knows the answer please help. Thank you.

  • RSCERO

    SSC-Addicted

    Points: 406

    We found a pretty easy to use and reliable way of doing this using a split function we built that returns the parameter array as a table. This way, we don't have to worry about dynamic SQL or SQL injection. Here's the function code and how we use it. The nice part about this function is that we can pass it whatever is needed for the delimiter. In this case, becuase SSRS returns multi-select parms as a comma seperated list, we use the comma. The following example will only return results from the main table where the category matches those selected in the parameters, whether it's 1 or 100 categories selected.

    Example:

    DECLARE @Categories VARCHAR(MAX)

    SET @Categories = 'VALUE1, VALUE2, VALUE3, ..., VALUE100' (this would be sent to the sproc by SSRS)

    SELECT *

    FROM MainTable a

    inner join Reporting.dbo.ParmSplit(@Categories,',') b on a.Category = b.Items

    -- =============================================

    -- Description: Function splits a delimited string into

    -- seperate records and returns a table

    -- containing the individual values

    -- =============================================

    CREATE FUNCTION [dbo].[ParmSplit](@String varchar(max), @Delimiter char(1))

    RETURNS @Results TABLE (Items varchar(max))

    AS

    BEGIN

    DECLARE @INDEX INT, @CNT INT

    DECLARE @VALUE varchar(max)

    SELECT @INDEX = 1, @CNT = 1 -- Initialize the variables

    WHILE @INDEX !=0

    BEGIN

    SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- Find the first split location

    IF @INDEX !=0

    SELECT @VALUE = LEFT(@STRING,@INDEX - 1) -- Slice it up

    ELSE

    BEGIN

    SELECT @VALUE = @STRING

    SELECT @CNT = 10000

    END

    INSERT INTO @Results(Items) VALUES(@VALUE) -- Store the results

    SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- Remove used slice

    SELECT @CNT = @CNT + 1 -- See if we are done

    IF LEN(@STRING) = 0 BREAK

    IF @CNT > 8000 BREAK -- Limit to 8000 items

    END

    RETURN

    END

    Addressing the last limit question, although not the cleaneast method, you could change the 8000 limit in the function (or make it a parmater of the function), and set the limit to 5, thereby limiting the selection to the first 5 selected in the list. This wouldn't be very clear for the users, but it would work to limit the result set.

  • Alasdair Thomson

    Hall of Fame

    Points: 3630

    We struggled with this exact same issue and came up with a function which is used/called in the sp you are wanting to run;

    Function:

    USE [LIVE]

    GO

    /****** Object: UserDefinedFunction [dbo].[funcParseInputStringToTable] Script Date: 04/21/2009 13:43:36 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /*** BEGIN CREATE FUNCTION [dbo].[funcParseInputStringToTable] ***/

    CREATE FUNCTION [dbo].[funcParseInputStringToTable]

    (@InputString nvarchar(255))

    /* SPECIFY TEMPORARY TABLE*/

    RETURNS @InputStringTable TABLE

    (InputValue nvarchar(255))

    BEGIN

    /* DECLARE VARIABLES*/

    DECLARE @Index1 AS nvarchar(255),

    @ParameterValue AS nvarchar(255)

    /* SET VARIABLES */

    SET @Index1 = CHARINDEX(',', @InputString)

    /* LOOP THROUGH THE INPUT STRING AND IDENTIFY THE INDIVIDUAL VALUES */

    WHILE (@Index1 > 0 OR LEN(@InputString) > 0)

    BEGIN

    /* PARSE OUT EACH INDIVIDUAL PARAMETER AND STORE IN THE TEMPORARY TABLE */

    IF @Index1 > 0

    BEGIN

    SET @ParameterValue = Left(@InputString,@Index1 - 1)

    SET @InputString = Right(@InputString,Len(@InputString) - @Index1)

    END

    ELSE

    BEGIN

    SET @ParameterValue = @InputString

    SET @InputString = ''

    END

    INSERT @InputStringTable (InputValue)

    VALUES(CAST(@ParameterValue AS nvarchar(255)))

    /* PREPARE TO LOOP */

    SET @Index1 = CHARINDEX(',', @InputString)

    END

    /* RETURN THE VALUES FROM THE INPUT STRING */

    RETURN

    END

    Then by calling this function in the SP itself we were able to correct this issue with SSRS and Multivalued parameters. By using this method we kept the security and flexibility a function offers without touching XML etc.

    😉

  • Keith Dunn

    SSC Veteran

    Points: 299

    We ran into problems with parsing the comma delimited string into a table because some data contains the comma delimiter. That is why we went to an XML approach.

Viewing 15 posts - 1 through 15 (of 70 total)

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