Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using XML to pass Multi-Select parameters from SSRS to SQL Server


Using XML to pass Multi-Select parameters from SSRS to SQL Server

Author
Message
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
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!Smile

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
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

WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
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
Author - SQL Server T-SQL Recipes
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

lklein
lklein
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 286
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.
lklein
lklein
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 286
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.
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
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
Author - SQL Server T-SQL Recipes
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

WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
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
Author - SQL Server T-SQL Recipes
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

lklein
lklein
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 286
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!
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
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
Author - SQL Server T-SQL Recipes
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

lklein
lklein
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 286
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?
lklein
lklein
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 286
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...
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