Working with the Member "Family" Functions

We discussed two general groups (from the perspective of the result datasets they return) to which the members of the "family" functions belong in our last lesson. One group works within a "vertical" scope, traveling up or down between hierarchical levels, as we will see in the Practice section for each respective function. The functions in this article (like the Ancestor(), .Children and .Parent functions of our last lesson) that act "vertically" include:

  • .FirstChild, and
  • .LastChild.

The second general group of "family" functions operates within a "horizontal" scope of the hierarchy involved. These functions travel within the same level of the hierarchy ("across" versus "up and down"), and, within the context of this article, include:

  • .FirstSibling, and
  • .LastSibling.

As we have already determined, the capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries. We'll take a look at our current selection of "family" functions individually, to obtain an appreciation for their capabilities, in the sections devoted to each that follow.

The .FirstChild Function


As we will observe, the .FirstChild function returns the first child of a specified member, using the syntax we describe below. An illustration of this concept might be helpful in getting an understanding of just what we mean by a "first child," as well as to provide a visual platform upon which to understand the other "family" functions that we will explore in this lesson.

I chose the Time dimension for this illustration because we all understand the time hierarchy, perhaps making a grasp of the core concepts less subject to being fettered by a need to study the hierarchical setup of a sample database. The illustration to which I refer appears below.

Illustration 1: Time Dimension Hierarchy, Showing Year 1997 with Expanded Q4



The .FirstChild function is appended to the right of the member, as shown in the following illustration:


A simple illustration of the .FirstChild function in action follows:

{[Time].[Year].[1997].FirstChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

The result dataset returned would appear as shown in Illustration 2 below:

Illustration 2: Example Result Dataset from Using the .FirstChild Function

A look at the Time hierarchy for the source member (1997) within the .FirstChild function above will make the illustration more meaningful. In Illustration 3 below, we see that Q1 is a member of the Quarters sublevel of the Year level, within the Time dimension. The order of the members within the database determines the status of Q1 as "first;" Q1 is the child of the Year level 1997, and as it is "first in line," it is returned.

Illustration 3 below displays the foregoing graphically.

Illustration 3: [1997].FirstChild is Q1

In the simple example above, we can easily see that the parent member is 1997.

We introduced the WHERE clause in our last lesson, in a discussion of the .Parent function. As we discussed at that point, the WHERE clause is optional; it specifies the Slicer Dimension, and limits the data returned to specific dimension(s) or member(s). The WHERE clause in our illustration above restricts the data extracted for the axis dimensions to a specific member of the Measures dimension, Warehouse Sales.