Multi Dimensional Expression Basic (MDX)

This post is copy from http://gumper.com, a unique website coz it’s only have one item….

The basic script of Multi Dimensional Expression (MDX). It’s very usefull for developer to build some scenarios in a CUBE.

thank you,

hope it’s ok i copied all the page.

MDX Expressions
Function	Requires	Returns
CurrentMember	Dimension	Member
PrevMember	Member	Member
NextMember	Member	Member
Lag	Numeric Expression	Member
Lead	Numeric Expression	Member
ParallelPeriod	Level, numeric expression, member	Member
PeriodsToDate	Level, member	Set
YTD	Member	Set
MTD	Member	Set
Sum	Set, numeric expression	Number

Navigating the Hierarchy
Function	Requires	Returns
Parent	Member	Member
Children	Member	Set
FirstChild	Member	Member
LastChild	Member	Member
Descendants	Member, level or member, distance	Set
Ancestor	Member, level or member, distance	Member
Siblings	Member	Set
FirstSibling	Member	Member
LastSibling	Member	Member
Cousin	Member, ancestor member	Member

Data Analysis
Function	Requires	Returns
Sum	Set, numeric expression	Number
Count	Set	Integer
Avg	Set, numeric expression	Number
Tail	Set, count	Set
Item	Set, index number	Member
OpeningPeriod	Level, member	Member
ClosingPeriod	Level, member	Member
Max	Set, numeric expression	Number
Min	Set, numeric expression	Number

Moving Averages
Function	Requires	Returns
Level	Member	Level
Members	Level or dimension	Setr
Iif	Condition, what to do if true, what to do if false	Number or string

Filters
Function	Requires	Returns
Filter	Set, condition	Set
IsEmpty	Expression	Boolean

Member Properties
Function	Requires	Returns
Properties	Member name	String
Val (VBA Function)	String	Numeric value

Aggregates
Aggregate function	Returned value
Sum	The sum of the input values
Min	The lowest of the input values
Max	The highest of the input values
Count	The number of input values
Distinct Count	The number of unique input values

Example MDX Calculations

Sales Growth

([Time].CurrentMember, [Measures].[Sales])
- ([Time].CurrentMember.PrevMember, [Measures].[Sales])

1 2	([Time].CurrentMember, [Measures].[Sales]) - ([Time].CurrentMember.PrevMember, [Measures].[Sales])

PP Growth

([Time].CurrentMember, [Measures].[Sales])
- (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])

1 2	([Time].CurrentMember, [Measures].[Sales]) - (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])

YTD Sales

sum(ytd([Time].CurrentMember), [Measures].[Sales])

1	sum(ytd([Time].CurrentMember), [Measures].[Sales])

YTD Sales Using PeriodsToDate

sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])

1	sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])

Product Percentage

([Product].CurrentMember, [Measures].[Unit Sales])
/ ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100

1 2	([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100

Product Total Percentage

([Product].CurrentMember, [Measures].[Unit Sales])
/ ([Product].[All Products], [Measures].[Unit Sales]) * 100

1 2	([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].[All Products], [Measures].[Unit Sales]) * 100

Average Stock – Brute Force

Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
/ Count(Descendants([Time].CurrentMember, [Month]))

1 2	Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity]) / Count(Descendants([Time].CurrentMember, [Month]))

Average Stock – Elegant

Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

1	Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Closing Period – Brute Force

(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

1	(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

Closing Period – Elegant

(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])

1	(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])

Max for Period

Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

1	Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Min for Period

Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

1	Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Moving Average

Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])

1	Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])

Products Down

Count(Filter(Descendants([Product].CurrentMember, [Product Name])
            , ([Time].CurrentMember, [Measures].[Unit Sales])
              &lt; ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales])))
/ 
Count(Descendants([Product].CurrentMember, [Product Name]))

Count(Filter(Descendants([Product].CurrentMember, [Product Name])

, ([Time].CurrentMember, [Measures].[Unit Sales])

< ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales])))

Count(Descendants([Product].CurrentMember, [Product Name]))

Dynamic Custom Default Member

Tail(Filter([Time].[Month].Members
     , Not IsEmpty([Time].CurrentMember)), 1).Item(0)

1 2	Tail(Filter([Time].[Month].Members , Not IsEmpty([Time].CurrentMember)), 1).Item(0)

Properties Query

Filter([Store].[Store Name].Members
       , Val([Store].CurrentMember.Properties(&quot;Store Sqft&quot;)) &lt; 21000)

1 2	Filter([Store].[Store Name].Members , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)

Products Down Query

select   
    {{{[Measures].[Products Down]}   
      * {[Customers].DefaultMember}   
      * {[Product].DefaultMember}}} on columns  
    , {[Time].&amp;[1998].&amp;[Q1].&amp;[1]  
       , [Time].&amp;[1998].&amp;[Q1].&amp;[2]  
       , [Time].&amp;[1998].&amp;[Q1].&amp;[3]  
       , [Time].&amp;[1998].&amp;[Q2].&amp;[4]  
       , [Time].&amp;[1998].&amp;[Q2].&amp;[5]  
       , [Time].&amp;[1998].&amp;[Q2].&amp;[6]  
       , [Time].&amp;[1998].&amp;[Q3].&amp;[7]  
       , [Time].&amp;[1998].&amp;[Q3].&amp;[8]  
       , [Time].&amp;[1998].&amp;[Q3].&amp;[9]  
       , [Time].&amp;[1998].&amp;[Q4].&amp;[10]  
       , [Time].&amp;[1998].&amp;[Q4].&amp;[11]} on rows  
from  
    [Sales_MDX2]

select

{{{[Measures].[Products Down]}

* {[Customers].DefaultMember}

* {[Product].DefaultMember}}} on columns

, {[Time].&[1998].&[Q1].&[1]

, [Time].&[1998].&[Q1].&[2]

, [Time].&[1998].&[Q1].&[3]

, [Time].&[1998].&[Q2].&[4]

, [Time].&[1998].&[Q2].&[5]

, [Time].&[1998].&[Q2].&[6]

, [Time].&[1998].&[Q3].&[7]

, [Time].&[1998].&[Q3].&[8]

, [Time].&[1998].&[Q3].&[9]

, [Time].&[1998].&[Q4].&[10]

, [Time].&[1998].&[Q4].&[11]} on rows

from

[Sales_MDX2]

MDX Query

select
    {[Measures].[Unit Sales]
     , [Measures].[MA]} on columns
    , {Descendants([Time].[Year].&amp;[1997], [Month])} on rows
from
    [Sales_MDX2]

select

{[Measures].[Unit Sales]

, [Measures].[MA]} on columns

, {Descendants([Time].[Year].&[1997], [Month])} on rows

from

[Sales_MDX2]

Related posts: