|
Contributors: Vidas Matelis, Thomas Ivarsson, Deepak Puri
Last update: July 13, 2008 MDX various queries examples 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,
[Product].[Product Categories].[Category] on 1
FROM [Adventure Works]
WHERE ({[Date].[Calendar].[Calendar Year].&[2002],
[Date].[Calendar].[Calendar Year].&[2003]},
[Customer].[Customer Geography].[City].&[Calgary]&[AB]); | | 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]) | |
PDF download here. 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.
|
For example...
Toyota cars are bein sold most In Calgary.
And at second position it is being sold by Toronto etc.
I tried but I couldn't get exact query..mine is 90% right can you give me query for above report subject.
Thanks.