I have weekly and monthly reports to be run regularly on DAS. The weekly report will be scheduled to run Saturday of every week and the data for the report should be where the accepted date is in the current week. Same with the monthly report, scheduled to run on the last day of the month with accepted date in the current month.
Can any one suggest me how I can run these reports without changing the condition for the Accepted Date and get it run for the current week and current month and leave it automatically scheduled on DAS.
If you are using ORACLE it is very simple. You can use the SYSDATE
function to get the date and month. In your case the condition will be
between sysdate -6 and sysdate
similar thing will work for Month also.
Good Luck
Let’s say Sunday thru’ Saturday is the week. You will schedule your reports
on Saturday night.
For handling Weekly reports, I would create two Objects in my universe:
ThisWeekStartDate --> current date - ((days(current date) - (
days(current date)/7) * 7) days)
ThisWeekEndDate --> current date + 6 days - ((days(current date) -
( days(current date)/7) * 7) days)
Don’t attach any table to object definition. It won’t parse, but it will run
fine.
Where ‘Current Date’ is a DB2 function to get today’s date. Use SYSDATE in
oracle.
In the report pull ‘Accepted Date’ to the condition panel, select ‘Between’
operator, ‘select object’ as operand1 and select ‘ThisWeekStartDate’ for
operand 2 select ‘ThisWeekEndDate’.
Now this report, whenever you refresh, it will run for current week. Same
idea you can extend for monthly reports also.
Object defintion formula I have specified has a bug. For this week it would
generate like follows:
ThisWeekStartDate = Sunday 07 Feb 1999 12:00:00 AM
ThisWeekEndDate = Saturday 13 Feb 1999 12:00:00 AM
Look at ThisWeekEndDate, it should be ‘Saturday 13 Feb 1999 11:59:59 PM’
This method works fine if you want to run the report automatically always.
Instead you can also design your report to accept user inputs, where user
can enter the dates when he refreshes. When you schedule this thru’ DAS you
can populated the prompts thru’ scripts.