MDX NextMember Function

In Multidimensional Expression, NextMember function will return the same level member at the Next position.
MDX Nextmember Function Syntax

The basic syntax of the MDX Nextmember is:

Member_Expression.NEXTMEMBER

Member_Expression: Any Multidimensional Expression that returns valid Member.


NOTE: Nextmember Function is used to move forward between Members at same level. For example, If we mentioned City member as Member_Expression then Nextmember function will move forward between remaining Cities, it will not go Level Up (State – Province) or Level down (Postal Code).


In this article we will show you, How to write Nextmember function with examples. For this, we are going to use below shown data.

MDX Nextmember Function Example

In this example we will show you, How to write the NextMember function in Multidimensional Expression query. The following query will return the Reseller Sales amount of the Calender Year 2012 because [CY 2011].NEXTMEMBER will look for year after 2011, which is 2012.


CODE


SELECT [Date].[Calendar].[Calendar Year].[CY 2011].NextMember ON COLUMNS,
[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works] 

OUTPUT

MDX Nextmember Function Example 2

In this example we will show you, How to write the multiple NextMember function in single Multidimensional Expression query. The following query will return the Reseller Sales amount of the Calender Year 2013.


CODE

SELECT [Date].[Calendar].[Calendar Year].[CY 2011].NEXTMEMBER.NEXTMEMBER ON 0
,[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works] 

OUTPUT





Analysis:

First, [CY 2011].NEXTMEMBER will look for year after 2011, which is 2012


[Date].[Calendar].[Calendar Year].[CY 2011].NextMember

Second, [CY 2011].Nextmember.Nextmember will look for year after 2012, which is 2013

[Date].[Calendar].[Calendar Year].[CY 2011].NextMember.NextMember


Finding Range Using MDX Nextmember Function


In this example we will show you, How to find the Range using Nextmember function. The following query will return the Reseller Sales amount from Calender Year 2011 to 2013 because we used the range symbol (:) between


[CY 2011] and [CY 2011].Nextmember.Nextmember.


CODE


SELECT
[Date].[Calendar].[Calendar Year].[CY 2011]:
[Date].[Calendar].[Calendar Year].[CY 2011].NextMember.NextMember ON 0
,[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works]

OUTPUT



MDX Nextmember Function Alternative 1


In this example, We are going to use Lead Function with Positive integer value to achieve above results. Please refer MDX Lead Function article to understand the function


CODE


SELECT
[Date].[Calendar].[Calendar Year].[CY 2011]:
[Date].[Calendar].[Calendar Year].[CY 2011].LEAD(2) ON 0
,[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Nextmember Function Alternative 2

In this example we will show you, We are going to use Lag Function with Negative integer value. Please refer MDX Lag Function article to understand the function


CODE


SELECT
[Date].[Calendar].[Calendar Year].[CY 2011]:
[Date].[Calendar].[Calendar Year].[CY 2011].LAG(-2) ON 0
,[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works] 

OUTPUT