August 17, 2010 at 2:36 pm
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
Change is inevitable... Change for the better is not.
August 18, 2010 at 11:35 am
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!
September 11, 2012 at 3:27 am
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'
October 4, 2012 at 1:24 am
Hi,
Please let me know how to use this function in report.
October 4, 2012 at 3:29 am
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'
October 4, 2012 at 7:09 pm
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.
April 10, 2014 at 3:21 pm
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
April 11, 2014 at 9:42 am
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
February 23, 2015 at 6:33 am
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
February 23, 2015 at 11:27 am
What SSRS version do you using, this problem was corrected in SSRS 2008
Viewing 10 posts - 61 through 69 (of 69 total)
You must be logged in to reply to this topic. Login to reply