OBIEE 10G/11G - Conditional Filtering based on Presentation Service Variable

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - About

How to apply a filter based on a condition ?

3 - How to

The value of stock is a good example. You can't sum it because it's point in time fact. The stock at the end of the week is not the sum of all stock by day.

If you have a table on a day grain and that you want to show a report on the week grain, you have to show for instance only the value of the stock the last day of the week.

Pseudo Code

IF the level IS DAY THEN
  No Filtering
IF the level IS week THEN
  ONLY the stock FOR Sunday
ELSE
  RETURN No DATA
END IF

To achieve the same in OBIEE, create a Advanced SQL filter and add this logical sql:

CASE '@{Level}{Day}'
  WHEN 'Day' THEN
    1
  WHEN 'Week' THEN
    "Date Dimension"."Day in week"
  ELSE
    1
  END) =
(
  CASE '@{Level}{Day}'
  WHEN 'Day' THEN
    1
    /* 1 = 1 then no Filter is applied */
  WHEN 'Week' THEN
    7
    /* We take only the stock on Sunday */
  ELSE
    0
    /* 1=0. Return no data, this case is not expected */
  END)

In this example, a presentation service variable: @{Level} is used.

Advertising

4 - Support

4.1 - Syntax error - nQSError: 27002

The CASE clause in the logical SQL is evaluated after the parse step during the query processing then you cannot include your predicate in your case statement.

For instance, this statement is forbidden

CASE '@{Level}{Day}'
  WHEN 'Day' THEN
    1 = 1
  WHEN 'Week' THEN
    "Date Dimension"."Day in week" = 7
  ELSE
    1 = 0
  END 

You will get this kind of message:

[NQODBC] [SQL_STATE: HY000] 
[nQSError: 10058] A general error has occurred. 
[nQSError: 27002] Near <=>: Syntax error [nQSError: 26012] . (HY000)