The Multi-valued Parameters problem in Reporting Services

Question

The Multi-valued Parameters problem in Reporting Services

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

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

gary.bland SSC Journeyman Points: 81 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

Hi,

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

Lucky's SSChasing Mays Points: 616 More actions · Answer 5

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 More actions · Answer 6

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: 244 More actions · Answer 7

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: 244 More actions · Answer 8

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 More actions · Answer 9

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: 897 More actions · Answer 10

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