SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Multi-valued Parameters problem in Reporting Services


The Multi-valued Parameters problem in Reporting Services

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85428 Visits: 41078
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gary.bland
gary.bland
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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!
drickus
drickus
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 101
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
Lucky's
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 97
Hi,
Please let me know how to use this function in report.
Lucky's
Lucky's
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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'
Lucky's
Lucky's
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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.
daniel bliss
daniel bliss
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 197
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
daniel bliss
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 197
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
lazithkumarkm
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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 Sad

Thanks in Advance
Rlk
Alex Grinberg-230981
Alex Grinberg-230981
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 411
What SSRS version do you using, this problem was corrected in SSRS 2008
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search