Problem with schedule report based on last interval

[ordotan]

Hi,

I'm trying to schedule a report to run every 5 minutes for the last 1 hour.

I have the following filter in the report, and the parameter I set for the schedule is "Last Interval=1". (run for the last 1 hour...)

   case
   when (?Last Interval?=1)
   then ([Start Time for filter]>{sysdate-1/24})
   when (?Last Interval?=2)
   then ([Start Time for filter]>{sysdate-2/24})
   else ([Start Time for filter]>{sysdate-3/24})
   end
   and
   [Start Time for filter]<{sysdate}

The schedule generates the first output OK, but then all the following outputs are generated for the initial hour interval, and it seems that "sysdate" isn't getting updated with the current time.

What am I doing wrong?

[CognosPaul]

Is it possible the query is caching the results? Try setting the query property "Use Local Cache" to no.

[ordotan]

Hey Paul, whats up ?  :)

Actually we are using it with "Local Cache=Yes", because the report has 10 charts, all based on the same query, and we want to use only 1 sql in the DB to populate all of them. We can only achieve it by using this attribute.

Is there a way to tell the report to clear its cache right after it was executed ? I saw in the properties of the report, in the "Advanced Option" a way to define it I think, but it is only in a resolution of Days/Week, and I need it in minutes resolution..(see in the attached screen capture)

[CognosPaul]

It might be recognizing that the SQL is identical, so it's not attempting a refresh. How about using a macro to set the date?

Code Select Expand

case
when (?Last Interval?=1)
   then ([Start Time for filter]>to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd hh:mm:ss'))#,'yyyy-mm-dd hh:mm:ss')-1/24)
when (?Last Interval?=2)
   then ([Start Time for filter]>to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd hh:mm:ss'))#,'yyyy-mm-dd hh:mm:ss')-2/24)
else ([Start Time for filter]>to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd hh:mm:ss'))#,'yyyy-mm-dd hh:mm:ss')-3/24)
end
   and
   [Start Time for filter]<to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd hh:mm:ss'))#,'yyyy-mm-dd hh:mm:ss')

You might need to play with the syntax a bit, I don't have Oracle available to me at the moment, so I'm not sure if using -1/24 will work. If not, you might need to do something like:

Code Select Expand

to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd hh:mm:ss'))#,'yyyy-mm-dd hh:mm:ss') - interval '1' hour
or maybe even:

Code Select Expand

{to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd hh:mm:ss'))#,'yyyy-mm-dd hh:mm:ss') - interval '1' hour}

[ordotan]

Hi,

When implementing the filter you suggested, I'm getting multiple queries for the report, becasue each query is being run of a different time, and the local cache can't be used.

For example -

Query no.1 :
...
AND "AGG_GI_5m" . "START_TIME" > to_date( '2014-08-07 11:02:51' , 'yyyy-mm-dd hh:mm:ss')
...

Query no.2 :
...
AND "AGG_GI_5m" . "START_TIME" < to_date('2014-08-07 11:02:52' , 'yyyy-mm-dd hh:mm:ss')
...
If only I could tell the report to dump its cache upon completion, it will solve this issue

[CognosPaul]

This is a single query right? Try creating a derived query from that and associating all of your objects to that query.

Try also setting RequestHintLocalCacheHasHigherPriority:
http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html

[ordotan]

Yes, it is a single query report, and I created a reference of it, and all charts are based on this reference.

The goal is to get single query, and it works OK, till the point we tried to run in with schedule. At this point, the use of local cache, screws things up :-\

I will try the "RequestHintLocalCacheHasHigherPriority" setting..

[ordotan]

Small update - my env was already configured with this setting (probably from previous tries...). So it didn't help

[CognosPaul]

How about using a job to schedule the report and refresh the cache?

disable the schedule on the report. Create a new job with two steps. Step 1, refresh the report cache, step 2 run the report. Set it to run sequentially.

Now this technically refreshes the prompt cache, so I'm not sure if this will force a refresh to the report cache.