Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

The Multi-valued Parameters problem in Reporting Services Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 2:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 37,994, Visits: 34,901
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #970724
Posted Wednesday, August 18, 2010 11:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 12:24 PM
Points: 5, Visits: 16
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!
Post #971335
Posted Tuesday, September 11, 2012 3:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 30, 2015 7:57 AM
Points: 5, Visits: 93
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'
Post #1357229
Posted Thursday, October 4, 2012 1:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:09 PM
Points: 62, Visits: 97
Hi,
Please let me know how to use this function in report.
Post #1368196
Posted Thursday, October 4, 2012 3:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:09 PM
Points: 62, Visits: 97
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'
Post #1368259
Posted Thursday, October 4, 2012 7:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:09 PM
Points: 62, Visits: 97
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.

Post #1368795
Posted Thursday, April 10, 2014 3:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 9, 2015 8:22 AM
Points: 11, Visits: 190
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
Post #1560641
Posted Friday, April 11, 2014 9:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 9, 2015 8:22 AM
Points: 11, Visits: 190
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
Post #1561024
Posted Monday, February 23, 2015 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 23, 2015 6:22 AM
Points: 1, Visits: 0
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
Post #1662526
Posted Monday, February 23, 2015 11:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 20, 2015 1:13 PM
Points: 28, Visits: 331
What SSRS version do you using, this problem was corrected in SSRS 2008
Post #1662663
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse