Oracle BI EE 10.1.3.4 & 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.