How to Display Essbase members in OBIEE Report using Outline Order

Question

Hi

I have requirement that OBIEE report should display the members in the same order they exists in Essbase.  I need to display all the descendants of a member that is passed from Prompt by user.  I am able to do that, thanks to all help on this thread (Essbase descendantas reporting using OBIEE similar to HFR  Essbase descendantas reporting using OBIEE similar to HFR  )

I referred these sources

https://oraclebizint.wordpress.com/2008/04/28/oracle-bi-ee-101332-handling-sort-order-in-hyperion-essbase-931-evaluate-and-mdx/

and

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/biee/r1013/essbase/biee_essbase.htm

however it looks they they are with assumption that Gen 4 is the lowest level (i.e. level 0 in essbase) always. However from Essbase stand point, this is not always true and most of the time they are unbalanced.

Thanks in advance.

-Kiran

ArijitC · Answer

Sorry, I did not read through the whole question previously.

Really I did not had good success using rank function to sort my members. I remember trying it few years back and struggling to make it even return value. I had used hard coded case statement. Which I never liked but did the work for me.

I have an idea about using essbase intrinsic outline member property to sort outline. I'm talking about outline member properties

* GEN_NUMBER
* LEVEL_NUMBER

I will try that tomorrow in my environment, I will update this thread if I get any success.

Thank You

Arijit

3227688 · Answer

I have written the same way as below to derive the descendants at level 0.

EVALUATE('Descendants([@{PV_Entity}], Levels([Entity],0))/*%1*/' ,"Entity"."Gen1,Entity").

My current questions is with sort order of members displayed in OBIEE report.

How can i prevent OBIEE sorting the members and keep them in same order they exists in outline.

ArijitC · Answer

3227688 wrote:however it looks they they are with assumption that Gen 4 is the lowest level (i.e. level 0 in essbase) always. However from Essbase stand point, this is not always true and most of the time they are unbalanced.Thanks in advance.

No it does not. If you understand the way that evaluate works along with essbase , you will see that what ever generation you put in bold italics section below will be  commented out by the section /*%1*/ in actual MDX generated

EVALUATE('Descendants([@{PV_Entity}], Levels([Entity],0))/*%1*/' ,"Entity"."Gen1,Entity")

Thanks

Arijit