Hi,
I need to tune a impromptu report.
That report has two date prompts
1) Start Date
2) End Date
The report fetches all the reports falls between these dates. In the database, date column has date and time like '12-Aug-2003 12:10:00'.
so with the help of cognos add-days function, the report achieves this with the following filter condition.
Date >= ?startdate? and Date < add-days(?enddate?,1).
so it is restricted to local processing/limited local processing.
I improved the performance of this query by altering the filter condition as
Date >= ?startdate? and Date <= ?enddate? and make the processing to database only.
Now in the final SQL, filter used by impromptu is
Date >= to_date('1998-03-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and
Date <= to_date('2003-08-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
obviously it doesn't return 2003-08-15 00:15:00. But I want to have all the rows having just date part as 2003-08-15.
I don't want to go for free-hand SQL. As it access manager is ignoring reports which is having free hand SQL ( Is anyone faced this problem access manager ignoring free hand SQL reports).
Thanks in advance
Kejal
I need to tune a impromptu report.
That report has two date prompts
1) Start Date
2) End Date
The report fetches all the reports falls between these dates. In the database, date column has date and time like '12-Aug-2003 12:10:00'.
so with the help of cognos add-days function, the report achieves this with the following filter condition.
Date >= ?startdate? and Date < add-days(?enddate?,1).
so it is restricted to local processing/limited local processing.
I improved the performance of this query by altering the filter condition as
Date >= ?startdate? and Date <= ?enddate? and make the processing to database only.
Now in the final SQL, filter used by impromptu is
Date >= to_date('1998-03-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and
Date <= to_date('2003-08-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
obviously it doesn't return 2003-08-15 00:15:00. But I want to have all the rows having just date part as 2003-08-15.
I don't want to go for free-hand SQL. As it access manager is ignoring reports which is having free hand SQL ( Is anyone faced this problem access manager ignoring free hand SQL reports).
Thanks in advance
Kejal