This article describes some of the frequently written MDX statements. The following MDX statement can be run in the sample Library for SSAS: Adventure works.
Example Model
The following hierarchy are used in the MDX:
percentage
1) A percentage of the total for a child. For example, the sales for each product represent a percentage of all product sales. With MEMBER [Measures]. [Sale Amount Ratio] As
' [Measures]. [Internet Sales amount]/([measures].[ Internet Sales Amount], [Product]. [Product Categories]. [All]) ' , format_string = ' 0% '
SELECT
{[Measures]. [Internet Sales Amount], [Measures]. [Sale Amount Ratio]} On 0,
NON EMPTY [Product]. [Product Categories]. [Product Name]. Members on 1
From [Adventure Works]
2) A child is the percentage of its parent. For example, the sales of each product account for the percentage of subcategory sales that they belong to.
With MEMBER [Measures]. [Sale Amount Ratio] As
' [Measures]. [Internet Sales amount]/
([Measures]. [Internet Sales Amount], [Product]. [Product Categories]. Currentmember.parent) '
, format_string = ' 0% '
SELECT
{[Measures]. [Internet Sales Amount], [Measures]. [Sale Amount Ratio]} On 0,
NON EMPTY CROSSJOIN ([product].[ Subcategory]. [Subcategory]. Members,
[Product]. [Product Categories]. [Product Name]. Members) on 1
From [Adventure Works]
3) A child is a percentage of its ancestors. For example, the sales of each product represent the percentage of the category sales to which they belong.
With MEMBER [Measures]. [Sale Amount Ratio] As
' [Measures]. [Internet Sales amount]/
([Measures]. [Internet Sales Amount],
ANCESTOR ([product].[ Product Categories]. CurrentMember, [Product]. [Product Categories]. [Category])) '
, format_string = ' 0% '
SELECT
{[Measures]. [Internet Sales Amount], [Measures]. [Sale Amount Ratio]} On 0,
NON EMPTY CROSSJOIN ([product].[ Category]. [Category]. Members, [Product]. [Product Categories]. [Product Name]. Members) on 1
From [Adventure Works]
Allocation, allocation quantity
1) Assign quantity according to a measure value. For example, cost is apportioned according to how much each product represents the total sales.
With MEMBER [Measures]. [Product Cost] As
' ([Measures]. [Internet total Product cost], [Product]. [Product Categories]. [All]) *
[Measures]. [Internet Sales amount]/
([Measures]. [Internet Sales Amount], [Product]. [Product Categories]. [All]) '
, format_string = ' 0.00 '
SELECT
{[Measures]. [Internet Sales Amount], [Measures]. [Product Cost]} On 0,
NON EMPTY [Product]. [Product Categories]. [Product Name]. Members on 1
From [Adventure Works]
2) Allocate quantity according to a hierarchy. For example, when calculating the cost of each category in product hierarchy, you can assign it according to how many products are in each category.
With MEMBER [Measures]. [Product Cost] As
' ([Measures]. [Internet total Product cost], [Product]. [Product Categories]. [All]) /
Count (
Descendants (
[Product]. [Product Categories]. CurrentMember,
[Product]. [Product Categories]. [Product Name],
Self
),
Includeempty
)‘
, format_string = ' 0.00 '
SELECT
{[Measures]. [Internet Sales Amount], [Measures]. [Product Cost]} On 0,
NON EMPTY [Product]. [Product Categories]. [Category]. Members on 1
From [Adventure Works]
Average
1) Simple average. For example: Calculate the average amount of sales per day for one months.
With MEMBER Measures. [Avg Gross Profit Margin] As
[Measures]. [Internet Sales amount]/
COUNT (Descendants ([Ship date].[ Fiscal]. CurrentMember, [Ship Date]. [Fiscal]. [Date]), Includeempty)
SELECT
{[Measures]. [Internet Sales Amount], Measures. [Avg Gross Profit Margin]} On COLUMNS,
[Ship Date]. [Fiscal]. [Month]. Members on ROWS
From [Adventure Works]
2) Weighted average value. Did not think of good examples.
Time-based computing
1) YoY and chain. For example: This year's monthly sales and the same period last year compared to the change
This is to add that, in the year-over MDX, the use of cousin or parallelperiod can be, but the use of parallelperiod better.
2) accumulate to the current statistics. For example: Get the cumulative sales for every one months of the year.
With MEMBER Measures. [Additive Internet Sales Amount] As
SUM (
PeriodsToDate ([Ship date].[ Fiscal]. [Fiscal year],[ship Date]. [Fiscal]. CurrentMember),
[Measures]. [Internet Sales Amount]
)
SELECT
{[Measures]. [Internet Sales Amount], Measures. [Additive Internet Sales Amount]} On COLUMNS,
[Ship Date]. [Fiscal]. [Month]. Members on ROWS
From [Adventure Works]
3) Moving average. For example: Calculates the total of average sales for the last three months of a category.
With MEMBER Measures. [Average Internet Sales Amount] As
AVG (LastPeriods (3, [date].[ Calendar]. CurrentMember),
[Measures]. [Internet Sales Amount])
SELECT
{[Measures]. [Internet Sales Amount], Measures. [Average Internet Sales Amount]} On COLUMNS,
NON EMPTY ([product].[ Product Categories]. [Category]. Members,
Descendants ([date].[ Calendar]. [Calendar year].&[2002], [Date]. [Calendar]. [Month], self)
) on ROWS
From [Adventure Works]
MDX percent calculation method
