Home > SQL Analysis Services > MDX Functions for Navigating Time

MDX Functions for Navigating Time

Functions for Navigating Hierarchies:

  • PrevMember
  • Children
  • Parent

Functions for Navigating Time

  • Lag
    • Lag(1) = PrevMember
    • Lag(-1) = NextMember
    • Lag(n) = Lead(-n)
    • SELECT [Date].[Fiscal].[Month].[February 2002].Lag(2) ON 0 FROM [Adventure Works]
      • December 2001
    • SELECT [Date].[Fiscal].[Month].[February 2002].Lag(-1) ON 0 FROM [Adventure Works]
      • March 2002
  • ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] )
    • Level_Expression – A valid Multidimensional Expressions (MDX) expression that returns a level.
      • Default value = parent of the specified member
    • Index – A valid numeric expression that specifies the number of parallel periods to lag.
      • Defuault value  = 1
    • Member_Expression – A valid Multidimensional Expressions (MDX) expression that returns a member.
      • Default value = Level_Expression.Hierarchy.CurrentMember
    • For Example:

    SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter]

       , 3

       , [Date].[Calendar].[Month].[October 2003])   ON 0

    FROM [Adventure Works]

    Result = January, 2003

     

    SELECT ParallelPeriod ([Date].[Calendar].[Calendar Semester]

       , 3

       , [Date].[Calendar].[Month].[October 2003])   ON 0

       FROM [Adventure Works]

    Result = April, 2002

    Advertisements
    Categories: SQL Analysis Services
    1. No comments yet.
    1. No trackbacks yet.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: