OBIEE version: 188.8.131.52.0
Essbase version: 11.1.2
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.
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.
If I got you correct, then your account hierarchy is something like
h1. Account (Gen1)
h2. --> Account 1 (Gen2)
h4. ---> Sales (Gen3)
h4. ---> Sales % (Gen3)
h2. --> Account 2 (Gen2)
h4. ---> Sales1(Gen3)
h4. ---> Sales1 % (Gen3)
and you are trying to analyze yoru numbers with this dimension. If you are, then how about having Gen3, Account dimension as the pivot table prompt.
If this is not what you are trying to achieve, is there anyway you can upload a picture/some format of the requirement?
Hope this helps.
Thank you for your quick reply.
I have added couple of screenshot to flickr to clarify my problem: http://www.flickr.com/photos/93812026@N07/
Yes, your assumption is correct. We are using gen x level members in report, but these members are shared members or Dynamic calculations i.e. we have created custom account hierarchy for them.
Basic situation, when I query that hierarchy. I.e. it returns account names for both members in the hierarchy. Furthermore, so far I have used only one pair of account, i.e. WOC
I'm able to "merge" these two account members simply by adding "case when" statement to account dimension object. It shows values correctly in a table (picture 4)
BUT, when adding other accounts to query filter, the returned values are no longer correct, but some sort of multiplication.
--> it seems, that OBIEE cannot figure the context it should do the calculation against and I don't know how to define it there.
Ps. Next step was, that we tried to use dashboard prompt to return correct values to calculations, but after being able calculate correctly values for these above mentioned accounts, we ran into problem, when trying to add entity level to dashboard prompt (OBIEE gave an error refering Essbase). I believe this is a bug in this version.
I managed to overcome this problem by simply creating calculated item to each and every decoded (case when then +'xyz+') value used in report. To others, that might struggle with similar problem, here is an example:
In Essbase I have two members; Member 1 and Member 1 %
-> I created separate calculation to both Member 1 and Member 1 % to be used in report (= as pseudocode: Measure 1: FILTER("Measure" USING("Member Gen" = 'Member 1')) AND Measure 2: FILTER("Measure" USING("Member Gen" = 'Member 1 %')) ))
-> In OBIEE I decoded these two (not calculations but members) to be shown as 'Member' ( = as pseudocode: Case when 'Member' or 'Member 1 %' then 'Member' ) in Pivot table prompt drop down list.
-> Then I added this decoded object to pivot table prompt field
-> After which, I simply created calculate item for each and every decoded value ( I simply created calculated item by name "Member" and used decoded member value in Selected field) and checked 'Remove calculated item members from view'.
That's it. Now I'm able to use one drop down value to show both bar and line values in chart and get OBIEE to do the calculation correctly.