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
Discussion:
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
Syntax
The .FirstChild function
is appended to the right of the member, as shown in the following
illustration:
<member>.FirstChild
A simple illustration of the .FirstChild
function in action follows:
SELECT
{[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.
» 
Comment and Contribute
