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]