OBIEE - Case When Statement

1 - About

This operator calculation have two type:

  • In a CASE statement, AND has precedence over OR.
  • If no ELSE expression is specified, the system will automatically add an null.

You can enter it on every place where you create a logical sql as the formula in Presentation service but also in the BI Server Expression Builder.

A Point-and-click generation of case statement logic is given by the Bins tab of the Column Formula

3 - Type of case

3.1 - Default

If the last ELSE statement is omitted, “ELSE NULL” is assumed. The case when statement returns then NULL.

3.2 - Case If

CASE WHEN "Sales Facts"."Amount Sold" > 600000  THEN 'Big' 
     WHEN "Sales Facts"."Amount Sold" > 300000  THEN 'Middle' 
     ELSE 'Small' END

Request Condition Operator:

  • >
  • =
  • IN <note info>The IN operator can only be used in the repository and not in the logical sql (ie in an answer/analytics)</note>

3.3 - Case Switch

It's also known as the case Lookup.

CASE  Promotions."Promo Subcategory" 
WHEN  'TV commercial'          then 'Commercial'  
WHEN  'TV program sponsorship' then 'Sponsor'  
ELSE  'Default' 

4 - Documentation / Reference

5 - Support

5.1 - With the GROUP system session variable in the repository

'Administrators;XMLP_ADMIN' in ('Administrators', 'CNT_ADMIN')

5.2 - Bug

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap