May 7, 2008 at 9:53 pm
Comments posted to this topic are about the item Using XML to pass Multi-Select parameters from SSRS to SQL Server
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2008 at 12:40 am
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?
May 8, 2008 at 5:46 am
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.
May 8, 2008 at 6:13 am
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.
May 8, 2008 at 6:18 am
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(....)).
May 8, 2008 at 7:14 am
Yes.. SSRS doesn't function with passing multivalue params to procs out of the box.. however, with some minor code efforts you can do it..
if you wanted to build a report off of the northwind db to pick customers from multiple countries..
where the proc to take in and parse multivalues.. I am sure it could use work..
Anyway.. I never though of using XML.. thanks for the tip
create proc [dbo].[multivalueinputproc] (@country nvarchar(1024)=null)
as
set nocount on
declare @countrytable table(Code nvarchar(75))
if IsNull(@country, '')=''
Begin
set @country='ALL'
End
--print @country
Else
While Len(@country) > 0
Begin
If patindex('%,%', @country)> 0
Begin
insert into @countrytable
values (rtrim(substring(@country, 1, patindex('%,%', @country)-1)))
set @country = ltrim(substring(@country, patindex('%,%', @country)+1, 1024))
End
Else
Begin
insert into @countrytable
values (ltrim(rtrim(@country)))
break
End
End
--print @country
if (select @country) = 'ALL'
Begin
select * from customers
End
Else
Begin
select * from customers
where country in (select * from @countrytable)
Order by country
End
May 8, 2008 at 7:23 am
This is the same method I use and it works perfectly everytime
May 8, 2008 at 10:12 am
Nice one...
May 8, 2008 at 1:08 pm
I get an error - Next control variable does not match For Loop control variable 'sParamItem' should I be customizing any of the function in the code property?
May 8, 2008 at 1:31 pm
lklein (5/8/2008)
I get an error - Next control variable does not match For Loop control variable 'sParamItem' should I be customizing any of the function in the code property?
This is the exact code I'm using in my library:
Public Shared 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
to use in the Code section of the report, remove the "public shared"
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2008 at 1:35 pm
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
Author - SQL Server T-SQL Recipes
May 8, 2008 at 1:36 pm
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
May 8, 2008 at 1:39 pm
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.
May 8, 2008 at 1:42 pm
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.
May 8, 2008 at 1:42 pm
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
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply