Cindy Howson has spend a separated chapter about this question. Please read her splendid book. BusinessObjects XI: The complete reference.

Occasionally, there is a single, clear-cut choice for where to put the intelligence. You are low on disk space, so you add it to the universe; the calculation is too complex for SQL, so you create a report variable. These are the easy answers. The not-so-easy answers are everything else in between.

The best place for intelligence is as suggested by the consultant’s annoying but valid answer “it depends.”

Intelligence is information with a business context. QUANTITY may be a physical column in a table. Add a time period such as month, then a context to the time period such as order month, and multiply the column by a price, and you arrive at Sales Revenue, something with meaning and value to business users.

And now the dilemma? I have a date-field, let’s say order-date for every order in my fact-table.

The end-user want to display and count all orders for the previous months until to date (YTD).

Without the ‘Ultimate Generic Date/Time framework’ implemented in your universe. You probably create a month & year object in your universe.  And try to extract the information by dropping following objects

  • month(order_date)
  • year (order_date)
  • count(order_id)

in the query panel, and using a where-clause like this

  • year(order_date) = 2012

But what if you have also a sales_date, billing_date, pre_order_date, cancel_date, delivery_date,… and so on, and you also want to know the quarter, the semester,… of these dates.

Then you will need to create lot of objects (semantic-logic) in your universe. Not implementing all these options from the beginning, can result in a higher maintenance cost afterwards. Not preferable if you want to cut your maintenance costs.

Learn the end-user how to create a month, year,… variable in the report (should be a common skill of a report creator) and how to use ‘Ultimate Generic Date/Time Framework’.

Double click to enlarge the image

The end-user only has to drop the order_date into the query panel. And use the following where clause (based on objects in the date/time framework.)

Double Click to enlarge the image

Then the report creator need to create some variables using the date-time functions in his report.

Difficult? No, you only have to provide the user with a template report, with the most common functions, like those below.

Double click to enlarge the image

No time to create such a template report? No panic, just download it from my dropbox