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 ««12345»»»

Using XML to pass Multi-Select parameters from SSRS to SQL Server Expand / Collapse
Author
Message
Posted Thursday, May 8, 2008 1:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
Andy Warren (5/8/2008)
That works let you let RS execute the query, but you can't just pass it to the proc that way, sees it as one big string, not discrete values. It's possible to parse that string of course and use it server side.

Only complaint with the article is that your code doesn't plan for the use of escape characters - maybe rare, but not impossible to have a " " embedded in a value.



In all of my reports, the multi-value lists are (mostly) populated from a call to the db via a stored proc. I guess it's possible to have escape characters in there... I just hadn't considered it.

Glad you liked the article!:)


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497389
Posted Thursday, May 8, 2008 1:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
Daniel Durrans (5/8/2008)
Maybe I have missed something or you are talking about a totally different thing but when I use a multi-select in SSRS I just use the parameter like this:

SELECT Columns FROM Table WHERE Column1 IN (@MultiParam)


no?


What you have will work if you're querying directly to the db.
What I wrote about is when using a stored procedure, and trying to pass the values to a parameter. In this case, you can't use the variable like you did above.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497391
Posted Thursday, May 8, 2008 1:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 22, 2014 3:06 PM
Points: 64, Visits: 263
Ok just changed the code property to :
Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String, ByVal Element As String) As String
'**************************************************************************
' Returns an XML string by using the specified values.
' Parameters:
' MultiValueList - a multi value list from SSRS
' Root, Node, Element - String to use in building the XML string
'**************************************************************************
Dim ReturnString = ""
Dim sParamItem As Object
ReturnString = " "
For Each sParamItem In MultiValueList
ReturnString &= " "
Next
ReturnString &= " "
Return (ReturnString)
End Function

now my error is 'parameter '@C' contains an error: [] Name ReturnXML is not declared.

My dataset Parameter code is: =ReturnXML(Parameters!C.Value,"C","Customer","Customer_Number") - C being the name of the multivalue parameter which is defined as string the the parameter details.
Post #497394
Posted Thursday, May 8, 2008 1:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 22, 2014 3:06 PM
Points: 64, Visits: 263
Ok just changed the code property to :
Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String, ByVal Element As String) As String
'**************************************************************************
' Returns an XML string by using the specified values.
' Parameters:
' MultiValueList - a multi value list from SSRS
' Root, Node, Element - String to use in building the XML string
'**************************************************************************
Dim ReturnString = ""
Dim sParamItem As Object
ReturnString = " "
For Each sParamItem In MultiValueList
ReturnString &= " "
Next
ReturnString &= " "
Return (ReturnString)
End Function

now my error is 'parameter '@C' contains an error: [] Name ReturnXML is not declared.

My dataset Parameter code is: =ReturnXML(Parameters!C.Value,"C","Customer","Customer_Number") - C being the name of the multivalue parameter which is defined as string the the parameter details.
Post #497397
Posted Thursday, May 8, 2008 1:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
david (5/8/2008)
What if the user doesn't choose any values because they don't want to filter the report on this parameter? I believe this solution would return no data to the report instead of all.


A multi-value list parameter must have at least one item chosen for you to view the report, and you can't select for it to be null. A solution to your scenario would be to do the "Select All" that is available for multi-value parameters.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497398
Posted Thursday, May 8, 2008 1:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
bo (5/8/2008)
Thank you for your posting, it is very helpful. Just want to point out when setting the return value of the function to the dataset parameter, it needs to reference the Code class(i.e. =Code.ReturnXML(....)).


good point.

If you're using the function in the Code tab of the report properties, it gets referenced as:
=Code.ReturnXML(...)

If you're using the function in an assembly, it gets referenced as:
=LibraryName.ClassName.ReturnXML(...)


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497400
Posted Thursday, May 8, 2008 1:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 22, 2014 3:06 PM
Points: 64, Visits: 263
ok - adding the code.Return... enable it to run - however with no results.

Is this right for in my sp
C=my parameter name
Customer=label
Customer_Number=int value

INNER JOIN
@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')

please... anyone... thank you!
Post #497406
Posted Thursday, May 8, 2008 1:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
lklein (5/8/2008)
My dataset Parameter code is: =ReturnXML(Parameters!C.Value,"C","Customer","Customer_Number") - C being the name of the multivalue parameter which is defined as string the the parameter details.


try changing that to =Code.ReturnXML(...)


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497407
Posted Thursday, May 8, 2008 1:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 22, 2014 3:06 PM
Points: 64, Visits: 263
Yes I did that - can you read my last post - it runs now but doesn't return anything - should I have the [1] on there?
Post #497410
Posted Thursday, May 8, 2008 2:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 22, 2014 3:06 PM
Points: 64, Visits: 263
More info: here's what I have in the Dataset Parameter Value
=Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")

but when I go look at the ExecutionLog the Parameter Passed is...
C=10165&C=10008&C=10162&C=10009&C=10007&C=10137&C=10179&C=10171&C=10002&C=10003&C=10001&C=10004&C=10154&C=10196&C=10005&C=10122&C=10153&C=10157&C=10006&C=10152&C=10125&C=10172&C=10214&C=10010&C=10159&C=10176&C=10055&C=10063&C=10126&C=10011&C=10012&C=10013&C=10020

I guess I would have expected it to either be not readable or in xml form...

Please - thank you...
Post #497419
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse