Skip to main content

NEXTMEMBER (MDX)

Returns the next member of the level to which the given member belongs. The details are different for time dimensions and data dimensions.

Returned Type

This function returns a member.

Syntax and Details

member_expression.NEXTMEMBER 

Where:

This function examines the members of the level to which the given member belongs, and returns the next member of that set (considering the default order of that set). For data dimensions, this function considers the parent level; it looks for the next member within the given parent member. (Note that the terms time dimension and data dimension refer specifically to the dimension type as defined in the cube. See Defining Models for InterSystems Business Intelligence.)

The NEXTMEMBER function is equivalent to LEAD(1).

Within any time dimension, this function is more useful for a timeline-based time level (such as Period, which groups records by year and month) than for a date-part-based time level (such as Month, which groups records only by month). See the examples. For a fuller discussion, see Introduction to Time Levels.

Example

The first examples use a time dimension. Consider the following query, shown for reference:

SELECT MEASURES.[%COUNT] ON 0, 
{birthd.1948,birthd.1949,birthd.1950,birthd.1951,birthd.1952} ON 1 
FROM patients
 
                             Patient Count
1 1948                                   10
2 1949                                    4
3 1950                                   12
4 1951                                    8
5 1952                                    6

The following query uses NEXTMEMBER:

SELECT MEASURES.[%COUNT] ON 0, birthd.1948.NEXTMEMBER ON 1 FROM patients
 
                             Patient Count
1949                                      4

For another example:

SELECT MEASURES.[%COUNT] ON 0, birthd.1949.NEXTMEMBER ON 1 FROM patients
 
                             Patient Count
1950                                     12

In this sample, the year level is the child of the decade level, which means that the members 1949 and 1950 belong to different parents. As you can see, the NEXTMEMBER function ignores the parent level when you use the function with a time dimension.

The second examples use a data dimension (the HomeD dimension). To see the hierarchy in this dimension, see the examples in the FIRSTCHILD function. The following query uses NEXTMEMBER with this dimension:

SELECT MEASURES.[%COUNT] ON 0, homed.city.Magnolia.NEXTMEMBER ON 1 FROM patients
 
                             Patient Count
Pine                                    114

Because this is a data dimension, this query retrieves the next member of the city level within the parent ZIP code. Within this ZIP code, Pine is the last city, so the following query returns no results:

SELECT MEASURES.[%COUNT] ON 0, homed.city.Pine.NEXTMEMBER ON 1 FROM patients
 
                             Patient Count
                                          *

For time level that is based on part of the date, this function returns null for the last member of the level. Consider the Month level, which groups records only by month. When we use this function with December, the engine returns null:

SELECT [BirthQD].[H1].[Month].[December].NEXTMEMBER ON 1 FROM patients
 
 
                                          *

For a fuller discussion, see Introduction to Time Levels.

See Also

FeedbackOpens in a new tab