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.

SSAS MDX Queries part 1

Simple MDX query 

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Product].[Category].MEMBERS ON ROWS
FROM [Adventure Works]

Hiding Nulls

select non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]}
on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]}
on rows 

from [Adventure Works]

Introducing .members

select
{[Product].[Category].[Category].members,[Product].[Category].[All Products]} on columns,
[Measures].[Sales Amount] ON ROWS
from
[Adventure Works]

Individual Members

select
[Product].[Category].[Category].[Bikes] on columns,
[Measures].[Sales Amount] ON ROWS
from [Adventure Works]

Multiple Members

select
{[Product].[Category].[Bikes],[Product].[Category].[Clothing]} on columns ,
[Measures].[Sales Amount] ON ROWS
from [Adventure Works]

User Hierarchies

User hierarchies are created when dimensions are designed in BIDS. A user hierarchy is a
hierarchy that has three or more levels (including the All level at the top).

select
{[Product].[Product Categories].Allmembers}on columns
,{[Product].[Category].Allmembers} ON ROWS
from
[Adventure Works]WHERE [Measures].[Sales Amount]

Same Non-measure Dimension on Two Axes with Differing Hierarchies

select
[Date].[Calendar].[Calendar Year] on columns,
[Date].[Month of Year].[Month of Year] on rows
from [Adventure Works]

Cross Joins

If you crossjoin two sets of members from
the same dimension, the two sets must be based on different hierarchies within the
same dimension. You can even crossjoin two different dimension

Example 1 :
select
NONEMPTY({[Product].[Product Categories].Allmembers})on columns,
NONEMPTY(crossjoin([Date].[Calendar].[Calendar Year], [Date].[Month of Year].[Month of Year])) on rows
from
[Adventure Works] WHERE [Measures].[Internet Sales Amount]

Example 2:

select
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]} on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from [Adventure Works]

Example 3:

select
crossjoin([Product].[Product Categories].[Category],{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]}) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Example 4(Crossjoin on Two Separate Non-measure Dimensions)

select
crossjoin([Sales Territory].[Sales Territory].[Country],[Product].[Product Categories].[Category]) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Example 5:(Nested Cross Jons)

select
crossjoin([Sales Territory].[Sales Territory].[Country],crossjoin([Product].[Product Categories].[Category],
{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]})) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Alternate for Cross Joins

select
crossjoin([Sales Territory].[Sales Territory].[Country],[Product].[Product Categories].[Category],
{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]})on columns,
([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year])on rows
from
[Adventure Works]