This content has been marked as final. Show 3 replies
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.