Thursday, 16 April 2015

OBIEE Filter Sliding Last Day of Month

In general OBIEE repositories provide a bundle of date repository variables which update for example every 30 minutes to easily calculate a sliding window on the date dimension. So, users don't have to change their reports in the course of time:

OBIEE Line-Bar Chart

OBIEE Date DimensionWhen having a look to the Oracle BI line-bar chart above it shows figures by the last day of the month in a sliding window. Such a request isn't very common and typically there are no date repository variables for such a requirement. But more likely two variables like

CURRENT_MONTH_YAGO
PREVIOUS_MONTH

should be available. But filtering the last day of several months on a standard date dimension in a sliding window is not possible with these Oracle BI repository variables. The solution is based on the same functionality already introduced in blog post OBIEE Substitute a Filter Variable. When applying the filter set a SQL Expression value as follows:

CASE
  WHEN "Calendar Date"."Month Code" IN
    (1,3,5,7,8,10,12) THEN 31
  WHEN "Calendar Date"."Month Code" IN
    (4,6,9,11) THEN 30
  ELSE 28
END

In the Edit Filter dialogue box it looks like:

OBIEE Edit SQL Expression Filter

The filter of the report will be:

OBIEE Filter

No comments:

Post a Comment