Basic MDX Queries

------------Restricting Cube Space/Slicing Cube Data---------

//The subcube name should be same as that of the cube

Create subcube [Adventure Works] AS select [Date].[Calendar].[Calendar Year].&[2004] on 0 from [Adventure Works]
select ([Measures].[Internet Sales Amount]) on 0,
{[Date].[Calendar].[Month].Members} on 1
from [Adventure Works]
drop subcube [Adventure Works]


------------------------------Member function-----------------------------------------------

//Ancestor(Member,Level)
select Ancestor([Product].[Product Categories].[Subcategory].[Locks],[Product].[Product Categories].[Category]) on 0 from [adventure works]
select Ancestor([Product].[Product Categories].[Product].[Cable Lock],2) on 0 from [adventure works]

//Descendants
SELECT [Measures].[Internet Sales Amount] ON COLUMNS, {DESCENDANTS([Geography].[Geography].[Country].[Germany], [Geography].[Geography].[City])} ON ROWS
FROM [Adventure Works]

//Cousin( Member_Expression , Ancestor_Member_Expression )
select COUSIN([date].[calendar].[july 2003], [cy 2004]) on 0 from [adventure works]
select cousin([Date].[Calendar].[Calendar Quarter].&[2004]&[3],[Date].[Calendar].[Calendar Year].&[2002]) on 0 from [adventure works]
select cousin([Product].[Product Categories].[Product].[AWC Logo Cap],[Product].[Product Categories].[Category].[Bikes]) on 0 from [adventure
works]

//LastChild
select ([product].[product model lines].LastChild) on 0 from [adventure works]

//LastSibling
select ([product].[product model lines].[model].[cycling cap].LastSibling) on 0 from [adventure works]

//FirstChild
select {[Product].[Product Categories].FirstChild} on 0 from [adventure works]
select {[Product].[Product Categories].[Category].[Bikes].FirstChild} on 0 from [adventure works]

//FirstSibling
select {[Date].[Calendar].[Month].[May 2000].FirstSibling} on 0 from [adventure works]
select {[Date].[Calendar].[Calendar Quarter].&[2004]&[2].FirstSibling} on 0 from [adventure works]
select {[Product].[Product Categories].[Category].[Bikes].FirstSibling} on 0 from [adventure works]

//Parent
select ([Measures].[Internet Sales Amount]) on 0, [product].[product model lines].[model].[taillight].parent on 1  from [adventure works]
//PrevMember

select ([Measures].[Internet Sales Amount]) on 0, [customer].[customer geography].[country].[Germany].PrevMember on 1  from [adventure works]

//Set.Item(Ordinal position)
select {[Date].[Calendar].[Calendar Quarter].&[2001]&[4].Children.Item(0)} on 0 from [adventure works]
select CROSSJOIN([product].[product model lines].[product line].members,  [measures].[sales amount]).Item(2) on 0 from [adventure works]

//Member.Lag(No of positions)
select {[Product].[Product Categories].[Category].[Bikes].Lag(1)} on 0 from [adventure works]

//Member.Lead(No of positions)
select ([product].[product model lines].[product line].[mountain].Lead(1)) on 0 from [adventure works]

//Members
select ([Measures].[Internet Sales Amount]) on 0, {[customer].[customer geography].[country].members} on 1  from [adventure works]

//NextMember
select ([Measures].[Internet Sales Amount]) on 0, {[customer].[customer geography].[country].[Australia].NextMember} on 1  from [adventure works]


----------------------------------Time Functions----------------------------------------------

//YTD
select {YTD([Date].[Calendar].[Month].&[2002]&[8])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]

//QTD
select {QTD([Date].[Calendar].[Month].&[2002]&[8])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]

//MTD
select {MTD([Date].[Calendar].[Date].&[20020310])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]

//ParallelPeriod
select {YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],-1,[Date].[Calendar].[Month].&[2003]&[5]))} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]

//PeriodsToDate
select {PeriodsToDate([Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].&[2003]&[5])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]
select {PeriodsToDate([Date].[Calendar].[Calendar Quarter],[Date].[Calendar].[Month].&[2003]&[5])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]

//OpeningPeriod
select {OpeningPeriod ([Date].[Calendar].[Month],[Date].[Calendar].[Calendar Quarter].&[2002]&[2])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]

//ClosingPeriod
select {ClosingPeriod ([Date].[Calendar].[Month],[Date].[Calendar].[Calendar Quarter].&[2002]&[2])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]