MDX Member Functions: The "Family" Functions - Page 4

The .Children Function

Much like the .Parent function, the .Children function works within a "vertical" scope, moving, as we noted in the introductory section of this lesson, down between hierarchical levels from the member to which the function is applied. As its name implies, the .Children function returns the children of the source member, using the syntax we describe below. Again resembling the .Parent function, the .Children function is especially useful in calculated members, among other scenarios, some of which we will explore later in the series.

Syntax

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

<member>.Children

A simple illustration of the .Children function, using our first example above and replacing the .Parent function with the .Children function, follows:

 SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

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

Referring again to the hierarchical structure within which our source member lies, we can see that the .Children function has moved in the opposite direction to the .Parent function above, as depicted in the illustration below:

Practice

Let's reinforce our understanding of how the .Children function operates by using it in a query. We will create a query very similar to the one we used above for the .Parent function to accomplish our ends.

We will return to the MDX Sample Application to craft our query. We'll begin with a basic query to return a set of members one level below Frozen Foods to determine the Units Shipped for 1997 and 1998, much as we did in our first practice exercise for the .Parent function above.

10.         Type the following query into the Query pane:

-- MDX05-3:  Tutorial Query No. 3
SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

11.         Click the Run button on the toolbar atop the Sample Application, to execute the query.

We see the result dataset below, which appears in the Results pane as soon as Analysis Services fills the cells specified by the query.

The query delivers the results that we requested: The children members for Booker appear, together with the Units Shipped, where applicable.

Now let's illustrate the operation of the .Children function once again, with another example.

12.         Type the following query into the Query pane:

-- MDX05-4:  Tutorial Query No. 4
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped], [Warehouse].[USA])

In addition to using the .Children function with another source member, we are also expanding the WHERE statement (see above for a discussion) to expand, in turn, the slicer dimension to specify not only the Units Shipped measure, but also the USA warehouses. The cube is therefore filtered, or "sliced" for the Units Shipped and USA members, within the context in which they are specified.

13.         Click Query on the top menu, and then select Run.

The Results pane appears as shown below.

The result dataset displays the children of the Frozen Foods level, enumerating them along with the Units Shipped data as appropriate.

We see that we have now obtained a set of members on a level below Frozen Foods for Units Shipped, because we affixed the .Children function to the Frozen Foods level. As is the case with the .Parent function, we will find the .Children function to be a powerful tool at a later juncture in our series, when we are using a relative member, such as .CurrentMember, in combination with the function.