I have 4 parameters in the report e.g. id,emp_name,emp_dept,emp_loc with a textbox for each of them.. now the report should be generated using the search string created by values entered into these four textboxes using wild cards.. e.g. if user enters emp_id as 1 then select * from employee where id like (%1%) if user enters emp_dept as 'sales' then select * from employee where emp_dept like (%'sales'%) now user can enter emp_name and emp_dept or any other combination or also all the four attributes..so search string needs to be configured accordingly..can somebody please suggest me how to do this..pls really need ur help guys.. :(

Hi, The easiset would be to use Stored Procedure with four paramters and set default to NULL and then procedure you can use dynamic query generation based on the parameter values. Kindly have a look at this wonderful link to see how to do this http://www.sommarskog.se/dyn-search.html- Chintak (My Blog)

Thank you so much Chintak Unfortunately, I am not allowed to use EXECUTE or SP_EXECUTESQL statements on the db. So, is there any other way?

why cant you specify all the four parameters in where clause using OR and set the default of these parameters to some value such as '#*$#@' so that it will not return unwanted rows

Hi, You can achieve it below is the sample format query which give you an idea on the implementation. DECLARE @Tab TABLE (EmpId NVARCHAR(20), EmpName NVARCHAR(20), EmpDept NVARCHAR(20), EmpLoc NVARCHAR(20)) INSERT INTO @Tab SELECT '1','ABC','Dev Manager','Blore India' UNION SELECT '12','BCD','Project Manager','Hyd India' UNION SELECT '23','CDE','Software Engg','Hyd India' UNION SELECT '34','DEF','Accounts','Delhi India' UNION SELECT '45','EFG','Accounts Engg','Mumbai India' UNION SELECT '56','FGH','Manager','Blore India' UNION SELECT '67','GHI','Software','Mumbai India' UNION SELECT '78','HIJ','Sales Manager','Delhi India' UNION SELECT '89','IJK','Sales','Hyd India' DECLARE @Para1 NVARCHAR(20) DECLARE @Para2 NVARCHAR(20) DECLARE @Para3 NVARCHAR(20) DECLARE @Para4 NVARCHAR(20) SET @Para1 = '2' SET @Para2 = 'B' SET @Para3 = 'Sales' SET @Para4 = 'Hyd' SELECT @Para1, @Para2, @Para3, @Para4 SELECT * FROM @Tab WHERE CAST(EmpId AS NVARCHAR) LIKE '%' + @Para1 + '%' OR EmpName LIKE '%' + @Para2 + '%' OR EmpDept LIKE '%' + @Para3 + '%' OR EmpLoc LIKE '%' + @Para4 + '%' --- Depending on your requirement you can go with AND/OR operator Hope its clear & helpful.... Pavan Kokkula Tata Consultancy Services.

hi pavan one question what will happen if you don't specify say @Para4 i.e. @Para4 = '' then the query will be SELECT * FROM @Tab WHERE CAST(EmpId AS NVARCHAR) LIKE '%' + @Para1 + '%' OR EmpName LIKE '%' + @Para2 + '%' OR EmpDept LIKE '%' + @Para3 + '%' OR EmpLoc LIKE '%%' it will return all the rows that's why we have to set a default value to some impossible value like i said '#*$#@' so the query becomes SELECT * FROM @Tab WHERE CAST(EmpId AS NVARCHAR) LIKE '%' + @Para1 + '%' OR EmpName LIKE '%' + @Para2 + '%' OR EmpDept LIKE '%' + @Para3 + '%' OR EmpLoc LIKE '%#*$#@%' and will work on only @Para1,@Para2 or @Para3 excluding the @Para4 -------------------------------------------------------- Surender Singh Bhadauria

Try query like this SELECT * FROM @Tab WHERE ( ( CAST(EmpId AS NVARCHAR) LIKE '%' + @Para1 + '%' or @Para1 is null ) and ( EmpName LIKE '%' + @Para2 + '%' or @Para2 is null ) and ( EmpDept LIKE '%' + @Para3 + '%' or @Para3 is null ) and ( EmpLoc LIKE '%' + @Para4 + '%' or @Para4 is null ) ) Spandan B

Hi Surender, A little change in the where caluse to get it wotk in all the scenario's SELECT * FROM @Tab WHERE CAST(EmpId AS NVARCHAR) LIKE '%' + CASE WHEN ISNULL(@Para1,'') <> '' THEN @Para1 ELSE NULL END + '%' OR EmpName LIKE '%' + CASE WHEN ISNULL(@Para2,'') <> '' THEN @Para2 ELSE NULL END + '%' OR EmpDept LIKE '%' + CASE WHEN ISNULL(@Para3,'') <> '' THEN @Para3 ELSE NULL END + '%' OR EmpLoc LIKE '%' + CASE WHEN ISNULL(@Para4,'') <> '' THEN @Para4 ELSE NULL END + '%' Hope its helpful.... Pavan Kokkula Tata Consultancy Services.

Hi rockstar283, Thanks for your question and other partners’ replies. Based on the description through the thread, I understand that you would like to filter the results by the values that the user had typed in the four parameters, the effect just like “ColumnName like ‘*abc*’, in addition, you have no permission to use EXECUTE or SP_EXECUTESQL statements on the database, right? Actually, in my opinion, the easiest way of achieving this requirement is adding filters in the Dataset level rather than in T-SQL query level, meanwhile, in order to ensure the filter have no effect when the corresponding parameter’s value is NULL, you should add a judgement for each of the filters. Supposing that the four parameters id, emp_name, emp_dept, emp_loc corresponding to the fields in Dataset are id, emp_name, emp_dept, emp_loc, and their Data types are all Text. 1. Double-click the Dataset in the Report Data window, open the Dataset Properties dialog box. 2. Click Filters in the left pane, click Add button, select id in the Expression drop-down list, select Like in the Operator drop-down list, click fx button on the right of the Value textbox. 3. In the Expression dialog box, type in like this: =Iif(Isnothing(Parameters!id.Value),Fields!id.Value,"*" & Parameters!id.Value & "*"). 4. Repeat the previous two steps for the other three parameters and corresponding fields. Hope it helps you. If your issue still exists, please feel free to let me know. Thanks, Sharp Wang Please remember to mark the replies as answers if they help you and unmark them if they provide no help.