I’ve got a report with prompts in it. How does BCA know what to do?
The most common solution to this, is the use of pre-defined conditions. Imagine a report which uses a prompt like this:
Date between <start date> and <end date>
When you run the report on your PC, you fill in the start date as the first day of the current month and the end date as today. The logic you use to decide what dates to type is simple in this case and can be replicated in a pre-defined condition in the universe. Create a new pre-defined condition in the universe, we might call it ‘Month to Date’. In oracle, for example, the Where clause might look like:
Table.Date_column between trunc(sysdate,'mm') and trunc(sysdate)
You then replace your prompt condition with this pre-defined condition and sent the report to BCA. The next time BCA runs the report, it will evaluate the sql in your pre-defined condition to decide what information should be retrieved. You can sit back and bask in the glory!
Thanks to HenkK for a nice variation for the more demanding user:
When we have a report with prompt for start date which is used by users as well as BCA we create double prompts like
<object> = Decode(@prompt("startdate or 0 for sysdate"), 0, Trunc(sysdate), @prompt("startdate or 0 for sysdate"))
Users just fill in the date they want as appropriate when running directly on their own PC. If the report is to be sent to BCA you fill in 0 and the prompt will be automatically completed.
Steve Krandel has another take on this problem, which you can read about here. Thanks Steve.
Nick Daniels (BOB member since 2002-08-15)