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