Dynamic Date ranges in query panel, conditions

[ordendelfai]

BO 5.1.7

I have some reports that I use to do in MS Access with dynamic date ranges that I would like to run using BO.

So far, I cannot find a way to use dynamic date ranges in the query panel unless I use a prompt. However, I need to use the Broadcast Agent (this is a jumbo sized company), so I cannot use prompts.

For starters, is there an equivalent way in the conditions section of a query in BO to enter the below criteria?

Cancel_Eff_date = Date()

This criteria would be used to run a daily report, through the Broadcast Agent. The "Cancel_Eff_Date" Field therefore needs to always be "Todays Date". I cannot create a User Object to do this because it is going to go through the broadcast agent, and my IS department is not willing to create a "Today Date" object for me to use.

Any help is really appreciated.



~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[skrandel]

You can't do it directly in the query panel, but you should be able to build objects in your universe that handle the dynamic dates you want.

Another, more useful option, is to build a calendar table that is updated as part of your ETL processes that holds the dates or ranges that you need.



Steve Krandel
VERITAS Software

 

[ordendelfai]

Hi Steve, thanks for the input. I ended up having to update the Oracle SQL directly to make dynamic date ranges. I basically used the Trunc function with "sysdate" to make it work.

I do not handle the universes at all, so if you do not mind, could you elaborate on what a Calendar table would contain, and how it would be helpful when having to make dynamic date ranges? I will forward your suggestion to my IS department and see what they think.

Thanks ;-)

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.