The .LastChild
Function
Similar in operation to
the .FirstChild function above, only working from the "opposite end"
of the range of the children of a specified member, the .LastChild
function returns the last child of the specified member using the syntax
we describe below. To illustrate our meaning of the term "last child,"
we will return to the visual representation we constructed for .FirstChild
to view the impact of the .LastChild function, working within the Time
dimension once again for a readily understandable hierarchy.
Illustration 7 below depicts the relationships between the members and levels
under consideration:
Illustration 7: Revisiting the Time Dimension Hierarchy to
Display .LastChild
Syntax
The .LastChild function
is appended to the right of the member, as shown in the following
illustration:
<member>.LastChild
A simple illustration of the .LastChild
function in action follows:
SELECT
{[Time].[Year].[1997].LastChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
The result dataset returned would appear as shown in Illustration
8 below:
Illustration 8: Example Result Dataset from Using the .LastChild
Function
Practice
We will construct an expression that calls the .LastChild
function into action to reinforce our grasp of its operation. We will begin
with a simple illustration that involves the .LastChild function: a
basic query designed to extract total Warehouse Sales for each member of
the Product Family level of the Product dimension for Q1
of 1997.
1.
Type the
following query into the Query pane:
-- MDX06-3: Tutorial Query No. 3
SELECT
{[Time].[Year].[1997].LastChild} ON COLUMNS,
{[Product].[Product Family].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
2.
Click the Run
button to execute the query.
We see
the dataset pictured below appear in the Results pane.
Illustration 9: The Query Result Dataset
The
query delivers the results that we requested; we see the totals for the
enumerated members of the Product Family level of the Product
hierarchy.
As our example illustrates above, the last child of
level member 1997 is Q4.
Next, we will write a basic query to return total Warehouse
Sales for the Non-Consumables children of the Product Family
level, by month (and thus for each of the children of the member Q4).
Using the .Children function again, we will enumerate the months
contained within Q4, which we determined, in the query immediately above,
to be the last child of member 1997 of the Time dimension.
The intent will be to create a column for each month in Q4, and a row for
each child of the Non-Consumables level of the Product dimension.
Type the following query into the Query pane:
-- MDX06-4: Tutorial Query No. 4
SELECT
{[Time].[Year].[1997].LastChild.Children} ON COLUMNS,
{[Product].[Product Family].[Non-Consumable].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])
3.
Click Query
on the top menu, and then select Run.
The Results
pane appears as shown in Illustration 10.
Illustration 10: The Query Results
The
query delivers the totals for the enumerated children of the Non-Consumable
level of the Product dimension, as before, by Q4 month (that
is, 10, 11, and 12), the children of the Q4 level
of the Time dimension.
We see that we have obtained summaries at the level of the
children of the Non-Consumable level for the Warehouse Sales for
each month of the last / fourth quarter of 1997, because we affixed the .LastChild
function to the 1997 year level of the time dimension, and then suffixed
.LastChild with the .Children function.
» 
Comment and Contribute
