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 Wednesday, May 7, 2008 9:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
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
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 #496838
Posted Thursday, May 8, 2008 12:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 6:30 AM
Points: 1, Visits: 106
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?
Post #496887
Posted Thursday, May 8, 2008 5:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 6:56 AM
Points: 6,804, Visits: 1,934
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.


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #497021
Posted Thursday, May 8, 2008 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 PM
Points: 1, Visits: 133
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.
Post #497037
Posted Thursday, May 8, 2008 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:14 AM
Points: 6, Visits: 43
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(....)).
Post #497041
Posted Thursday, May 8, 2008 7:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 9:59 AM
Points: 257, Visits: 181
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


Post #497078
Posted Thursday, May 8, 2008 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 9, 2008 8:14 AM
Points: 1, Visits: 4
This is the same method I use and it works perfectly everytime
Post #497090
Posted Thursday, May 8, 2008 10:12 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,471, Visits: 1,402
Nice one...


Post #497244
Posted Thursday, May 8, 2008 1:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:21 AM
Points: 66, Visits: 286
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?
Post #497373
Posted Thursday, May 8, 2008 1:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
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
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 #497388
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse