Aug 22 2007
Dynamic Dates Part I: Yesterday and Today
Hitting a Moving Target
Does anybody really know what time it is?
Does anybody really care?
If so I can’t imagine why…
With apologies to the rock group Chicago, but I could not help starting out this post with a quote from one of their most famous hit records. Yeah, I have records. Deal with it. 
I am going to start out this post with a very obvious statement. There are two ways to get reports: Interactive and Scheduled. Interactive reports can have prompts that allow me to specify which values I want to see, and many times those prompts will include dates or date ranges. Since they are interactive I can change the dates each time I run the report.
But what about scheduled reports? I would hardly want to schedule a report that always ran for August 22, 2007, right? I want that date parameter value to change. Maybe I want the report to always run for “yesterday” or “last week” or “the second Tuesday of the month” or anything along those lines. The challenge is, of course, that the value for “yesterday” changes each day I run the report. How do I hit that moving target?
It’s All About Today
The first component that I need to solve this issue is knowing what “today” means for any given database. Every system that I have worked with has provided at least one mechanism to get today’s date:
| Database | Function or Token |
|---|---|
| DB2 | current date |
| Oracle | sysdate |
| Informix | TODAY |
| MySQL | CURDATE() |
| SQL Server | getdate() |
| Sybase | getdate() |
| Teradata | DATE |
Some databases use a function and others provide a token or pseudo-column instead. I am going to use Oracle syntax for the rest of this post but the general concepts and techniques that I plan to show are often able to be ported to other database systems.
Using sysdate
As mentioned I can get today’s date from Oracle using the pseudo-column sysdate. (It’s called a pseudo-column because you can get it from any table in the database.) Once I know what today is I can derive other interesting values. The first step is often to get only the date (instead of a full date/time result) and the Oracle function trunc() takes care of that. So today can be obtained by:
trunc(sysdate)
The trunc() function in Oracle is an overloaded function which means I can use it with a variety of different syntaxes. (It may be that I could create a more portable version of the same objects using the cast() function.) Now that I have today, what can I do with it? I mentioned earlier in this post that one of the common requests is to be able to run a report for “yesterday” on a scheduled basis. Yesterday is the day before today on most calendars. 
So I can create an object for “yesterday” using either of the following:
trunc(sysdate-1)
trunc(sysdate)-1
Both of these will return the exact same result, so it doesn’t matter which one I pick. I am going to use the first one for reasons that will be detailed in a future blog post.
Conditions in a Scheduled Document
Once these objects are created I have a couple of options to consider. First, I can make predefined conditions that are based on a specific date object like Order Date or Shipped Date. I will often do this for frequently used conditions. But an important option that should not be overlooked is creating a simple object called Yesterday with the code posted above. After this object is created then I can create any date condition, using any date object in the universe, by using the “Select an Object” query operand. This option is available in full client from version 5.x forward (and possibly in version 4.x too, I don’t remember). It is also available in Web Intelligence XI and might be in 6.5.
I will create Today and Yesterday objects using Oracle as my target database. Here are the objects:
And here is the result I get when I parse either of these two objects:
This is typical! Most if not all of my objects built for this strategy will fail to parse. One of my current universes has several hundred objects that fail to parse during a universe integrity check, and that is just fine. I don’t want to reference any table with these objects (not even the special Oracle “dual” table) because then I would be limited as to how I can use them. If I use the dual table and don’t include any joins then I would get Cartesian warnings. Objects like this are perfectly safe to use as long as they participate in a query with at least one object that does use a real table. And since these objects would never be used by themselves that is okay.
Now that I have my objects built, here’s how I will use them. I am going to build a condition that will return all orders entered yesterday by using the following steps.
- Create a condition on Order Date Equal to and select Object from the condition operand options.
- Browse the universe to my Dynamic Dates for Scheduling class and select Yesterday.
- Confirm the selection and review the condition.
That generates a condition that looks just perfect for my purposes. The order date will be compared to a dynamic “yesterday” object. Since that object is based on the system date then that object will change in value on each new daily execution of the scheduled report. If order dates are date/time values then to get the full range I can use a between operator instead. For this I need both of my dynamic date objects, as shown next. This condition will return everything from midnight yesterday to midnight today.
What does the SQL code look like?
SELECT
CUSTOMER.CUSTID,
...
sum(ORD.TOTAL)
FROM
CUSTOMER,
ORD
WHERE
( CUSTOMER.CUSTID=ORD.CUSTID )
AND
ORD.ORDERDATE BETWEEN trunc(sysdate-1) AND trunc(sysdate)
GROUP BY
CUSTOMER.CUSTID
The condition added to the where clause by this condition is bold to try to make it stand out more. I can see that there is no specific date in the query, therefore as the report instance is executed on its daily scheduled basis I will get what I expect: a daily report with orders from yesterday.
Predefined Conditions
The biggest advantage to creating the general date objects is that they can be used to compare against any other date object in the universe. Of course I can also consider creating predefined condition objects in my universe. These objects are often built for conditions that are too complex for users to create, or there is a need for consistency across all reports, or for convenience. But in this case the number of predefined condition objects could become a challenge.
I would have to create a cross product of date objects, dynamic date elements, and desired date operators. That’s a lot of predefined conditions in my universe! 
So far I have two dates (order date and ship date) and two dynamic elements (today and yesterday). I could have:
- Order Date Equal To Today
- Order Date Equal To Yesterday
- Order Date Between Yesterday and Today
- Order Date … ?
As you can see it could get out of hand. And all of the conditions above can be created by the user with the proper query techniques if I just build the dynamic objects. In theory you could build a huge set of predefined conditions. In practice I generally will build one or two that are used frequently and let the user do the rest. So I will create objects for orders placed yesterday and for orders shipped yesterday and leave it at that until I get requests for something different.
Summary
This is one of the standard elements of any universe that I design. This post is designed to introduce the concepts. The next post in this series will cover some more Oracle-specific date functions and how they can be used to do very creative things with dynamic date objects for scheduling.
Oracle functions used in this post
- trunc() As used in this article it returns a date value with the time truncated off
Just a note about DB2. I had a problem doing this with CURRENT_DATE and the querys were running for a long time. I had to ‘wrap’ my statement with the COALESCE in order for the database to not have to do the calculation for each row (I think that’s what my DBA said).
I have done many of these dynamic date objects. I’ve created begin and end date objects for previous and current week, month, fiscal year, calendar yesr, etc. and then used these (using ‘between’) for many conditions.
Heres a couple of examples.
Prev Month Begin Date:
COALESCE(DATE(RTRIM(CHAR(Month(CURRENT_DATE - 1 month))) ||'/01/' || CHAR(YEAR(CURRENT_DATE - 1 month))),CURRENT_DATE)Prev Week End Date:
COALESCE((CASE DAYOFWEEK(CURRENT_DATE)
WHEN 1 THEN
CURRENT_DATE - 1 DAYS
WHEN 2 THEN
CURRENT_DATE - 2 DAYS
WHEN 3 THEN
CURRENT_DATE - 3 DAYS
WHEN 4 THEN
CURRENT_DATE - 4 DAYS
WHEN 5 THEN
CURRENT_DATE - 5 DAYS
WHEN 6 THEN
CURRENT_DATE - 6 DAYS
WHEN 7 THEN
CURRENT_DATE - 7 DAYS
END),CURRENT_DATE)
Prior Fiscal Year Begin Date (our FY is July-June):
COALESCE( (CASE WHEN MONTH(CURRENT_DATE)
Last example cut off. Here is the full exapmle for Proir Fiscal Yeatr Begin Date (FY is July-June)
COALESCE( (CASE WHEN MONTH(CURRENT_DATE)
Hi, DOTJake, and thanks for sharing. I have finally figured out that when you post code that includes < and > you need to wrap the comment text in “code” formatting. That will keep your characters from being interpreted as web links. I have edited your first example, so you can see the code is in courier (fixed) font which also seems to me to make it easier to read.
If you can try submitting your example again, but put <code> in front of the code, and </code> at the end, I think it will come out.
Something like this:
<code>If x < y then 1 else 0</code>Nice, Dave. Now you need to put a pointer from the Reporter and Designer FAQs over to this.
You’ve accidentally left Sybase out of your table — it uses getdate() also.
I didn’t “accidentally” leave it out, I left it out on purpose.
Many years ago I worked with a client that was using Sybase IQ. It did not have any internal functions, not even getdate(). That was the one and only time I ever used @Script() in a universe, and it was to get the current date. Since then I have not worked with Sybase of any flavor at a client site in probably eight or nine years, and I don’t see it gaining any market share over the next few years either. But point taken, and I will add it in to the table. Thanks, and welcome to my blog.
Now that I know how to post code, here is the Proir Fiscal Year Begin Date (FY is July-June) example:
COALESCE( (CASE WHEN MONTH(CURRENT_DATE)
Dave,
It seems to be cutting off after the ‘greater than or equal to’ symbol
Thanks Dave for your lesson.I learnt a new concept now.
Let me extend a little bit on this
What if the user wants this to be dyamanic like nstead of yesterday it may be 30 days or 20 days back
There is no fixed value he may enter any value…
Testing comment…
Case when something >= something elseHm. Now I don’t know what to tell you. Here is the code for what I just posted:
kumar wrote:
Kumar, if you look, you have answered your own question.
Thing about the part I have emphasized… how would you do that? With a prompt, correct?
So instead of an object called Yesterday I will create an object called Prompt Days Ago that looks like this:
trunc(sysdate - @prompt('Enter days ago','N',,,))This will prompt the user for the number of days. If the user enters 0, they get today. If they enter 1, they get yesterday. If they enter 7, they get a week ago. And so on from there.
Hey Dave!
You have been fabulous again…i mean as usual. I have been learning lots of tips & tricks from you in the past…and it is going on!
Thanks a lot for such a great contribution for BOBJ comunity.
Regards,
Kaivalya.
Yes Dave I got you.Thanks
@Kaivalya, you are welcome, thank you for taking the time to let me know my contributions are helpful.
@Kumar, happy to help. It was a good question.
Hey Dave,
We had a similar requirement in out last project where in we had to schedule the reports every week for current week data and also to give the end user an option to enter Start date and End date when refreshing the report.
To resolve this we did the following:
1) Create two objects “StartDate Prompt†and “StopDate Prompt “(not filters) with the following definitions respectively:
StartDate Prompt: @Prompt (’Enter Fiscal Begin Date:’,'D’,, mono,free)
StopDate Prompt: @Prompt(’Enter Fiscal End Date:’,'D’,,mono,free)
2) Create “DateRange Prompt†filter where in Case statement is used to assign default values if the user does not enter any values for the prompts.
UV_DATE.CALENDAR_DATE BETWEEN case when
@Prompt(’Enter Fiscal Begin Date:’,'D’,,mono,free)=’ ‘
Then (current date – 7 days)
else (@Select(DATE RANGE\ StartDate Prompt) ) end AND case when
@Prompt(’Enter Fiscal End Date:’,'D’,,mono,free)=’ ‘
then (current date – 1 days)
else ( @Select(DATE RANGE\ StopDate Prompt) ) end
While scheduling, if single space is given as the parameter then the report can be scheduled weekly to get current week’s data.
The date prompt is very helpful. But I also wanted to give the option to select the time i.e. 12/12/07 4:30:00pm to 12/12/07 6:30:00pm. Can you please help me with that.
Regards,
Raj
Hi Dave,
Thats a wonderful tip which i learnt today.
Thank you so much.
Dave :
….. you Rock the world .. with your creativity in using …..Business Objects !!
Merry Christmas and a Happy New Year.
Thank you.
With sincere regards
indu
Raj, the prompts that I have built here all assume that the date is to be truncated, that is, to be selected without a time. If you are using Oracle then you can simply add the hours/minutes factor back to the resulting date.
For example, today was built as
trunc(sysdate). If you wanted to run that from 4:30PM to 6:30PM then you can add the appropriate number of hours, as in:trunc(sysdate)+(16.5/24)trunc(sysdate)+(18.5/24)
4:30PM is 16:30 in 24 hour time, and 16:30 is 16.5 hours, and so 16.5 / 24 gives you the decimal portion of a day that matches 4:30PM. You would, of course, have to use a “between” operator for all of your conditions since you have a start time and an end time.
Hope this helps get you started.
These are great tips! I’m going to go off on a relatped tangent. Is it feasible to create a dynamic schedule for a report. I want to trigger a report based on a table used as a change log. If there’s a new record, I want a report to go out. Is this API-only functionality?
Hi, Mike, sorry for the delay. I just got back from a vacation at a site run by your employer.
What version of Business Objects are you using? What I have done in the past using older versions is set up a failure rule on the schedule. If there are no new records then the report fails and thus is not distributed. If there are new records, then the report is distributed. I have not played with XI enough to know how to suggest doing the same thing in that version yet.
Dave,
Is there any reason why the Yesterday and Today objects will work with a BETWEEN statement and not an EQUAL TO in my Universe? The Birthdate and other objects are a Date. SQL code looks like this in BO:
This works:Birthdate BETWEEN trunc(sysdate-1) AND trunc(sysdate)
This returns no data: Birthdate = trunc(sysdate-1)
The most likely case is that Birthdate is not truncated. Therefore when you compare with a strict test like “equal to” it must match exactly. The botton line is that there is no reason why “Today” or “Yesterday” objects would not work using equality checks.
Dave, thanks for the reply. Great website for BO! Birthdate was not a good example. I apologizae for that. Admission date and time is a better one. If I change the WHERE statement in the SQL to trunc(pat_ENT_PATIENT.ADMIT_DT) = trunc(sysdate-1) and choose not to regenerate it works. That means either the end users do this or I create explicit objects for each date time field such as Admission Date/Time, Admission Date and Admission time using trunc. Any better ideas? Sorry, just took over this universe and I am in learning mode. The site has been very helpful!
If your date is not truncated just use a between operation rather than equal to. For example, if you say Admission Date Between Yesterday and Today, and “Yesterday” and “Today” are defined as I have done them in this post, then you’ll get everything from yesterday at midnight up to today at midnight. If you have an admission date that is exactly midnight, it could get counted twice. In which case you can do Admission Date >= Yesterday and Admission Date < Today. That lets you use the standard objects that you might build without having to edit your SQL.
Editing SQL is a bad idea.
Dave, Thank you so much!!! Editing the SQL is exactly what I want the end user to avoid! You’ve been extremely helpful as well as your site! Thanks again!
Dave, you are wonderful, it was really helpful… Thanks!!!
Dave,
Pls can you kindly help me to figure this out;
I have succeeded in creating my previuos and current month object, but i want their corresponding revenue to be display on a separate column and the measure object not aggregated. How do i go about it.
Note; If create a report with both objects(previous & current month), i want to display each of the data on a separate column.
I await your prompt response.
Tbaby, welcome and thank you for your comment. Your question isn’t solved by creating dynamic date objects, it’s going to be solved by using a calendar table and multiple query passes. In other words, a completely different approach.
I am in the process of writing up one way to do this for a presentation at the user conference next month (October) in Dallas. It’s fairly complex, and not something I am able to answer here in the limited space, and not on this post since it’s a completely different technique.
Come back in a month and you should be able to download the presentation from my conferences page.
How do you deal with this in business days including holidays. This is critical in financial data?
Hi, David, and welcome. If you have any non-standard issues to deal with I much prefer to use a calendar table than try to work with database functions. It makes the solution much cleaner that way.
Hey Dave, I have read over the blogs entries above, and also review the BOB website. I can not seem to figure out how to use the dayofweek function when trying to create a predefined condition.
In short, I am trying to identify which day it is, and then subtract x number of days from a transaction date data object within my universe.
Here is the code I have to date.
Case when dayofweek(CURRENT_DATE)=2 then
@Select(Transaction Details\Transaction Date) = (current_date – 3 days)
when dayofweek(CURRENT_DATE)=3 then
@Select(Transaction Details\Transaction Date) = (current_date – 1 days)
END
I get the following error when I try to parse my condition.
[IBM][CLI Driver][DB2] SQL0104N An unexpected token “=” was found following “”. Expected tokens may include: “END”. SQLSTATE=42601
:-104
I appreciate any help.
Thanks,
Dave,
I finally was able to make so progress on this.
@Select(Transaction Details\Transaction Date) = COALESCE(
(case when dayofweek(CURRENT_DATE) = 2 then (CURRENT DATE – 3 days)
when dayofweek(CURRENT_DATE)=3 then (CURRENT DATE – 2 days)
ELSE (CURRENT DATE – 1 days)
END),CURRENT_DATE)
But I would like to add to the when dayofweek(CURRENT_DATE)=3 then (CURRENT DATE – 2 days) part.. if current_date =3 I actually need to pull Current date – 2 and current date – 1.
Any ideas?
Thanks again,
Hi Dave,
Thanks for your tips.Its really helpful to novices like me in BO.
My users need to get report in email, which will tell them from which DB/env the report data are coming from either in the subject line/mail body or report name.Can this be possible through scheduling report feature in XI R2?Dynamically appending the DB/Env name while running and fetching data?
Hi, CA, welcome. Please note that your question really has nothing to do with this blog post. As per my support policy I would suggest that you post your question on BOB instead. Thanks.
Thanks for the awesome ideas !
Hello, I realize this is an older post but when I try and add the Today/Yesterday objects is posts the error you mention above and never creates the objects. What am I missing. (Bo XI r3.2)
-glen