Monday, 14 July 2014

OBIEE Substitute a Filter Variable

Have a look to the following report querying some figures on a weekly basis and showing the result per day.

Oracle BI Trellis Chart

The analysis is filtered on ISO week by using a refreshing repository variable called CURRENT_WEEK.


In ISO calendar the week turns on Mondays. When not reporting on real-time data and having a one day load frequency you will see no results each Monday. So it would be best to see previous week data on Mondays and of course having not change the filter criteria in the report. To achieve delete the repository variable filter and add a filter on ISO week using a SQL expression:


As expression insert:

CASE
  WHEN DayOfWeek(Current_Date) = 2
  THEN VALUEOF(PREVIOUS_WEEK)
  ELSE VALUEOF(CURRENT_WEEK)
END

Here we go! On Mondays your users will see previous week data.
Interested in some more end user topics? Check out Oracle BI useful things.

No comments:

Post a Comment