Facing problem in Passing Multiple values to a DataSet
Hi all, I have a report parameter(@rptParameter), which allows only single value. The Main Data Set query : Select * From XXXX Where Title like @rptParameter+'%' This is how i'm handling till now. but now i need to allow users to select multiple values at report param, and need to generate report based on that. I'm totally lost, i don't have any idea that hw can i use "like" and "in" at a time in my Data set. Please help me out. If you not clear with question, let me know i will give you some example. Thanks in Advance.
October 13th, 2010 5:44am
Hello, You just pass the multi-value parameter to the sproc and the sproc should be like below. CREATE PROCEDURE [dbo].[sp_Test] ( @Val NVARCHAR(100) ) AS BEGIN SELECT A FROM ( SELECT '1' AS Col UNION SELECT '2' AS Col UNION SELECT '3' AS Col UNION SELECT '4' AS Col ) A WHERE ',' + @Val + ',' LIKE '%,' + A.Col + ',%' END -- EXEC [dbo].[sp_Test] '1,2' -- Just for checking Hope its helpful...Pavan Kokkula Infosys Technologies Limited.
October 13th, 2010 6:07am
Hi Pavan, Thanks for you reply. I got an idea after seeing your reply. But i did not understand the query you written. When users select multiple values at report parameter,( ex : @rptPar = ('abc','bcd','cde')) I'm passing that value to my main data set query. Now i want those multiple values should be divided and my query needs to be like this Select Column From Table where Column like 'abc'+'%' (This works, if only value passed, but i'm allowing users to select multiple values) so now i struck there. if they select 'abc' from drop down i need to display all records in a column which starts with abc, i.e 'abc'+'%'........ how can i fulfill for multi values?
October 13th, 2010 5:34pm
Please read this great article for the subject http://www.sommarskog.se/arrays-in-sql.htmlBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
October 13th, 2010 5:40pm
Hi DJ3001, We could try to write T-SQL to achieve this as follows: 1. On the report, use the expression =Join(Parameters!Multivals.value,",") to convert the multiple valued parameter to a string like this: a,b,c,d,e ....... 2. In the database, write a function to convert a string with special delimiter into a table, you can reference the following T-SQL code: CREATE FUNCTION [dbo].[fn_String_To_Table] ( @String VARCHAR(max), /* input string */ @Delimeter char(1), /* delimiter */ @TrimSpace bit ) /* kill whitespace? */ RETURNS @Table TABLE ( [Val] VARCHAR(4000) ) AS BEGIN DECLARE @Val VARCHAR(4000) WHILE LEN(@String) > 0 BEGIN SET @Val = LEFT(@String, ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1), LEN(@String))) SET @String = SUBSTRING(@String, ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0), LEN(@String)) + 1, LEN(@String)) IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val)) INSERT INTO @Table ( [Val] ) VALUES ( @Val ) END RETURN END 3. Then, inner join the tables based on the like oprators to get the result you want like this: (assume that the Tbl2 is created by the table function) select * from Tbl1 inner join Tbl2 on Tbl1.val like '%' + Tbl2.val2 + '%' I believe you can write the stored procedure or query string to arrive your purpose based on above my 3 clues. thanks, Jerry
October 14th, 2010 8:31am
Hi Did your problem fixed. I am currently facing the exact proble you had.
October 15th, 2010 1:33am
Hi Jerry, Thanks for your reply and also for good explanation. Every thing what you did in sql n tables all are fine. But i did not understand how to get that string (a,b,c,...) into my proc are function wat ever it is. My user will select some values from report parameter, when i try to pass that parameter in to stored proc r function it was like Exec Procname 'a','b' or Exec Procname a,b,c some thing like this so, it was throwing error. Main thing i want to know is, how to capture and pass that report parameter value(a,b,c) in to the my proc or function which are in my main Dataset. If you can help me in solving this, it would be grt help. Thanks once again.
October 15th, 2010 2:45am
Hey Jerry, Thank you so much. I got the solution.
October 15th, 2010 9:25pm
Hey Jerry, Thank you so much. I got the solution. Could you pls. share with us how did you do that ? I'm having same exact problem and i guess Venkat too. Venkat did you resolve your issue if yes could you pls. let me know how? Thank you.SSRSRpt
November 9th, 2010 10:06am
Yeah, I'd like to see the solution as well. I have the exact same problem but I'm having a hard time tying together all of Jerry's recommendations. I would like to see how he uses the function in the sql statement.
November 12th, 2010 5:34pm
Hey Jerry, Thank you so much. I got the solution. Hi DJ3001, Could you pls. share the solution with us. we are having exact same issue. Thank you.SSRSRpt
December 16th, 2010 1:55pm