Ancestors Function in MDX

15
8265
Ancestors Function
Ancestors Function

Friends,

This is Very similar to the Function Ancestor in MDX.  The major difference is that Ancestor returns Ancestor where as Ancestors returns SET OF ALL ANCESTORS at the specified level. With Microsoft SQL Server Analysis Services, the set returned will always consist of a single member – Analysis Services does not support multiple parents for a single member. So, this is not a useful one in SSAS MDX. This function works on Hierarchies. If you pass a member and a level then it will  return the ALL the ANCESTORS of a specified member at a specified level. PFB the syntax of the Ancestors function.

Ancestors(Member_Expression,Level_Expression)

Member_Expression – A valid MDX expression that returns a member from a Hierarchy Level.

Level_Expression – A valid MDX expression that returns a level from a Hierarchy Level.

For Ancestors function when you pass a member from a level of hierarchy then the level parameter value can be any from the same hierarchy but it should be above the level to which the member belongs. For example consider time hierarchy –

Year –> Quarter –> Month

If you pass a member from Quarter level then the level parameter should be either Quarter and Year but not Month. If a level expression is specified, the Ancestors function returns ALL the MEMBERS(PARENTS) of specified member at the specified level. If the specified member is not within the same hierarchy as specified level, the function returns an error.

You can also specify Distance instead of LEVEL as parameter. If a distance is specified, the Ancestors function returns ALL the ANCESTORS of the specified member that is the number of steps specified up in the hierarchy specified by the member expression. A member may be specified as a member of an attribute hierarchy, a user-defined hierarchy, or in some cases, a parent-child hierarchy. A number of 1 returns a member’s parent and a number of 2 returns a member’s grandparent (if one exists). A number of 0 returns the member itself. The syntax looks like given below –

Ancestors(Member_Expression,Distance)

PFB the query sample which is using Level_Expression –

select [Measures].[Sales Amount] on Columns,
Ancestors([Dim Time].[Hierarchy].[Calendar Year].&[2002].&[3].&[September],[Dim Time].[Hierarchy].[Calendar Quarter]) on Rows
From [Adventure Works]

PFB the same query as above but using Distance –

select [Measures].[Sales Amount] on Columns,
Ancestors([Dim Time].[Hierarchy].[Calendar Year].&[2002].&[3].&[September],1) on Rows
From [Adventure Works]

Ancestors Function

In the above Screenshot you can clearly see that Ancestors function is also returning a SINGLE value as Many parents are not allowed in SSAS. You can use Ancestors function to navigate to any level above to the level to which the member passed as parameter belongs to. There is one more function which acts in the same fashion i.e Parent. Learn more about Parent Function here.

That’s it .. Happy Coding !!

Regards,

Roopesh Babu V

15 COMMENTS

  1. Talk to your doctor about eating grapefruit and drinking grapefruit juice while taking Viagra cialis online generic The American College of Physicians recommends looking at how quickly the medication takes effect, how long it lasts, the side effects and the price

  2. Наращивание ресниц — эффектная замена макияжа глаз. Но только на время — и, как правило, короткое. Позже приходится либо повторять процедуру, либо возвращать собственные ресницы к жизни Мои ресницы очень сильно пострадали после наращивания. Больше половины просто высыпались, а оставшиеся – сломались и стали очень тусклыми. Первые недели пробовала касторовое масло, но это явно не для быстрого восстановления. Затем решила дать шанс профессиональной сыворотке. Все ингредиенты можно купить в аптеках. Лучшим средством для восстановления ресниц является касторовое масло. Наносить средство лучше всего вечером после снятия макияжа, проще всего сделать это тонкой кисточкой. Держать его на ресницах нужно 1 час. Курс лечения составляет один месяц. Не стоит ждать моментального результата от масок и компрессов. Все природные компоненты, содержащиеся в маслах и травах, имеют свойство накапливаться. Поэтому необходимо набраться терпения и пройти весь курс, чтобы восстановить ресницы после наращивания в течение месяца. https://www.lifestyleandmindsetassist.com/community/profile/bernadetteschul/ Благодаря этому ресницы достигают максимальной длины на фазе роста и дольше находятся в стабильном состоянии. Приведенные цифры получены на основе клинических исследований, проводимых на многочисленных группах респондентов. Испытания подтвердили безвредность косметического средства. Карепрост для ресниц применение – некоторые уточнения: «Когда подруга рассказала о карепросте я сначала не поверила. Звучало как что-то нереальное. Но когда подруга показала отзывы в интернете я задумалась, ведь сотни человек не могут писать просто так. Решила тоже заказать. Знаете, я не думала что эффект может быть ТАКОЙ)) У меня ресницы выросли значительно больше чем у подруги! ) Теперь она мне завидует ))» Средство стимулирующее рост ресниц.Содержит компонент БИМАТОПРОСТ.Этот компонент проникает в волосяной мешочек ресницы и стимулирует быстрый рост.Объем  3 мл. Страна производитель ИндияЧтобы купить карепрост в Харькове просто оформите заказ на нашем сайте

  3. Canadian law does not forbid you from playing at online casinos such as Spin Casino. Instead, the law is aimed more at online casino Canada real money operators, who may not operate gambling websites if their servers are located within the country. These are some of the simplest games at our online casino, but some of the most irresistible too. You’ll learn how to play within a matter of moments but it could be hours before you are able to tear yourself away! Five-card Stud Poker rules are blended with the pace of a slots machine for a brand-new type of gambling fun. The ‘Line Bet’ and ‘Total Bet’ fields will read ‘FREE’ to indicate that this is a Free Spins session. After you have used all your free spins, any Free Spins Bonus winnings will be credited to your account either as a Casino Instant Bonus or as cash, depending on the terms of the Bonus. https://www.basement-rebel.com/forums/member.php?action=profile&uid=103703 Whenever you are on the lookout for new online poker sites to try, have a look at our reviews. Those are your best resource to get to know what the top online poker rooms are and find out what poker website hosts exactly the games you want to play. +150 free spins Regardless of the result, the $1K $2K Pot-Limit Omaha game on CoinPoker has caught fire in recent weeks. With G, King’s Resort owner Tsoukernik, and other high-stakes players anteing up, more seven-figure pots are likely, which could be the catalyst crypto poker sites need. Whenever you are on the lookout for new online poker sites to try, have a look at our reviews. Those are your best resource to get to know what the top online poker rooms are and find out what poker website hosts exactly the games you want to play. Unlike the regular top-rated Cafe casino poker games, video poker is slightly different. They offer a very amusing and attractive combination of the rules of standard poker card games and online slot machines. This is what makes video poker casino games a preferred pick of many online players.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

7 + 2 =