Date Objects Every Universe Should Have

A large amount of data analysis will use dates to filter or group results. This article looks at what objects and predefined conditions we should include in your universe that provide rich functionality to report authors that are querying with dates.

The code examples below mainly use SQL Server syntax and apologies to those of you who are using other database systems.

Key Universe Date Objects

Today, This Week, This Month, This Quarter, This Year

Our Universe should include these objects which return the current date, the current week number, current month name, current year and so on. These are useful in two scenarios: the user can create a query which contains filters based on these objects and the user can include these objects in a result set.

For example if you want to create a report that lists all orders that have a ship date in the future and if today is 1st of March 2009 then you could write a query that contains the filter,

[Ship Date] > 01-Mar-2009

however every day you’ll need to update it. You could of course use a prompt but it is much more convenient to create an object in Universe that will always return today’s date. We can then use a filter,

[Ship Date] > [Today]

Then the report will always use today’s date each time it is refreshed. Similarly if we want a report that lists all items being shipped in the current month we can have,

[Ship Month] = [This Month]

or even,

[Ship Month] = [This Month] AND [Ship Date] > [Today]

Note if current month just returns the month number then just using this object in a condition will return data for the current month across all years so may also want to include the year within the current month object.

The other scenario is where we want to include today’s date in a result set. In the query we just add our [Today] object to the result set and then display in the report.  An example of where we may want to use this is if we want to create a report that displays all orders that are shipping in the current month and then split these into two sections of those shipping prior to today and those shipping after today. To do this we can create a report variable,

=If([Ship Date]<[Today]; "Before Today"; "After Today")

Web Intelligence does provide a function that returns current date however this isn’t always ideal. Please see the article Limitations of Using System Date for a discussion on this.

These objects are based on database functions that return the current system date for example getdate() in SQL Server. The object in the Universe will have a defined Select clause but not an necessarily an associated table. This then means that when you parse the object you will get an error. To avoid this error you could associate the object with a table but this table will then be included in any report’s query that uses this object. At best you could associate the object with a Time dimension table in a data mart.

Is Today, Is Current Week, Is Current Month, Is Current Year

These objects return a “Yes” or a “No” to indicate whether a row has a date stamp that is either today or falls within the current week, or current month etc.  These are based on a columns of a Time dimension and an alternative to this, which may be preferable, is to have a time dimension that has columns that a indicate whether the row is today’s date or is in the current week and so on.

For example if you wants to create a report to select a set of sales transactions that fall within the current week then you can use a condition similar to,

[Is Current Week] = 'Yes'

Again we can also make use of these objects in a report result set. For example, if we want to display a report that compares this week sales to last weeks. Then we can create a crosstab and use the [Is Current Week] object in the horizontal axis to display the two sets of data,

These objects need to be created against a specific date column and use a CASE or DECODE statement, for example in SQL Server,

CASE [Ship Date] WHEN getDate() THEN "Yes" ELSE "No"

If we have a data mart that has a Time dimension then we can create objects against the columns in this table. Obviously if we have more than one date in our application, for example order date, despatch date, ship to date, then we’ll need to create objects for each different date if required.

One current limitation of Web Intelligence is that we can’t use functions within the where clause of a query. For example if we want to create a report that selects projects that are end within the next 20 days then we may want to add a filter similar to,

[Project End Date] < dateadd(dd, 20, [Current Date])

This isn’t possible in the current version of Web Intelligence however we can create some useful predefined conditions to provide a workaround for this.

In the next section below I’ve listed some predefined conditions that you may wish to include in your Universe.

Predefined Conditions

Today Plus N Days

To address the problem scenario above we can create a predefined condition that is essentially,

[Project End Date] < dateadd(dd, 20, getDate())

However to make things a bit more flexible we replace the ’20’ above with a prompt so that the report author can then decide how many days after today the wish to set,

[Project End Date] < dateadd(dd,
      @prompt("Projects ending in N days time where N is", "N",,mono,free),
      getDate())

These objects will also be specific to a date object or on the Time Dimension

This Week, Last Week, This Month, Last Month, This Year, Last Year

The predefined conditions This Week and Last Week are used to filter the data to only select transactions that occurred during this week or last week. You can also create similar conditions such as This Month and Last Month or Last Two Weeks, Next Two Weeks and which ones to implement should reflect common queries that your users will want to create.

Note, they should be created so that they can be combined, that is, a user can select both ‘This Week’ and ‘Last Week’ conditions to obtain last 2 weeks of data.

The following will filter for the current week,

[date] >= dateadd(wk, datediff(wk, 0, getdate()), 0) and
[date] <  dateadd(wk, datediff(wk, 0, getdate())+1, 0)

The following will filter for current month

[date] >= dateadd(mm, datediff(mm, 0, getdate()), 0) and
[date] <  dateadd(mm, datediff(mm, 0, getdate())+1, 0)

Note, the filter is greater than or equal to the first day of current month and less than first day of next month.

For a more comprehensive list of date manipulation functions please refer to the following articles,

Date Maths – SQL Server

Date Maths – IBM DB2

Data Maths – Oracle

This list is by no means a complete or comprehensive list of additional objects for a Universe but hopefully I’ve given you some ideas of what additional objects and predefined conditions can be included in a Universe. Certainly there is always a need for additional objects other than the objects that just map the table columns.

I hope you found this article informative and would love to hear your feedback.

See Also

Limitations with Using System Date.