Business Intelligence – Oracle

Oracle BI EE & Essbase Connectivity – Report Use Case 3 & 4 – CASE Statements and Variable Measures – Adding and Subtracting Measures

Posted by Venkatakrishnan J on December 9, 2008

We are in the last article(probably one more) of our article series on BI EE and Essbase Connectivity. The one piece that is pending is the Essbase Security and BI EE which i shall cover later. In this article we shall see 2 report use cases which are typically common requirements especially on multidimensional data sources. Lets start with the first use case

A report containing Gen2, Product, Gen2, Market,Actuals and Budget. All the members that are less than 20000(for Actuals) should be displayed as 0. And for Budget all the values should be displayed as is

Typically, the usual way to approach this problem is to build a report like the one shown below. And then on top of it a filter on Actuals > 20000 would be applied.


But the problem with the above approach is that the filter would be applied for the entire report instead of the Actuals metric alone. So we would be losing out on members with proper data in the intersections.


Now, in order to overcome that, lets remove the filter and in the Actuals column enter the below EVALUATE function

EVALUATE('case when (%1.dimension.currentmember,%2.dimension.currentmember,[Actual]) > 20000 then (%1.dimension.currentmember,%2.dimension.currentmember,[Actual]) else 0 end' AS INTEGER,Market."Gen2,Market",Product."Gen2,Product")


Now, if you look at the report, this would show the data for all the intersections for Budget as well as Actual. Also, all the Actuals which have data < 20000 would have been converted to 0.


Lets move on to the next use case. A typical requirement is to subtract [Actual] and [Budget] at the reporting layer rather than having an outline member called Variance. The use case is stated below

A report containing Gen2, Product, Gen2, Market, Actual, Budget and a report level variance (a calculated column from BI EE which is Actual – Budget).

We shall use the same report above. Just pull in all the columns Gen2, Product, Gen2, Market, Actual and Budget into the report. Now, add one more column and in the formula of the column use the below evaluate function

EVALUATE('(%1.dimension.currentmember,%2.dimension.currentmember,[Actual]).value - (%1.dimension.currentmember,%2.dimension.currentmember,[Budget]).value', Product."Gen2,Product",Market."Gen2,Market")


Though both the above use cases are pretty simple, i wanted to cover them in this blog entry as i found these occurring quite a few times in our internal forums.

One Response to “Oracle BI EE & Essbase Connectivity – Report Use Case 3 & 4 – CASE Statements and Variable Measures – Adding and Subtracting Measures”

  1. Michael Crutcher said

    Hey, thanks for your blog, there’s a lot of good information in here. I was wondering if while using the evaluate function you’ve gotten this error:

    Target data source does not support the Complex Expression operation

    I have yet to be able to get any evaluate function to return anything due to either this error or the detected an aggregate in the outer query block error. Is there something else that needs to be setup in OBI Admin tool to make evaluates return at all?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: