I have oracle as my datasource.. I wan tto know if there`s a way i could create a dynamic query select name,date,city,state,productid from customer where name or dateor city or state or productid=:whereclause . What i am trying to do is have a report and use a where clause whcih will let me select paramter at run time time..FM

you can turn your query into an expression which will allow you have a query thats dynamic trivial example: ="select 1 from dual where " & iif(Parameters!parm.Value < 5, "1=1", "1=2") however I try to avoid using expression based queries as much as possible. If you give more details there may be a way around using a dynamic query.

This report is going to be deployed on our company portal and we are gonna use a webservices.. The way this report would be used it a use could go onto the UI and select these params LIke they could search for records based on "workorder" "Date" "Customer name" or Zip or State. I have a view that im using see below. select * from vw_inspection Functionality wise i wwant this view to have conditional paramter.. If i am getting it right?FM

This i smy query SELECT RACKNUMBER,WORKORDERNUMBER,OWNERDESCRIPTION,MATERIALCOLORBAND,MATERIALTYPE,ENDUSERDESCRIPTION,QTY,LENGTH,DESCRIPTION1,WOLINENUMBER,DESCRIPTION2,DESCRIPTION3,LOCATIONID,MATERIALDESCRIPTION,USEMATERIALDESCR,INVENTORYSTATUS,OWNER_COMPANYID,ENDUSER_COMPANYID,SOWO,TRACKINGNUMBER,AUTHNUMBER,SERVICECODE FROM VW_RPT_INV_STANDARD_FORMAT WHERE ( locationid=:locationid or WORKORDERNUMBER=:WORKORDER.... and so on) <=== WHERE Whereclause=@Parameter @parameter i should be able to select any of the columns i used in the select stmt from my view . FM

As a quick test, I got this to work, using SQL Server on the back end... So you'll need to make the necessary syntax adjustments for Oracle... DECLARE @Column INT, @Value VarChar(255) SET @Column = 3 SET @Value = 'Burnett' SELECT * FROM Person.Contact WHERE CASE @Column WHEN 1 THEN FirstName WHEN 2 THEN MiddleName WHEN 3 THEN LastName END = @Value Jason Long

you dont need a dynamic query for conditional parameter, just dont use the parameter if its not used. You can have the parameter allow null or blank and ignore it in your query if its left that way: select 1 from table1 where (workorder = :workorder or :workorder is null) and (table1.date = :date or :date is null).....

You should be able to do this with a CASE expression. I don't use Oracle, so please forgive any syntax errors... WHERE CASE @ColumnNameParameter WHEN 'Column 1' THEN Table.Column1 WHEN 'Column 2' THEN Table.Column2 WHEN 'Column 3' THEN Table.Column3 WHEN 'Column 4' THEN Table.Column4 WHEN 'Column 5' THEN Table.Column5 END = @ValueParameter Jason Long

Hi Farhan1, Thanks for your question and other partners’ replies. In order to reach to this target, I suggest you creating one stored procedure with one input parameter. This parameter is where clause, you can get it from the parameter in report. The body of this stored procedure is one dynamic T-SQL query based on the original T-SQL query and the input parameter. Notice that give one default value for the input parameter, for example: where 1=1. Please refer to: how to execute oracle procedure with command type text in SSRS. Hope it helps you. If there is anything unclear, please let me know. Thanks, Sharp Please remember to mark the replies as answers if they help you and unmark them if they provide no help.