Hi All,
In my report, I have Date propmts(From Date & To Date) and Exception Code prompt. By default I want to display current date's data and for all Exception codes(so its a optional prompt).
I am using SQL Query to fetch the data from the table and I must use all the filters in the SQL Query itself. So I am using macro in the SQL query.
Select Exception_Code, Job_Name,Table_Name,Survey_Dt
where Survey_DT between #prompt('param_From_Dt','token','current date')# and #prompt('param_To_Dt','token','current date')#
and Exception_Code = #prompt('param_Exception_Code','string','B201')#.
In order to make this optional I have passed a default value as B201. However, when the report should run for the first time, it should display all the data for the current date for all Exception codes.
FYI--Database is DB2.
How to incorporate this in SQL query?
Writing from my phone so please forgive my brevity.
Make the entire and exception part of the macro, specifically in the fourth parameter.
Select Exception_Code, Job_Name,Table_Name,Survey_Dtwhere Survey_DT between #prompt('param_From_Dt','token','current date')# and #prompt('param_To_Dt','token','current date')# #prompt('param_Exception_Code','string',' ',' and exception_code=')#
If the parameter is empty then it will return a space to the sql. If not it will put the filter in.
It works :)..I tried this..Thanks Paul
Thanks Paul..
It's working fine
Thanks very much