MDX : MDX Tutorial, MDX Query, MDX Scrip, MDX Select Query.....
Hi friends,
I have seen developers searching for a last minute MDX query / script
to create a calculated member on the cube. Many start in an organized fashion
with a start from some simple MDX tutorial, and learning tuples,
namedsets, axis, MDX Select Query Syntax etc... But still after that,
many queries remain unsolved, and the real answer comes after a
hands-on experience of some real-time project when one really starts
applying functions, tweaking or nesting queries and parameters
Hope this helps all the last minute MDX requirements of those developers
who don't have time to spend in learning all the syntax, structure and
theory behing MDX syntax just to solve a little query or requirement
on a cube in production or last-minute patch.
MDX Various 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;
Hey Ram I need to add a query in mdx to calculate the count only from the most recent month..do u know hoe to do it?
ReplyDelete