We can make use of native Essbase Functions using the Evaluate and Evaluate_Aggr Expressions in OBIEE.
Let’s first see how we can call an Essbase Function TOPCOUNT from within OBIEE.
The TOPCOUNT function returns a set of n elements ordered from largest to smallest, optionally based on an evaluation. For example:
To Find the top 3 Products ranked by Sales I would use the following statement:
EVALUATE(‘TopCount(%1.members, 3, %2)’ as VARCHAR(20), Products,
Sales)
Where 3 is the INDEX of the Topcount function.
In the above statement the index n which is equal to 3 is passed to the Essbase Cube in order to retrieve the TOP 3 products ranked by sales.
In order to Call this function from within the OBIEE RPD, I have created a new logical column in the BMM layer called Top3 and in the Expression Builder I use the following statement.
EVALUATE(‘TopCount(%1.members, 3, %2)’ AS VARCHAR ( 20 ), “universi-vlz1vh.oracle.com”.”Sales”.””.”Sales_db”.”Gen4,PRODUCT_D”, “universi-vlz1vh.oracle.com”.”Sales”.””.”Sales_db”.”SALE_AMOUNT”)

When entering the expression I would recommend inserting the required columns from the Expression Builder window rather than typing it. You will be less prone to make syntax errors this way.
Now when you drag this new column into the Presentation Layer and run a report in Answers you will see that the Top3 column does in fact give you the Top3 products ranked by Sales.


Now let us see if we can make the index in the topcount (3 in the above case) function dynamic. Basically what I would like to do is to populate the index using a variable instead of hard-coding it in the Expression like we did earlier.
Let us see if the Evaluate Expression will work if we use a sessionVariable as the index.
I have created a session variable “N” which will return a numeric value. Then I created a new logical column and in the expression builder entered the following:
EVALUATE(‘TopCount(%1.members, VALUEOF(NQ_SESSION.N) , %2)’ AS VARCHAR ( 20 ), “universi-vlz1vh.oracle.com”.”Sales”.””.”Sales_db”.”Gen4,PRODUCT_D”, “universi-vlz1vh.oracle.com”.”Sales”.””.”Sales_db”.”SALE_AMOUNT”)
You can see that I have replaced the index with the session Variable Value.
Now let us run the report and see what we get.


You can see that an error is generated. If you look at the MDX being generated by OBIEE then you find that the the session Variable value is not being passed into Essbase and so we get the Unknown Member used in Query Error. So to my knowledge it is not possible to dynamically pass the value of a OBIEE session variable to Essbase as an index value.
With
set [Evaluate0] as ‘{TopCount([PRODUCT_D].Generations(4).members, VALUEOF(NQ_SESSION.N) , [Accounts].[SALE_AMOUNT]) }’
set [Axis1Set] as ‘{[Evaluate0]}’
select
{} on columns,
{[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Sales.Sales_db]
This is a significant handicap that puts limitations on the use of Evaluate Expressions to leverage the native Essbase Functions.
Filed under: Essbase, Hyperion, OBIEE | Leave a comment »