Dave's Adventures in Business Intelligence

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.

1. Create a condition on Order Date Equal to and select Object from the condition operand options.

   

2. Browse the universe to my Dynamic Dates for Scheduling class and select Yesterday.

   

3. 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:

1. Order Date Equal To Today
2. Order Date Equal To Yesterday
3. Order Date Between Yesterday and Today
4. 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