Using Case Statement - OBIEE 11G

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.

Syntax

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.

CASE (If)

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.

Syntax

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.

END ends the CASE statement.

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 !

Using Set Operations in OBIEE 11g ( Union / Intersect / Minus)

There are 4 Set Operations available in OBIEE in OBIEE Answers Report Level to leverage the usual set operations functionality that a normal SQL writer would do to join two disjoint queries.

4 Set Operations are:

Set Operator	Effect on Results
Union	Returns non duplicate rows from all requests.
Union All	Returns all rows from all requests, including duplicate rows.
Intersect	Returns rows that are common to all requests.
Minus	Returns rows from the first request that are not in the other requests.

In OBIEE you can join distinct Subject Areas using set operations as well. The below article talks about the requirement and then solution using Set Operations.

Requirement 1: Show Open and Closed Service Requests(SR) Count by Month.

You cannot get Open and Closed SRs count in same report unless you create a RPD column and its not advisable to create RPD column for everything which you can achieve by creating simple report.

Solution using Union Operation:

Create a Simple Report having three columns as Month , Open SR Count and Closed SR Count.
Add Column Formula for Month as Opened Month and Count(SR_NUMBER) as Open SR Count and Closed SR Count as sum(0) to give this column as Aggregation data type.
In the Filters , Add SR Open Year = 2015 to get Open SR Count for 2015 year. This filter will give you all the SRs Count that was opened in 2015 , irrespective of the current SR status.

See below :

Open SR Count = Count(SR_NUMBER)

Closed SR Count = SUM(0)

Month = SR Open Month

Now Click on The Button to combine /Union another analysis to get Closed SR Count.

 Select the Subject Area as Service Request and you will see that Set Operations button is now added and you can create another analysis now with different columns and filters. Select the operator you want to choose.

 Here you will have to drag the columns as :

Drag SR Closed Month column next to SR Opened Month

Drag SR_NUMBER Column to Open SR Count

Drag SR_NUMBER Column to Closed SR Count

Then go to edit the formula and edit formula for last two columns as :

 Second Column will be Open SR Count, Edit the Formula to sum(0) as results will come from previous analysis. Change the heading as Open SR Count.

Third Column will be Closed SR Count, Edit the formula as Count(SR_NUMBER) and change the heading to Closed SR Count.

Add a filter SR Closed Year = 2015, to fetch you all data for 2015.

Click on Results tab to see the results.

This report is fetching all SRs which were opened and Closed in Year 2015 by Month.

Note: You can see January Closed SRs more than Opened because the data exists in warehouse for years prior to 2015.

 This was how you will use Union Operation.

In Progress for other Operations.