dcsimg

MDX Essentials: Member Functions: More "Family" Functions - Page 4

April 21, 2003

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers