SSRS Multi value dropdownlist as an XML paramater

Question

SSRS Multi value dropdownlist as an XML paramater

nzarrad

SSC Enthusiast

Points: 199
More actions
May 26, 2011 at 5:50 am

#238255

Hi All,
My SSRS report has multi value paramater which displays a list of country names. The county names will exceed 8000 chars easily that's why I'm using XML.
I'm converting the list of country names into XML then passing that to my sproc where I call another function to convert the XML into a table.
That's what I want to do but I can't get it to work.
My query to populate the dropdownlist just return a field called "CountryName"
Something like this:
SELECT 'UK' AS CountryName
UNION
SELECT 'USA'
The function, which I pasted in the code section in SSRS is this(copied from this forum):
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
This is the sql function to convert the xml into a table:
ALTER function [dbo].[rf_ParameterTableNew] (@parmxml xml) RETURNS TABLE
AS
RETURN
select m.item.value('COUNTRYNAME[1]','varchar(max)') [CountryName]
from @parmxml.nodes('/c/COUNTRY') as m(item)
This the code I'm using in Paramaters section in SSRS:
=CODE.ReturnXML(Parameters!prCountries.Value,"c","Country","CountryName")
It's not erroring but I'm getting nothing back.
Where am I going wrong?
Thanks for your help.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I use varchar(max) and I never had a problem with 8K limit as a parameter... what problem are you getting with this option?

nzarrad SSC Enthusiast Points: 199 More actions · Answer 2

Ninja's_RGR'us (5/26/2011)
I use varchar(max) and I never had a problem with 8K limit as a parameter... what problem are you getting with this option?

When the 8k limit is exceeded the param will be truncated. The report doesn't error but the info will be incomplete & incorrect. This is why I'm converting the param into xml, but not getting anywhere at this moment.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Why not use a country code? I mean 250?? countries X 5 char is still WAY under 8000.

nzarrad SSC Enthusiast Points: 199 More actions · Answer 4

Ninja's_RGR'us (5/26/2011)
Why not use a country code? I mean 250?? countries X 5 char is still WAY under 8000.

You are right there. The country is just an example and I have many reports where the 8k limit is causing me a real headache. I believe this issue is sorted out in 2008 but I'm using version 2005 and I can't find anyother solution except the xml one. That's why I'm hoping someone would help with this.

Thanks.

Jaselnewpar SSC Eights! Points: 979 More actions · Answer 5

For multi-value drop downs I insist on using ID's as my data value. If I don't have an ID to work with in my data I create my own by selecting the data and using ROW_NUMBER to create an ID.

http://msdn.microsoft.com/en-us/library/ms186734.aspx

This data can be collected before hand in a temp table or CTE and then joined together in your main dataset query.

Use the same query in a dataset used for your report parameter and viola; ID to ID parameter matching.

This will keep performance optimized, and avoid any issues you may happen across when strange characters are introduced into your text values.

nzarrad SSC Enthusiast Points: 199 More actions · Answer 6

Jaselnewpar (5/26/2011)
For multi-value drop downs I insist on using ID's as my data value. If I don't have an ID to work with in my data I create my own by selecting the data and using ROW_NUMBER to create an ID.
http://msdn.microsoft.com/en-us/library/ms186734.aspx
This data can be collected before hand in a temp table or CTE and then joined together in your main dataset query.
Use the same query in a dataset used for your report parameter and viola; ID to ID parameter matching.
This will keep performance optimized, and avoid any issues you may happen across when strange characters are introduced into your text values.

Thanks for you reply. I'll give that a go.

I'll be interesting to get the XML example to work.

Theres a smilar thread here:

http://www.sqlservercentral.com/Forums/Topic496838-1273-4.aspx