In a message dated 98-09-15 08:15:13 EDT, you write:
I am have a BOI report that takes 2-3 hours to run and needs to be run
daily. Currently the report runs off of a prompt for the Report Date.
I’d like to be able to send it to the document agent every night to make weekly reporting easier, but I’ve been unable to find a way to alter this prompt’s value from within my script! I’d consider making the date a hard-coded part of the query, except you have to actually run the report to accept those changes (at which point, there’s no need to send the file to the document agent.)
From the above description, I assume that you have condition something like this :
report_date = @Prompt(‘Enter Report Date’,‘D’,)
I aasume that you are using ORACLE at the RDBMS. My solution is :
- Create an object called “Report Date” in the universe. 2. The definition of this objcet is SYSDATE. 3. Now replace your condition in the report with :
report_date = Report Date
( You have to use the Select an object option as the choice
of selecting the operand in the Query Panel ).
VOILA!!! Your report is automated now. Submit it to the DAS to be run everyday.
I hope this will help you,
Chandrashekar.S
I would like to add a few thoughts that might be useful in this area! If you have a report range that is strictly for one day, then this should work fine. I would suggest that you use the formula TRUNC(SYSDATE) in order to get a basic “day” value instead of a date and time, however.
If you need a weekly range, meaning that the report is run daily but for a range of days from Sunday - Saturday, then you can be a little bit more tricky. I would build the following logic into a predefined condition (filter) object called Weekly Report:
Assume the condition is based on table.dateval
table.dateval between
trunc(next_day(sysdate-7,‘SUNDAY’)) and
trunc(next_day(decode(to_char(sysdate,‘DAY’),‘SATURDAY’,sysdate-1, sysdate),‘SATURDAY’))
The next_day function takes a date and returns the date matching the “next day” with the appropriate name. For example, next_day(sysdate,‘FRIDAY’) when executed today (Sept 15, 1998) will return Sept 19, 1998. This weekly range works for a report that is to be run for the week Sunday through Saturday, change the day names to suit your own needs if required. The reason for the extra trickery with the decode() function for Saturday is that if the report is run on Saturday, then the “next” Saturday is actually a week away, giving you a two week range. If the report is run on Sunday, then the sysdate-7 results in Saturday, and the “next” Sunday is still correct.
For a monthly range, there are some more useful Oracle functions: add_months() and last_day(). The last_day() function returns the last date of the month (which is different from month to month, of course) while the add_months calculates a month offset from a date. For a standard monthly report range use:
table.dateval between
trunc(last_day(add_months(sysdate,-1))+1) and trunc(last_day(sysdate))
The trick here is to find the last day of the previous month and then add one, thereby arriving at the first day of this month. The second end of the range is simply the last day of the month. There are other ways to obtain the first day of the month, including:
to_date(‘01-’ || to_char(sysdate,‘MON-YY’),‘DD-MON-YY’)
… so this is not the only answer.
All of this code works fine in any version of Oracle. Other databases do not have the same functions, so other tricks would be required.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)