Saturday 3 November 2012

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. 

No comments:

Post a Comment