Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 22, 2003

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

By William Pearson

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


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


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

{[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


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
{[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
{[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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM