MDX Function, few more

After the post MDX introduction and MDX function, here in the same series I am going to explain few more function which will be very frequent.

PeriodsToDate

Is a time based function; it gives you till period values from the start of the period. A period can be any time based period like Quarter, Week, Month and Year.

Function takes a level and a member as input parameter. Level is the level you want to travel and member is till that member.

SELECT

      {[Measures].[Internet Order Count]} ON COLUMNS,

      PeriodsToDate([Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].&[2006]&[8]) ON ROWS

FROM      [Adventure Works]   

So here result will give you all the values starting from month 1 of year 2006 to the Month 8 of year 2006.

Interesting, isn’t it??

Well more understating will be when it is user dynamic.

To do so we have to create a calculated value. For crating a calculated value we use WITH MEMBER.

Following is Syntax of 

WITH MEMBER [Measures].[YourMeasureName]

AS

<>

 Than normal SELECT Statement which uses above calculation in any of their AXIS

Hear it how it comes together -

WITH MEMBER [Measures].[Internet Order Count YTD]

AS

aggregate(

PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].[Calendar].currentmember)

,[Measures].[Internet Order Count]

)    

SELECT

      {[Measures].[Internet Order Count],[Measures].[Internet Order Count YTD]} ON COLUMNS,

      [Date].[Calendar].[Month] ON ROWS

FROM [Adventure Works]

WHERE {[Date].[Calendar Year].&[2007]}

Here we have simple used PeriodsToDate With in a calculation.

Here instead of a member we have used currentmember. This current member will help us calculate will you for every rows means for every member of Month attribute.

When you run this query result will give you cumulative of internet Order counts till year end.

Another important Time period based function is

PARALLELPERIOD

Return a data for  a period in the same relative position as a specified member, this period can be before or upcoming based on the input given.

SELECT

    PARALLELPERIOD([Date].[Calendar].[Calendar Year],1, [Date].[Calendar].[Month].&[2010]&[9]) ON COLUMNS

     

FROM [Adventure Works]

We can also have dynamic query here as well, see following

WITH MEMBER [Measures].[Internet Order Count PP]

AS

aggregate(

PARALLELPERIOD([Date].[Calendar].[Calendar Year],1, [Date].[Calendar].currentmember)

,[Measures].[Internet Order Count]

)    

SELECT

      {[Measures].[Internet Order Count],[Measures].[Internet Order Count PP]} ON COLUMNS,

      [Date].[Calendar].[Month] ON ROWS

FROM [Adventure Works]

WHERE {[Date].[Calendar Year].&[2010]}

Hope this will help you understand basic of MDX and also some of the Important MDX functions.