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: Yesterday @ 9:25 PM
Points: 35,372, Visits: 31,925
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: Thursday, August 7, 2014 11:08 PM
Points: 5, Visits: 83
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: Monday, September 29, 2014 1:00 PM
Points: 11, Visits: 177
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: Monday, September 29, 2014 1:00 PM
Points: 11, Visits: 177
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
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse