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.

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.

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?

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/

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

Hi Did your problem fixed. I am currently facing the exact proble you had.

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.

Hey Jerry, Thank you so much. I got the solution.

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

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.

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