Saturday, October 31, 2009

IsAncestor explanation

Recently I answered a post on the user forum regarding a way to test if a member is a Descendent of another member using MDX member formula's in an ASO database. It took me a little while to figure it out. I think it's a little hard for Essbase people to understand because of the way we used to do it in BSO using the function @ISDESC. Below was my response and I thought it was worth posting on the blog.

--In calc script language you are saying @ISDESC("C009") meaning you want hierarchical members below "C2009". Now in Calc script we also have @ISANCEST and if we said @ISANCEST("C009") we would be looking for all members that are hierarchically above "C2009". MDX does not have ISDESC, MDX only has IsAncestor. The parameters are member1 and member2. Now depending on which way you ask the question, you get a different answer.
IsAncestor([C009], [Dimension].CurrentMember) is really saying "Is "C009" an ancestor of the current member"? The answer is only true for descendants of "C009". Now, if you were to say IsAncestor([Dimension].CurrentMember, [C009]) you would be asking "is the current member an ancestor of "C009""? The answer is only true for ancestors of C009.

1 comment:

GlennS said...

Gary, I appreciate the explaniation. It was clear and consise, even if it will get confusing to people trying to debug code(or writing it). Is the member name first or second. It would have been much easier if they added the isdescendants like the ischild.