Using Case Statements in OBIEE
Case Statement usage in OBIEE should be avoided if it is
possible. It slows down the performance of the report.
If you feel that Case statement can be implemented in the RPD as its used as a metric or calculation which can be used
multiple times in the reports, then it should be added back into OBIEE RPD to help improve the performance of the reports.
There are two Case Statements in OBIEE :
Case Switch and Case IF. There is a little help available
when you select that function to tell you about how to use it. To write a
formula using Case, select that and click Ok. Case default function will be
added to the formula.
CASE (Switch)
This form of the CASE statement is also referred to as the CASE(Lookup) form. The value of expr1 is examined, then the WHEN expressions. If expr1 matches any WHEN expression, it assigns the value in the corresponding THEN expression.
If none of the WHEN expressions match, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.
If expr1 matches an expression in multiple WHEN clauses, only the expression following the first match is assigned.
CASE “Products”.”Modality”
WHEN ‘CT’ THEN ‘Canada’
WHEN
‘AW’ THEN ‘Surgery’
ELSE ‘Americas’
END
Where:
CASE starts the CASE statement. Must be followed by an expression and one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.
WHEN specifies the condition to be satisfied.
THEN specifies the value to assign if the corresponding WHEN expression is satisfied.
ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.
END ends the CASE statement.
This form of the CASE statement evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression.
If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.
CASE
WHEN
“Products”.”Modality” = ‘AW’ OR “Products”.”Modality” = ‘CT’ THEN ‘Surgery’
WHEN
“Products”.”Modality” = ‘Anastesia’ THEN ‘Americas’
ELSE ‘Canada’
END
Another Example is :
CASE when TimestampDiff(SQL_TSI_DAY,
CURRENT_DATE , OPEN_DATE) > 30 THEN ‘RED’ WHEN TimestampDiff(SQL_TSI_DAY,
CURRENT_DATE , OPEN_DATE) <10 THEN ‘GREEN’ ELSE ‘YELLOW’ END
In below example to create customized metrics for Activity Open Date etc, if
Activity Open Date is Null, then Take Activity Created Date
CASE WHEN
ACTIVITY.OPEN_DATE is NULL THEN Activity.CREATED_DATE ELSE ACTIVITY.OPEN_DATE
END
Where:
CASE starts the CASE statement. Must be followed by one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.
WHEN specifies the condition to be satisfied.
THEN specifies the value to assign if the corresponding WHEN expression is satisfied.
ELSE specifies the value to assign if none of the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.
You can Nest
CASE Statements as well. E.g .
To calculate Open Date , there is a
case statement (CASE WHEN ACTIVITY.OPEN_DATE is NULL
THEN Activity.CREATED_DATE ELSE ACTIVITY.OPEN_DATE END)
Now if you want to use the OPEN_DATE from above case
statement to calculate RED, Yellow and Green, you can do so by nesting case
statements, Instead of using OPEN_DATE column in TimeStampDiff formula, you can
use Case Statement.
So the Case Statement is e.g.:
CASE when
TimestampDiff(SQL_TSI_DAY, CURRENT_DATE , OPEN_DATE)
> 30 THEN ‘RED’ WHEN TimestampDiff(SQL_TSI_DAY, CURRENT_DATE , OPEN_DATE) <10 THEN ‘GREEN’ ELSE
‘YELLOW’ END
You can embed OPEN_DATE as case statement in this formula
too.
CASE when TimestampDiff(SQL_TSI_DAY,
CURRENT_DATE , (CASE WHEN
ACTIVITY.OPEN_DATE is NULL THEN Activity.CREATED_DATE ELSE ACTIVITY.OPEN_DATE
END)) > 30 THEN ‘RED’ WHEN TimestampDiff(SQL_TSI_DAY, CURRENT_DATE , (CASE WHEN ACTIVITY.OPEN_DATE is NULL THEN
Activity.CREATED_DATE ELSE ACTIVITY.OPEN_DATE END)) <10 THEN ‘GREEN’
ELSE ‘YELLOW’ END
Have a great Day !
