Merging two Essbase member names in OBIEE pivot table prompt
OBIEE version: 11.1.1.6.0
Essbase version: 11.1.2
Hi fellows,
I would appreciate your advice on the following problem.
Background:
We are reporting HFM data coming through Essbase using OBIEE. One of the report requirements is to report ‘measure 1’ (alias: Sales) and ‘measure 1 %’ (alias: Sales %) for specific accounts in same chart i.e. in Line-Bar chart.
I have created a custom account hierarchy to Essbase, which is used to retrieve only specific accounts to the report. In that hierarchy, I have two members for each account (all together 4 different accounts) to be used in the report. One for absolute value (for example account 224455 for Sales), which is used as bar in chart and the other one for % -calculation (for example account ‘224455 / account 556677 * 6’) which is used as line in chart.
Reason, why this % calculation is done to Essbase is, that OBIEE, for some reason, returns null when trying to apply it straight to the report (using formula ~ filter(measure using(account 224455) / (filter(measure using(account 556677)*4).
Problem:
I need to use pivot table prompt to show only one account at a time in chart. As I have four of them, pivot table prompt seems to be only way to do that. Problem here is how to have only one pivot prompt value for both absolute and % value?
I have tried also to used case when statement in report. Something like:
Case
when account in (Sales, Sales %) then Sales
when ….
else
end
...but this does not seem to solve the problem.
I have also tried to create a workaround by creating a dashboard prompt and them applying that to the calculation rule in the report. It seems to work until I add another dashboard prompt (I also have to have selection for organization in the report), after which the report returns error saying that created prompt cannot be recognized in Essbase. This sounds like a bug in OBIEE.
I would appreciate any suggestion / advice on which way to proceed with this problem.
-Esa-
Ps. I have added this very same question to communities.oracle.com as I don't understand the difference between these two forums...sorry for multiple posting.