Scripts and Prompts

Hello, all!

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.)

Does anyone have any suggestions??

Thanks in advance

Lisa Main


Listserv Archives (BOB member since 2002-06-25)

Hello Lisa,

Main, Lisa wrote:

Hello, all!

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 :

  1. 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


Listserv Archives (BOB member since 2002-06-25)

Hi, Lisa.

You may want to consider creating an object which uses the database’s date functions. For example, for an Oracle database, create an object whose SQL would be
SELECT SYSDATE
Or, if you need an object to calculate the date from a week ago, you can use a SQL equal to
SELECT SYSDATE - 7

Don’t bother associating a table to these objects. SYSDATE works dynamically with any table in your query.

So for the report you send to Document Agent, set your Report Date Object = Sysdate Object
and you’ll get the daily results you need.

Cheers,
Luis Gonzalez

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.)

Does anyone have any suggestions??


Listserv Archives (BOB member since 2002-06-25)

Hi Lisa,

It’s probably not exactly what your looking for, but we solved this date driven report by using a database table containing the necessary dates. This table could be put in the universe or alternatively accessed via a stored function.
In our case it is updated by batchprograms running and keeping track of up to where data is present in the database, but from your script you could log on to the database and update the date value in the table before refreshing the report.

Hope this helps somewhat,
Marianne Wagt
IDETA
The Netherlands


Listserv Archives (BOB member since 2002-06-25)