DeepSee MDX Reference
LEAD
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Given a level member and a nonnegative integer, this function counts forward in the level and returns a later member. The details are different for time dimensions and data dimensions.
Returned Type
This function returns a member.
Syntax and Details
member_expression.LEAD(optional_integer_expression) 
Where:
This function examines the members of the level to which the given member belongs, counts forward from the current member (using integer_expression), and returns the member at that position. For time dimensions, this function ignores any parent level. For data dimensions, this function considers the parent level; it counts forward from the current 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 DeepSee Models.)
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). If the level is based on a date part, this function returns null when it refers to a level beyond the end of the set; see an example of a similar scenario in NEXTMEMBER. For a fuller discussion, see Introduction to Time Levels,” in Using MDX with DeepSee.
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 LEAD:
SELECT MEASURES.[%COUNT] ON 0, birthd.1948.LEAD(1) ON 1 FROM patients
 
                             Patient Count
1949                                      4
For another example:
SELECT MEASURES.[%COUNT] ON 0, birthd.1948.LEAD(3) ON 1 FROM patients
 
                             Patient Count
1951                                      8
In this sample, the year level is the child of the decade level, which means that the members 1948 and 1951 belong to different parents. As you can see, the LEAD 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 LEAD with this dimension:
SELECT MEASURES.[%COUNT] ON 0, homed.city.Magnolia.LEAD(1) 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.LEAD(1) ON 1 FROM patients
 
                             Patient Count
                                          *
See Also