Various MDX Queries

How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT( NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10 , ( [Measures].[Internet Sales Amount] ) ) ON ROWS
FROM [Adventure Works] WHERE ( [Date].[Calendar].[Calendar Year].&[2003] )


How in MDX query can I get top 3 sales years based on order quantity?

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, TopCount([Date].[Calendar].[Calendar Year].Members
,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works]


How do you extract first tuple from the set?

SELECT {{[Date].[Calendar].[Calendar Year].Members}.Item(0)} ON 0
FROM [Adventure Works]


How do you get Last month in the time dimension

SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0
FROM [Sales Summary]
WHERE ([Measures].[Sales Amount])


Need a MDX query that returns list of months from start of year up to specified month.

SELECT YTD([Date].[Calendar].[Month].&[2003]&[8]) ON 0
FROM [Sales Summary];


I Need an MDX statement to show the first day of the last month in the cube

SELECT OpeningPeriod([Date].[Calendar].[Date]
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) ON 0
FROM [Sales Summary]

How in the report can I order date dimension members in descending order?

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, ORDER(Tail([Date].[Calendar].[Calendar Year].Members, 3)
, [Date].[Calendar].CurrentMember.Member_Key, DESC ) ON 1
FROM [Adventure Works]


I Need an MDX statement to get the last Month loaded into a cube SELECT ClosingPeriod([Date].[Calendar].[Month],

SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0
FROM [Sales Summary]


I Need an MDX statement to get the first month of the last year loaded into a cube

SELECT OpeningPeriod([Date].[Calendar].[Month]
, ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember) ) ON 0
FROM [Sales Summary]


How do you write MDX query that uses execution date/time as a parameter?

SELECT {[Measures].[Internet Order Count]} ON 0
, {StrToMember("[Date].[Date].[" + Format(now(), "MMMM dd, yyyy") + "]") } ON 1
FROM [Direct Sales]


Need MDX Query to get latest months and previous years same months data

SELECT {ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember)
, ParallelPeriod([Date].[Calendar].[Calendar Year] , 1
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) } ON 0
FROM [Sales Summary]


I need an MDX query to show year level data for all years except the last one, and month level data for the last year.

SELECT {NULL: ClosingPeriod([Date].[Calendar].[Calendar Year]
, [Date].[Calendar].DefaultMember).PrevMember
, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year]
, [Date].[Calendar].DefaultMember) , [Date].[Calendar].[Month]) } ON 0
FROM [Sales Summary]


How do you extract first member of the tuple?

SELECT {([Date].[Calendar].[Calendar Year].&[2003]
, [Customer].[Customer Geography].[Country].&[Canada]).Item(0) } ON 0
FROM [Adventure Works]


How can I compare members from different dimensions that have the same key values?

SELECT {[Measures].[Internet Order Count]} ON 0
, FILTER( NonEmptyCrossJoin( [Ship Date].[Date].Children, [Delivery Date].[Date].Children )
, [Ship Date].[Date].CurrentMember.Properties('Key') = [Delivery Date].[Date].Properties('Key') ) ON 1
FROM [Adventure Works]


How can I get attribute key with MDX?

WITH MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_Key
SELECT {Measures.ProductKey} ON 0, [Product].[Product Categories].Members on 1
FROM [Adventure Works]


How do you compare dimension level name to specific value?

WITH MEMBER [Measures].[TimeName] AS IIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr')
SELECT [Measures].[TimeName] ON 0
FROM [Sales Summary]
WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])


How do I calculate sales for 12 Month to date in MDX?

WITH MEMBER [Measures].[Last 12 Mth Order Count] AS
SUM( ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12) : ClosingPeriod([Date].[Calendar].[Month]
, [Date].[Calendar].[All Periods])
, [Measures].[Order Count])
SELECT [Measures].[Last 12 Mth Order Count] ON 0
FROM [Adventure Works]


How do I group dimension members dynamically in MDX?

WITH MEMBER [Product].[Category].[Case Result 1] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") < "3"))'

MEMBER [Product].[Category].[Case Result 2] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") = "3"))'

MEMBER [Product].[Category].[Case Result 3] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") > "3"))'

SELECT NON EMPTY {[Measures].[Order Count] } ON COLUMNS
, {[Product].[Category].[Case Result 1],[Product].[Category].[Case Result 2],[Product].[Category].[Case Result 3] } ON ROWS
FROM [Adventure Works]


How do you write MDX query that returns measure ratio to parent value?

WITH MEMBER [Measures].[Order Count Ratio To Parent] AS IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, NULL , [Measures].[Order Count] / ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) )
, FORMAT_STRING = "Percent"

SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods] } ON 1
FROM [Adventure Works]


MDX query to get sales by product line for specific period plus number of months with non empty sales.
WITH Member [Measures].[Months With Above Zero Sales] AS COUNT( DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month]) * [Measures].[Sales Amount] , ExcludeEmpty )

SELECT {[Measures].[Sales Amount]
, [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works] WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])


How can I get Last (Previous) Year to Date (YTD) values?
WITH MEMBER [Measures].[Current YTD] AS SUM(YTD([Date].[Calendar].CurrentMember)
, [Measures].[Internet Order Quantity])
MEMBER [Measures].[Last YTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year] , 1
, [Date].[Calendar].CurrentMember)) ,
[Measures].[Internet Order Quantity] )

SELECT {[Measures].[Current YTD] , [Measures].[Last YTD] } ON 0
FROM [Adventure Works] WHERE ([Date].[Calendar].[Date].[March 22, 2004])

How do you calculate monthly average of a year?

WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month]), [Measures].[Internet Order Count] )

SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])


How do you calculate monthly average of a year including empty months?

WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, CoalesceEmpty([Measures].[Internet Order Count], 0) ) 

SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])


Scope MDX


Can you use the Filter function in a SCOPE statement?

SCOPE ( Filter([Date].[Calendar].[Month].MEMBERS, [Date].[Calendar].Properties("Month of Year") = "January") );
Measures.[Amount] = 10;
END SCOPE;

Can you create a SCOPE based on the Member property?

SCOPE ( Filter([Date].[Calendar].[Month].MEMBERS , [Date].[Calendar].Properties("Month of Year") = "January") );
Measures.[Amount] = 10;
END SCOPE;

How do you specify SCOPE for a Dimension to include all members before a certain member

SCOPE(NULL:[Date].[Calendar].[Calendar Quarter].&[2003]&[3]); This = 100;
END SCOPE;

How do you specify SCOPE for a Dimension to include all members after a certain member

SCOPE([Date].[Calendar].[Calendar Quarter].&[2003]&[3]: NULL); This = 100;
END SCOPE;

How Can I define SCOPE in MDX for all measures in one measure group?

SCOPE(MeasureGroupMeasures("Sales Orders")); This = ...;
END SCOPE;


How Can I define SCOPE in MDX for all but one measures in one measure group?

SCOPE(MeasureGroupMeasures("Internet Sales") - {[Measures].[Internet Tax Amount]});
This = ...;
END SCOPE;


How Can I define SCOPE in MDX for all measures in multiple measure groups?

SCOPE({MeasureGroupMeasures("Internet Sales"), MeasureGroupMeasures("Internet Orders")
, MeasureGroupMeasures("Internet Customers") }); This = ...;
END SCOPE;