Tuesday, January 25, 2011

Identifying Descendants with MDX? Use IsAncestor

There were many times when I found myself wondering why there wasn’t an ISDESC or IsDescendants function for MDX. Then one day I found the IsAncestor function. With this function, a descendants function is completely unnecessary, although it is a little counterintuitive when you are used to the BSO world of formulas.

The formatting of this function is: IsAncestor (member1, member2 [,INCLUDEMEMBER]).

The trick to this function is to ask yourself each time you use it “Is the first member the ancestor of the second member?”

For the examples, I will use the Sample ASO application and focus on the Market dimension.
So, if you wanted to see all of the descendants of East in the Market dimension your formula would be IsAncestor([East], [Market].CurrentMember) with the result set being New York, Massachusetts, Florida, Connecticut, and New Hampshire.

If you wanted to see all descendants of East in the Market dimension including East, your formula would be IsAncestor([East], [Market].CurrentMember, INCLUDEMEMBER) and your result set would be East, New York, Massachusetts, Florida, Connecticut, and New Hampshire.

You can also use the function to test and see if a member is a descendant of another member. An example of this would be IsAncestor([Market].CurrentMember, [Florida]). The result set would be true for the East and Market members and false for all other Market members. If you add INCLUDEMEMBER in the function your result set would also include the member Florida.

It would be far easier if there were a descendant function in MDX, but once you get used to using IsAncestor, you will find it will do the job every time.
at

1 comment:

  1. AnonymousJune 21, 2013 at 5:16 PM

    Hi. My name is Anna. I work with BSO for 15 years. I created my first ASO cube recently I came across your blog in my search to resolve a problem with the MDX formula. I found some very helpful tips in your blog. I added the link to my favorites and will be checking your new posts. Please keep posting.
    I’m working on converting one of the BSO formulas to MDX. One of the members in the formula has a fixe on the shared members roll-up.
    CASE
    When IS([Reporting View].currentmember,[Card_Network_View]) and islevel ([Cards_Network_Direct].CurrentMember,0) then
    ([TotExpenses],[reported])
    END
    All the children of [Cards_Network_Direct] are shared members. The formula validates and I save the outline without an error but it does not work. It appears that “islevel ([Cards_Network_Direct].CurrentMember,0)” is ignored. Have you ever come across a similar issue? My email is annabalzak@discover.com. I would feel very grateful if you respond. Thank you. Anna

    ReplyDelete

Subscribe to: Post Comments (Atom)