Scheduling Report & Run For Dynamically Calculated date/time

[ankan_priya]

Hi All,

I have a report which I want to schedule to run every day.

As per our product architecture, we don't directly connect to the DB but we have a middle JDBC layer that the cognos connects to & that JDBC layer connects to the DB and returns the data as per the query. Each query has to be compulsarily filtered at JDBC using 4 parameters among which we have START_TIME & END_TIME.

For the normal report running, we take a date-time input from the user on the prompt page, manipulate it using javascript to calculate new date-time as per the data server's timezone(this is important for us) and accordingly use them in the query filters.

However, when the report is to be scheduled, we don't take the date input from the user as we have to fetch data for current date/time(with timezone offset calculation). The issue is:

"We are not able to manipulate the date/time as per the timezone because the javascript doesn't work in scheduled report. We tried to use the _add_days() function in the query but our JDBC doesn't support those date/time functions. If we keep aside the timezone thing for once, our JDBC has a default behaviour for returning data for current date if no START_TIME & END_TIME filters are mentioned in the query, but then we get stuck with the timezone thing and remain deprived of doing timezone calculation and get correct data."

Due to some limitations, we cannot update the JDBC as of now. Is there any other way to achieve the report scheduling with timezone calculated current date/time.

Thanks a lot.

[MFGF]

We tried to use the _add_days() function in the query but our JDBC doesn't support those date/time functions.

_add_days() is an inbuilt Cognos function, so passing it in your query to the database (via jdbc) isn't going to work. You haven't mentioned what database you are using - I'm guessing it's possible to figure out if your db has a similar inbuilt funtion to _add_days() and try passing this.

Good luck!

MF.

[ankan_priya]

Hi MFGF,

Thanks for your reply.

We are using Oracle DB. The issue is that we are not connecting to the database, we are connecting to the JDBC layer which does not have any such functionality to handle date/time functions, so cognos fails to run those functions in the DB(here JDBC) environment and end up running those functions in its own(cognos) environment on whatever data is returned from JDBC(as a defualt behaviour of running query for current date).