• Keith Dunn (4/21/2009)


    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 = "<parameter>"

    If obj.IsMultiValue then

    For i = 0 To obj.count - 1

    strXML = strXML & "<item "

    strXML = strXML & "value = " & Chr(34) & Cstr(obj.Value(i)) & Chr(34) & " "

    strXML = strXML & "label = " & Chr(34) & Cstr(obj.Label(i)) & Chr(34) & " "

    strXML = strXML & "/>"

    Next

    Else

    strXML = strXML & "<item "

    strXML = strXML & "value = " & Chr(34) & Cstr(obj.Value) & Chr(34) & " "

    strXML = strXML & "label = " & Chr(34) & Cstr(obj.Label) & Chr(34) & " "

    strXML = strXML & "/>"

    End IF

    strXML = strXML & "</parameter>"

    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)

    )

    Thanks very much for sharing this, Keith: I'm trying to get around the 8000 character limit for a long multi-valued string. Unfortunately, your code above won't compile (SSRS 2008). Can you please send a sample of the desired output so I can get an idea of what might be wrong?

    Thanks,

    Dan