MDX Various Queries Cheat Sheet
Contributors: Vidas Matelis, Thomas Ivarsson, Deepak Puri
Last update: July 13, 2008
| 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 ROWSFROM [Adventure Works] WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ; | 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 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]) |
| 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] ASIIF( ([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 1FROM [Adventure Works] |
| 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 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 1FROM [Adventure Works] ; |
| How do you extract first tuple from the set? SELECT {{[Date].[Calendar].[Calendar Year].Members}.Item(0)} ON 0FROM [Adventure Works]; | How do you extract first member of the tuple? SELECT {([Date].[Calendar].[Calendar Year].&[2003], [Customer].[Customer Geography].[Country].&[Canada]).Item(0) } ON 0FROM [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 0FROM [Sales Summary] WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3]); | How can I get attribute key with MDX? WITH MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_KeySELECT {Measures.ProductKey} ON 0,[Product].[Product Categories].Members on 1 FROM [Adventure Works] |
| MDX query to get sales by product line for specific period plus number of months with sales WITH Member [Measures].[Months With Non Zero Sales] AS COUNT(CROSSJOIN([Measures].[Sales Amount], DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month])) , ExcludeEmpty )SELECT {[Measures].[Sales Amount], [Measures].[Months With Non Zero Sales]} ON 0, [Product].[Product Model Lines].[Product Line].Members on 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]); | How do I create a Rolling 12 Months Accumulated Sum that can show a trend without seasonal variations? WITH MEMBER [Measures].[InternetSalesAmtYTD] AS SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]), Format_String = "### ### ###" MEMBER [Measures].[InternetSalesAmtPPYTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember)), [Measures].[Internet Sales Amount]), Format_String = "### ### ###" MEMBER [Measures].[InternetSalesAmtPY] AS SUM(Ancestor(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),[Date].[Calendar].[Calendar Year]), [Measures].[Internet Sales Amount]),Format_String = "### ### ###" MEMBER [Measures].[InternetSalesAmtR12Acc] AS ([Measures].[InternetSalesAmtYTD]+[Measures].[InternetSalesAmtPY] )- [Measures].[InternetSalesAmtPPYTD] Select {[Measures].[Internet Sales Amount], Measures.[InternetSalesAmtYTD], [Measures].[InternetSalesAmtPPYTD],[Measures].[InternetSalesAmtR12Acc]} On 0, [Date].[Calendar].[Month].Members On 1 From [Adventure Works] Where ([Date].[Calendar Year].&[2004]); |
| How can I setup default dimension member in Calculation script? ALTER CUBE [Adventure Works] UPDATE DIMENSION [Geography].[Geography], DEFAULT_MEMBER = [Geography].[Geography].[Country].&[Canada]; | How to setup calculated measure as default measure for a cube? ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER=[Measures].[Profit]; |
| How can I create MDX calculated measure that instead of summing children amounts uses last child amount? WITH MEMBER [Measures].[Internet Order Count For End of Period Below] AS ([Date].[Calendar].CurrentMember.LastChild, [Measures].[Internet Order Count])SELECT [Measures].[Internet Order Count For End of Period Below] ON 0, [Date].[Calendar].Children ON 1FROM [Adventure Works]; | MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category WITH SET [FirstSales] AS FILTER(NONEMPTY( [Customer].[Customer Geography].[Customer].MEMBERS * [Date].[Date].[Date].MEMBERS , [Measures].[Internet Sales Amount]) AS MYSET, MYSET.CURRENTORDINAL = 1 or NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(MYSET.CURRENTORDINAL-2).ITEM(0)))Member [Measures].[CustomersW/FirstSales] as Count(NonEmpty([FirstSales], [Measures].[Internet Sales Amount])), FORMAT_STRING = '#,#' SELECT {[Measures].[Internet Sales Amount],[Measures].[CustomersW/FirstSales]} on 0, |
| How to calculate YTD monthly average and compare it over serveral years for the same selected month? WITH MEMBER Measures.MyYTD AS SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])MEMBER Measures.MyMonthCount AS SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year]))) MEMBER Measures.MyYTDAVG AS Measures.MyYTD / Measures.MyMonthCount SELECT {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0, [Date].[Calendar].[Month] On 1 FROM [Adventure Works] WHERE ([Date].[Month of Year].&[7]) |
Please contribute to this document. You can send e-mail with your query or just leave here comment. We will add query to the list.