Using XML to pass Multi-Select parameters from SSRS to SQL Server

  • Comments posted to this topic are about the item Using XML to pass Multi-Select parameters from SSRS to SQL Server

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Maybe I have missed something or you are talking about a totally different thing but when I use a multi-select in SSRS I just use the parameter like this:

    SELECT Columns FROM Table WHERE Column1 IN (@MultiParam)

    no?

  • That works let you let RS execute the query, but you can't just pass it to the proc that way, sees it as one big string, not discrete values. It's possible to parse that string of course and use it server side.

    Only complaint with the article is that your code doesn't plan for the use of escape characters - maybe rare, but not impossible to have a " " embedded in a value.

  • What if the user doesn't choose any values because they don't want to filter the report on this parameter? I believe this solution would return no data to the report instead of all.

  • Thank you for your posting, it is very helpful. Just want to point out when setting the return value of the function to the dataset parameter, it needs to reference the Code class(i.e. =Code.ReturnXML(....)).

  • Yes.. SSRS doesn't function with passing multivalue params to procs out of the box.. however, with some minor code efforts you can do it..

    if you wanted to build a report off of the northwind db to pick customers from multiple countries..

    where the proc to take in and parse multivalues.. I am sure it could use work..

    Anyway.. I never though of using XML.. thanks for the tip

    create proc [dbo].[multivalueinputproc] (@country nvarchar(1024)=null)

    as

    set nocount on

    declare @countrytable table(Code nvarchar(75))

    if IsNull(@country, '')=''

    Begin

    set @country='ALL'

    End

    --print @country

    Else

    While Len(@country) > 0

    Begin

    If patindex('%,%', @country)> 0

    Begin

    insert into @countrytable

    values (rtrim(substring(@country, 1, patindex('%,%', @country)-1)))

    set @country = ltrim(substring(@country, patindex('%,%', @country)+1, 1024))

    End

    Else

    Begin

    insert into @countrytable

    values (ltrim(rtrim(@country)))

    break

    End

    End

    --print @country

    if (select @country) = 'ALL'

    Begin

    select * from customers

    End

    Else

    Begin

    select * from customers

    where country in (select * from @countrytable)

    Order by country

    End

  • This is the same method I use and it works perfectly everytime

  • Nice one...

  • I get an error - Next control variable does not match For Loop control variable 'sParamItem' should I be customizing any of the function in the code property?

  • lklein (5/8/2008)


    I get an error - Next control variable does not match For Loop control variable 'sParamItem' should I be customizing any of the function in the code property?

    This is the exact code I'm using in my library:

    Public Shared Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String, ByVal Element As String) As String

    '**************************************************************************

    ' Returns an XML string by using the specified values.

    ' Parameters:

    ' MultiValueList - a multi value list from SSRS

    ' Root, Node, Element - String to use in building the XML string

    '**************************************************************************

    Dim ReturnString = ""

    Dim sParamItem As Object

    ReturnString = " "

    For Each sParamItem In MultiValueList

    ReturnString &= " "

    Next

    ReturnString &= " "

    Return (ReturnString)

    End Function

    to use in the Code section of the report, remove the "public shared"

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Andy Warren (5/8/2008)


    That works let you let RS execute the query, but you can't just pass it to the proc that way, sees it as one big string, not discrete values. It's possible to parse that string of course and use it server side.

    Only complaint with the article is that your code doesn't plan for the use of escape characters - maybe rare, but not impossible to have a " " embedded in a value.

    In all of my reports, the multi-value lists are (mostly) populated from a call to the db via a stored proc. I guess it's possible to have escape characters in there... I just hadn't considered it.

    Glad you liked the article!:)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Daniel Durrans (5/8/2008)


    Maybe I have missed something or you are talking about a totally different thing but when I use a multi-select in SSRS I just use the parameter like this:

    SELECT Columns FROM Table WHERE Column1 IN (@MultiParam)

    no?

    What you have will work if you're querying directly to the db.

    What I wrote about is when using a stored procedure, and trying to pass the values to a parameter. In this case, you can't use the variable like you did above.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Ok just changed the code property to :

    Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String, ByVal Element As String) As String

    '**************************************************************************

    ' Returns an XML string by using the specified values.

    ' Parameters:

    ' MultiValueList - a multi value list from SSRS

    ' Root, Node, Element - String to use in building the XML string

    '**************************************************************************

    Dim ReturnString = ""

    Dim sParamItem As Object

    ReturnString = " "

    For Each sParamItem In MultiValueList

    ReturnString &= " "

    Next

    ReturnString &= " "

    Return (ReturnString)

    End Function

    now my error is 'parameter '@C' contains an error: [] Name ReturnXML is not declared.

    My dataset Parameter code is: =ReturnXML(Parameters!C.Value,"C","Customer","Customer_Number") - C being the name of the multivalue parameter which is defined as string the the parameter details.

  • Ok just changed the code property to :

    Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String, ByVal Element As String) As String

    '**************************************************************************

    ' Returns an XML string by using the specified values.

    ' Parameters:

    ' MultiValueList - a multi value list from SSRS

    ' Root, Node, Element - String to use in building the XML string

    '**************************************************************************

    Dim ReturnString = ""

    Dim sParamItem As Object

    ReturnString = " "

    For Each sParamItem In MultiValueList

    ReturnString &= " "

    Next

    ReturnString &= " "

    Return (ReturnString)

    End Function

    now my error is 'parameter '@C' contains an error: [] Name ReturnXML is not declared.

    My dataset Parameter code is: =ReturnXML(Parameters!C.Value,"C","Customer","Customer_Number") - C being the name of the multivalue parameter which is defined as string the the parameter details.

  • david (5/8/2008)


    What if the user doesn't choose any values because they don't want to filter the report on this parameter? I believe this solution would return no data to the report instead of all.

    A multi-value list parameter must have at least one item chosen for you to view the report, and you can't select for it to be null. A solution to your scenario would be to do the "Select All" that is available for multi-value parameters.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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