InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 00:46:24 GMT20http://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxYou can either use it in the Code tab of the report properties, or you can build it into a library and link the dll into the report from the References tab of the report properties.Fri, 05 Oct 2012 07:13:22 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxHi,very useful code..can u please let me know how to call this custom code function from SSRS ?Thu, 04 Oct 2012 21:11:26 GMTLucky'shttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]WayneS (5/12/2008)[/b][hr]Hello again...As you can see, posting XML strings is stripping everything out.I've recoded this to build the string in a more complex method. Hopefully, it will post okay.This code, when I run it, returns 2 records. What do you get?[code]declare @start char(1), @end char(1), @Root char(1), @Node char(8), @Element char(15)select @Start = char(60), @end = char(62), @Root = 'C', @Node = 'CUSTOMER', @Element = 'CUSTOMER_NUMBER'declare @C XMLset @C = @Start + @Root + @End + @Start + @Node + @End + @Start + @Element + @End + '1' + @Start + '/' + @Element + @End + @Start + '/' + @Node + @End + @Start + @Node + @End + @Start + @Element + @End + '2' + @Start + '/' + @Element + @End + @Start + '/' + @Node + @End + @Start + '/' + @Root + @Endselect m.item.value('CUSTOMER_NUMBER[1]','integer') [Customer_Number] from @C.nodes('/C/CUSTOMER') as m(item)[/code][/quote]I get 1 & 2 too.But I haven't managed to pass the multi value paramater as an XML to my sproc.Did anyone got it to work?This is my thread:http://www.sqlservercentral.com/Forums/Topic1115348-150-1.aspx#bm1115415Thanks.Fri, 27 May 2011 08:17:12 GMTnzarradhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxYes - I get 1,2Mon, 12 May 2008 07:31:29 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxHello again...As you can see, posting XML strings is stripping everything out.I've recoded this to build the string in a more complex method. Hopefully, it will post okay.This code, when I run it, returns 2 records. What do you get?[code]declare @start char(1), @end char(1), @Root char(1), @Node char(8), @Element char(15)select @Start = char(60), @end = char(62), @Root = 'C', @Node = 'CUSTOMER', @Element = 'CUSTOMER_NUMBER'declare @C XMLset @C = @Start + @Root + @End + @Start + @Node + @End + @Start + @Element + @End + '1' + @Start + '/' + @Element + @End + @Start + '/' + @Node + @End + @Start + @Node + @End + @Start + @Element + @End + '2' + @Start + '/' + @Element + @End + @Start + '/' + @Node + @End + @Start + '/' + @Root + @Endselect m.item.value('CUSTOMER_NUMBER[1]','integer') [Customer_Number] from @C.nodes('/C/CUSTOMER') as m(item)[/code]Mon, 12 May 2008 06:53:39 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxWayne,I've put the original code back in the code property and have the same results - nothing returns. However, now that it's back to your code - is there anything on the sql server that needs to be set to allow XML?Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String, ByVal Element As String) As StringDim ReturnString = "" Dim sParamItem As Object ReturnString = " " For Each sParamItem In MultiValueList ReturnString &= " " Next ReturnString &= " "Return (ReturnString)End FunctionFri, 09 May 2008 08:13:31 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]Adriaan Davel (5/9/2008)[/b][hr]Hi Wayne,Nice one, have you looked at the performance of using the XML joins in your stored procedure? [/quote]In my "casual" testing, XML datasets up to about 1000 "records" performed pretty fast. Beyond 1000 it started slowing down; when it got to 5000 it was crawling, and I stopped one query with > 20,000 elements after about an hour. Most of the XML datasets I use are in dealing with SSRS, where the users want to be able to select > 1 option. Most of these selections are kept down to 15000. but, they've been told...Fri, 09 May 2008 06:00:27 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxHi Wayne,Nice one, have you looked at the performance of using the XML joins in your stored procedure? I did quite a bit of performance testing with the "other" methods [url=http://www.sqlservercentral.com/articles/Development/3138/][/url] and the results were quite interesting, would love to see how XML performs with this.Fri, 09 May 2008 01:16:49 GMTAdriaan Davelhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]lklein (5/8/2008)[/b][hr]This is the function in the code section - which is different from the beginning article.[/quote]If you're not using the function in the beginning article, I don't know what you're doing. And the post you sent is removing all of the xml tags, so all I see is a string of spaces.Thu, 08 May 2008 16:43:14 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxThis is the function in the code section - which is different from the beginning article.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 FunctionThu, 08 May 2008 15:42:28 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]lklein (5/8/2008)[/b][hr]This is so frustrating... yikes - I get an error running that just in design view right?[/quote]Yes, the RaisError raises an error. In Preview mode, you'll see the xml line that you're passing to the procedure.Thu, 08 May 2008 15:38:42 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxThe following code (based off of your query) works for me. Note that I build the XML string by hand, but your XML string should look similiar.[Code]declare @C XMLset @C = ' 'select m.item.value('CUSTOMER_NUMBER[1]','integer') [Customer_Number] from @C.nodes('/C/CUSTOMER') as m(item)[/Code]The result set I get back is:Customer_Number--------------12There must be a problem with the XML string that you are passing to the @C parameter.Thu, 08 May 2008 15:36:06 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxThis is so frustrating... yikes - I get an error running that just in design view right?here's the whole detail===================================An error occurred while executing the query.10008 (Microsoft Report Designer)===================================10008 (.Net SqlClient Data Provider)------------------------------For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=50000&LinkId=20476------------------------------Server Name: x.x.comError Number: 50000Severity: 16State: 1Procedure: spCUSTOMER_PROMOLine Number: 7------------------------------Program Location: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.ExecuteQuery()Thu, 08 May 2008 15:34:14 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxAs far as your XML code goes, I don't see anything wrong with what you're doing.I'd really like to see the XML string that's being passed to the report.How about trying this code out? (I consolidated your code into one select statement, and formatted it some to make it easier for me to read).When you call this from your report, you should get the XML string that you're passing to the procedure. Can you copy it and put it up here?[Code]ALTER proc [dbo].[spCUSTOMER_PROMO] @C XML--, @INPUT_DATE DATETIMEASdeclare @c1 varchar(max)set @c1 = convert(varchar(max), @C)RaisError(@c1, 16, 1)declare @temp varchar(25) -- need something since two fields are never inserted into, but needed for outputselect CS.CUSTOMER_NAME CUSTOMER, TA.CUSTOMER_NUMBER, CASE WHEN TP.TP_PROGRAM_ID = 'PREMIUM' THEN TD.TP_DISCOUNT_AMOUNT ELSE '' END PREM, CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101) PREM_VALID, TA.TP_PROMOTION_CODE, CASE WHEN TP.TP_PROGRAM_ID = 'FREIGHT' THEN TD.TP_DISCOUNT_AMOUNT ELSE '' END FREIGHT, @temp FREIGHT_VALID, @temp FREIGHT_CODE FROM TP_CUSTOMER_ASSOCIATIONS AS TA INNER JOIN TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE INNER JOIN CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER INNER JOIN TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE INNER JOIN @C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer') WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE AND TP.TP_PROGRAM_ID IN('PREMIUM', 'FREIGHT') ORDER BY CS.CUSTOMER_NAME-- =Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")[/Code]Thu, 08 May 2008 15:24:41 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxFirst off thank you so much for trying to help me... I've tried the passing and parsing out - that doesn't work. I'm wondering if there's a setting I need.The raise error - I'm not sure how that works but it didn't return anything - =Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")and the sp:ALTER proc [dbo].[spCUSTOMER_PROMO] @C XML--, @INPUT_DATE DATETIMEASdeclare @INPUT AS table ( CUSTOMER VARCHAR(35), CUSTOMER_NUMBER VARCHAR(10), PREM NUMERIC(18,2), PREM_VALID VARCHAR(25), PREM_CODE VARCHAR(25), FREIGHT NUMERIC(18,2), FREIGHT_VALID VARCHAR(25), FREIGHT_CODE VARCHAR(25) )INSERT INTO @INPUT (CUSTOMER,CUSTOMER_NUMBER,PREM,PREM_VALID,PREM_CODE)SELECT CS.CUSTOMER_NAME, TA.CUSTOMER_NUMBER,TD.TP_DISCOUNT_AMOUNT,CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101),TA.TP_PROMOTION_CODEFROM TP_CUSTOMER_ASSOCIATIONS AS TA INNER JOIN TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE INNER JOIN CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER INNER JOIN TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE INNER JOIN@C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE AND--(CS.CUSTOMER_NUMBER IN (SELECT VALUE FROM dbo.FX_SPLIT(@C,','))) and TP.TP_PROGRAM_ID IN ('PREMIUM')INSERT INTO @INPUT (CUSTOMER,CUSTOMER_NUMBER,FREIGHT,PREM_VALID,PREM_CODE)SELECT CS.CUSTOMER_NAME, TA.CUSTOMER_NUMBER,TD.TP_DISCOUNT_AMOUNT,CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101),TA.TP_PROMOTION_CODEFROM TP_CUSTOMER_ASSOCIATIONS AS TA INNER JOIN TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE INNER JOIN CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER INNER JOIN TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE INNER JOIN@C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE AND--(CS.CUSTOMER_NUMBER IN (SELECT * FROM dbo.fnDStringToTable(@C,','))) and TP.TP_PROGRAM_ID IN ('FREIGHT')select * from @INPUT ORDER BY CUSTOMERThu, 08 May 2008 14:48:45 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]lklein (5/8/2008)[/b][hr]Dang I wish that were it - it's all caps in both the sp and in the report pieces.[/quote]Is everything is spelled the same?Well, can you:1. Post the code you are doing with the ReturnXML(), and2. Post the procedure code (at least the parameters and select statement), and3. Post the XML string you're getting from the RaisError code above.Thu, 08 May 2008 14:42:57 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxthis is what I have in my spINNER JOIN@C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')Thu, 08 May 2008 14:41:03 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxDang I wish that were it - it's all caps in both the sp and in the report pieces.Thu, 08 May 2008 14:38:14 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxI think I found your problem (at least I hope so)...you are calling:=Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")and in the procedure you are going:INNER JOIN@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')I just ran a test, and found out that this is CaSe SeNsItIvE.Change your select to:INNER JOIN@C.nodes('/C/CUSTOMER') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('CUSTOMER_NUMBER[1]','integer')If you're getting no result set, then the improper case is in the nodes(). If you only get a NULL result, then it's in the value().Most likely, it's in both.Thu, 08 May 2008 14:33:40 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxThe declare @c1 varchar(max)set @c1 = convert(varchar(max), @C)returns my params...Thu, 08 May 2008 14:24:20 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxC=Root is the parameter name both in the report and the sp and there is only this paramCustomer=Node is the displayed label name of the paramCustomer Number=Element is the actual value of the paramI ran the select sample code and just ran it as the sp code then ran it in the report data and it returned nothing.I would love for this to work. Thank you for trying to help me. Any other ideas?Thu, 08 May 2008 14:22:21 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]lklein (5/8/2008)[/b][hr]ok - adding the code.Return... enable it to run - however with no results.Is this right for in my spC=my parameter nameCustomer=labelCustomer_Number=int valueINNER JOIN@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')please... anyone... thank you![/quote]As you wrote the query, you should have a parameter in your stored procedure defined as @C XML.I don't see anything wrong with your query. If it still doesn't return anything, try adding this to your procedure. When you run the report, you'll see what you're sending it.[Code]declare @c1 varchar(max)set @c1 = convert(varchar(max), @C)RaisError (@c1, 16, 1)[/Code]At this point, you can copy the XML string to SSMS and debug your proc from there.Also, you can run this to see if the XML string is processing correctly:[Code]select m.item.value('Customer_Number[1]', 'integer') CustomerNumber from @C.nodes('/C/Customer') AS m(item)[/Code]Just to clarify:C=RootCustomer=NodeCustomer_Number=ElementThu, 08 May 2008 14:13:02 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxMore 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=10020I guess I would have expected it to either be not readable or in xml form...Please - thank you...Thu, 08 May 2008 14:09:49 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxYes I did that - can you read my last post - it runs now but doesn't return anything - should I have the [1] on there?Thu, 08 May 2008 13:57:40 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]lklein (5/8/2008)[/b][hr]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.[/quote]try changing that to =Code.ReturnXML(...)Thu, 08 May 2008 13:55:29 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxok - adding the code.Return... enable it to run - however with no results.Is this right for in my spC=my parameter nameCustomer=labelCustomer_Number=int valueINNER JOIN@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')please... anyone... thank you!Thu, 08 May 2008 13:50:28 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]bo (5/8/2008)[/b][hr]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(....)).[/quote]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(...)Thu, 08 May 2008 13:45:09 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]david (5/8/2008)[/b][hr]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.[/quote]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.Thu, 08 May 2008 13:42:49 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxOk 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 Functionnow 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.Thu, 08 May 2008 13:42:47 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxOk 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 Functionnow 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.Thu, 08 May 2008 13:39:00 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]Daniel Durrans (5/8/2008)[/b][hr]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?[/quote]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.Thu, 08 May 2008 13:36:42 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]Andy Warren (5/8/2008)[/b][hr]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.[/quote]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!:)Thu, 08 May 2008 13:35:13 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspx[quote][b]lklein (5/8/2008)[/b][hr]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?[/quote]This is the exact code I'm using in my library:[code] 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[/code]to use in the Code section of the report, remove the "public shared"Thu, 08 May 2008 13:31:15 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxI 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?Thu, 08 May 2008 13:08:15 GMTlkleinhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxNice one...Thu, 08 May 2008 10:12:19 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxThis is the same method I use and it works perfectly everytimeThu, 08 May 2008 07:23:40 GMTJohn.Schreckhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxYes.. 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[code]create proc [dbo].[multivalueinputproc] (@country nvarchar(1024)=null)asset nocount ondeclare @countrytable table(Code nvarchar(75))if IsNull(@country, '')='' Begin set @country='ALL' End--print @countryElse 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 @countryif (select @country) = 'ALL' Begin select * from customers EndElse Begin select * from customers where country in (select * from @countrytable) Order by country End[/code]Thu, 08 May 2008 07:14:12 GMTchristopher.dorchhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxThank 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(....)).Thu, 08 May 2008 06:18:21 GMTbo-143158http://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxWhat 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.Thu, 08 May 2008 06:13:04 GMTdelishhttp://www.sqlservercentral.com/Forums/Topic496838-1273-1.aspxThat 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.Thu, 08 May 2008 05:46:29 GMTAndy Warren