The Multi-valued Parameters problem in Reporting Services

  • Jeff Moden

    SSC Guru

    Points: 994523

    gary.bland (8/17/2010)


    What I am trying to accomplish is when I choose a parameter I need to pass the result into another parameter in order to limit the number of available selections. An example would be State and City. Selecting a State would then limit the selection of Cities.

    In your example, you'd simply have a two columns... one for city, one for state. Can't do this unless you can relate a city to a state.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • gary.bland

    SSC Journeyman

    Points: 81

    Thanks so much. After sleeping on it last night I finally got it to work. The problem lie within Reporting Services not the function. Thanks again!

  • drickus

    Valued Member

    Points: 73

    Hi

    Is the above code compatible in the SSRS 2012.

    I get the following error.

    Unable to cast object of type 'System.Object[]' to type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameter'

  • Lucky's

    SSChasing Mays

    Points: 616

    Hi,

    Please let me know how to use this function in report.

  • Lucky's

    SSChasing Mays

    Points: 616

    Am getting the below error in Report when i used the above functions:

    UNABLE TO CAST object of type 'system.object[]' to type 'microsoft.reporting services.reporting processes.ReportobjectModel.parameter'

  • Lucky's

    SSChasing Mays

    Points: 616

    Hi ,

    Tried using the above code

    Am getting the error in report as

    UNABLE TO CAST object of type 'system.object[]' to type 'microsoft.reporting services.reporting processes.ReportobjectModel.parameter'

    Could you please explain how to use these functions /where to call these fns.

  • daniel bliss

    SSC Veteran

    Points: 217

    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

  • daniel bliss

    SSC Veteran

    Points: 217

    Well, I answered my own question, which I will share here in case it is of use to anyone else.

    My situation is that I had a very long list of StudentID's as a multi-valued parameter is SSRS (2008)--so long that it could sometimes exceed the 8000 char limit for using varchar as a datatype in my stored procedure. All I care about here are the StudentID's (parameter value), not their names (parameter label).

    I added the following code to the custom code block in Report Properties in SSRS:

    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 & "<M>" & Cstr(obj.Value(i)) & "</M>"

    Next

    Else

    strXML = strXML & "<M>" & Cstr(obj.Value(i)) & "</M>"

    End IF

    Return strXML

    End Function

    In the dataset that calls my stored procedure, I invoke this function as an expression for the Student parameter that is passed to the sp:

    =Code.StrParmToXML(Parameters!Student)

    On the SQL side, I parse the xml parameter to table rows using the following function:

    create function [dbo].[fn_xml_to_table] (@parmxml xml) RETURNS TABLE

    AS

    RETURN

    with name_cte as

    (

    select @parmxml as Names

    ),

    name_parse_rows as

    (

    select

    ltrim(rtrim(Split.a.value('.', 'VARCHAR(100)'))) AS item

    from name_cte

    CROSS APPLY Names.nodes('/M') Split(a)

    ) select * from name_parse_rows

    I then invoke the function to select all students using the following sql statement:

    select

    UserID

    ,StudentName

    from v_Students

    where UserID IN (select item from dbo.fn_xml_to_table(@xmlUserList_in));

    Dan

  • lazithkumarkm

    SSC Journeyman

    Points: 83

    Hi,

    Thanks a lot for your post :-).

    Could you please explain the steps to implement the following

    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.

    we are using a data set query instead of stored procedure(sproc) to populate the parameter values(>5000 records)

    We have created the code,SQL function but we are unable to integrate within the SSRS report.

    It would be of great help if you could post the steps at the earliest since we got stuck with this issue and not able to move forward 🙁

    Thanks in Advance

    Rlk

  • Alex Grinberg-230981

    SSC Eights!

    Points: 896

    What SSRS version do you using, this problem was corrected in SSRS 2008

Viewing 10 posts - 61 through 70 (of 70 total)

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