Business Intelligence – Oracle

Oracle BI EE & Essbase Connectivity – Report Use Case2 – Drills from Prompts – EVALUATE in Prompts

Posted by Venkatakrishnan J on December 8, 2008

In the last blog entry we saw one use case wherein we changed measure metric values depending on different dimension members. In this blog entry, lets look at another common multi dimensional reporting requirement. The typical requirement is to choose any member of a dimension in a prompt and then make the report to display all the child members(including that member) of the chosen member. For example, consider the report below.


As you see this is nothing but a very simple report containing All the members of Accounts, Gen2 of Year, Gen2 of Products and the their corresponding Actuals, Budget and Variance. Now, our aim is to have a prompt which will display all the members of the Accounts dimension. And on choosing any one member(at any level) in the prompt, we need to display the above report with the corresponding children of the chosen member for the Accounts dimension alone. So, let us start with building the prompt first. When we build a prompt especially for Essbase data sources, we need to make sure that we include a column which does not have an “is prompted” filter. This is necessary else, the MDX generated would be wrong especially when we use EVALUATE functions. Or the best option is to use a CASE WHEN statement which i have covered before in many blog entries. So, in the formula column of the prompt enter the below formula

CASE WHEN 1=0 THEN "Year"."Gen3,Year" ELSE 'Test' END


Then in the Show section of the prompt, make the display of prompt values to come from SQL results. Then enter the below logical SQL.

EVALUATE('%1.dimension.members',Accounts."Gen3,Accounts") saw_0
FROM "Basic#1" ORDER BY saw_0

Also, make the prompt to set a presentation variable called Drill.


When we preview the prompt, it should show us all the members in the accounts dimension.


Now, lets go to the report above. And in the Accounts formula column, enter the below EVALUATE function


The above EVALUATE function would basically display the children of the presentation variable Drill. The SELF_AND_BEFORE property would basically just include the member as well. We are also doing an intersect operation in order to ensure that the EVALUATE does not throw any error. Now, lets include both this report as well as the prompt in the dashboard. Let us choose Profit in the Prompt.


As you see, we only get the immediate children of Profit (which are Margin and Total_Expenses). Similarly, this would work for all the members without any children as well. For example, when COGS is chosen, it will display only COGS in the report since it does not have any children.


One Response to “Oracle BI EE & Essbase Connectivity – Report Use Case2 – Drills from Prompts – EVALUATE in Prompts”

  1. Prasanth said

    hey venkat,

    This information is very helpful. But lately,i am running into another problem

    I have an Org dimension which is across 5 generations. So In my report i have the org generation starting at Gen3 which goes all the way down to Gen5. So i have a prompt with 3 gen columsn gen3, gen4,gen5.So when i select some members in the prompts from gen3 to gen5 itz giving NO results. how ever it shows results in the report if i use a normal drill down instead of the prompts. So even it has results for the selected prompt combination it gives no results. But in the drill down it shows results.

    Prompt columns: gen3, gen4, gen5
    Report columsn: gen3, dollars
    filters in the report: gen3, gen4, gen5 are prompted

    Is there any specific way to handle Essbase prompts in OBIEE. Please advice.


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: