------------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]
