SSAS MDX Queries part 2

Children Property

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Country].[France].children on rows
from
[Adventure Works]

Exists Function

select
exists([Customer].[Customer Geography].[Customer].[Crystal Zheng],[Customer].[Customer Geography].members) on columns,
[Measures].[Sales Amount] ON ROWS
from
[Adventure Works]

Parent Function

select
[Measures].[Internet Sales Amount]on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.parent on rows
from
[Adventure Works]

Ancestor Function

select
[Measures].[Internet Sales Amount] on columns,
ancestor([Customer].[Customer Geography].[Customer].
[Crystal Zheng],[Customer].[Customer Geography].[City]) on rows
from
[Adventure Works]

select
[Measures].[Internet Sales Amount]on columns,
ancestor([Customer].[Customer Geography].[Customer].[Crystal Zheng],2) on rows
from
[Adventure Works]

Ascendants Function

 Returns all the ancestors of a specified member and the member itself up to the root of the member's hierarchy. Note that the specified member is returned first, then its parent, its grandparent and so on.

Example :

select
[Measures].[Internet Sales Amount]on columns,
ascendants([Customer].[Customer Geography].[Postal Code].&[OX14 4SE]&[Oxford]&[ENG]) on rows
from
[Adventure Works]

Ascendants with Hierarchize Function

Select
[Measures].[Internet Sales Amount]on columns,
hierarchize(ascendants([Customer].[Customer Geography].[Customer].[Crystal Zheng])) on rows
from
[Adventure Works]

FirstChild Function

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.firstchild on rows
from
[Adventure Works]

LastChild Function

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.lastchild
on rows
from
[Adventure Works]

Lastsibling  Function

select [Measures].[Internet Sales Amount]on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].lastsibling on rows
from [Adventure Works]

Descendants Function 

Syntax:

1. DESCENDANTS( «Member»[, «Level»[, «Desc_flags»]] )
2. DESCENDANTS( «Member»,«Distance»[, «Desc_flags»] ) 

According to MSDN, descendants MDX function returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
Desc_flags has 8 different flags option for returning set of descendant members. Here is description of each option mentioned in MSDN.
 
SELF Returns only descendant members from the specified level or at the specified distance. The function includes the specified member, if the specified level is the level of the specified member.

AFTER Returns descendant members from all levels subordinate to the specified level or distance.

BEFORE Returns descendant members from all levels between the specified member and the specified level, or at the specified distance. It includes the specified member, but does not include members from the specified level or distance.

BEFORE_AND_AFTER Returns descendant members from all levels subordinate to the level of the specified member. It includes the specified member, but does not include members from the specified level or at the specified distance.

SELF_AND_AFTER Returns descendant members from the specified level or at the specified distance and all levels subordinate to the specified level, or at the specified distance. S

SELF_AND_BEFORE Returns descendant members from the specified level or at the specified distance, and from all levels between the specified member and the specified level, or at the specified distance, including the specified member.

SELF_BEFORE_AFTER Returns descendant members from all levels subordinate to the level of the specified member, and includes the specified member. 

Examples :

select
[Measures].[Internet Sales Amount] on columns,
descendants([Customer].[Customer Geography].[Country].&[United Kingdom],[Customer].[Customer Geography].[City])on rows
from [Adventure Works]

select
[Measures].[Internet Sales Amount] on columns,
descendants([Customer].[Customer Geography].[Country] .[France],[Customer].[Customer Geography].[City],self) on rows
from
[Adventure Works]

Descendants with after

select
[Measures].[Internet Sales Amount]on columns,
descendants([Customer].[Customer Geography].[Country].[France],[Customer].[Customer Geography].[City],after)on rows
from
[Adventure Works] 

Descendants with before 

select
[Measures].[Internet Sales Amount]on columns,
descendants([Customer].[Customer Geography].[Country].[France],[Customer].[Customer Geography].[City],before) on rows
from [Adventure Works]

Range Function 

select
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar].[Calendar Quarter].[Q3 CY 2003]
on columns,[Measures].[Reseller Sales Amount]on rows
from [Adventure Works] 

Head(Top) Function 

select
{[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
on columns,head([Customer].[Customer Geography].[Country],2)
on rows from [Adventure Works] 

--It will display first 2 countries from the dimension

Tail(Bottom) Function 

select
{[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
on columns,tail([Customer].[Customer Geography].[Country],3)
on rows from [Adventure Works]

--it will show last three members of the Country level.