MDX Functions

Once you know basics we can have look at the basic functions and properties of MDX.

I hope you have read the previous blog MDX  Introduction.  

In the last post I have shown you a very basic MDX, it just show one member, if you want to see all the members than

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].ALLMEMBERS on 1

FROM [Adventure Works]

Using . ALLMEMBERS to a dimension attribute will give you all the members of that attributes.

Just like this we have some more properties and function

PREVMEMBER

 Gives you previous member of a Dimension Member.  

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].&[2009].PREVMEMBER on 1

FROM [Adventure Works]

So you will get data across Year 2008.

RANGE

Range is not a function but it’s a way of writing MDX, by which you can see data for a range of dimension member.  

SELECT {[Measures].[Internet Order Count]} on 0,

{[Date].[Calendar Year].&[2005]:[Date].[Calendar Year].&[2007]} on 1

FROM [Adventure Works]

FILTER

If you want to filter data on Axis based on a condtion you can use filter function.  Like following

SELECT [Measures].[Internet Sales Amount] ON 0,

FILTER

(

[Date].[Date].[Date].MEMBERS

, [Measures].[Internet Sales Amount]<10000 o:p="o:p">

) ON 1

FROM [Adventure Works]

FILTER takes a set and a condition as input parameter.

In above example we are showing all the dates for which we have Internet sales amount more than 10000.

Instead of a measure you can also have Dimensions as Filter condition.

EXCEPT

Same as FILTER there is Except

--Except

SELECT [Measures].[Order Quantity]

   ON COLUMNS,

   Except

      ([Product].[Product Categories].[All].Children ,

         {[Product].[Product Categories].[Components]}

      ) ON ROWS

FROM

   [Adventure Works]

It gives you result except the condition, so its other way around of Filter.

So it takes a set and a condition as input parameter, but here result will exclude the condition and gives you result.

This are few function I have explained you above, in next post will see more of those.