How to pass null values in macro in SQL Query in Report Studio

[laxmanrao83]

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?

[CognosPaul]

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.

[ravikrsingh]

It works  :)..I tried this..Thanks Paul

[laxmanrao83]

Thanks Paul..

It's working fine

Thanks very much