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;