Once you know basics we can have look at the basic functions and
properties of MDX.
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.
