# MDX Essentials: Member Functions: More "Family" Functions - Page 6

April 21, 2003

#### Practice

Let's confirm our understanding of the .FirstSibling function with a quick query. We will create a rudimentary query designed to return total Units Shipped for each member of the Beverages Product Department Level of the Product dimension for Q1 of 1997.

4. Type the following query into the Query pane:

```-- MDX06-5:  Tutorial Query No. 5
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling} ON COLUMNS,
{[Product].[Product Department].[Beverages].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
```

5. Select the F5 key (an alternative way to run the query).

The query executes, and the following result dataset appears in the Results pane.

Illustration 14: The Query Result Dataset

The query delivers the results that we requested; we see the Q1 (the first sibling of specified member Q3) totals for the enumerated children of the Beverages level, within the Product Family level of the Product hierarchy.

Let's write another query to return total Units Shipped for the Canned Foods Product Department, by month, for Q1, using what we have learned. Employing the .Children function again, we will enumerate the months contained within Q1, which we have determined, in the query immediately above, to be the first sibling of member Q3 (the first of the peer members in its horizontal level) of the Time dimension. To restate, our intent will be to create a column for each month in Q1 and a row for each child member of the Canned Foods Product Department level of the Product dimension.

6. Type the following query into the Query pane:

```-- MDX06-6:  Tutorial Query No. 6
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling.Children} ON COLUMNS,
{[Product].[Product Department].[Canned Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
```

7. Press F5 to run the query.

The Results pane appears as shown in Illustration 15.

Illustration 15: The Query Results

The query delivers the totals for the enumerated children of the Canned Foods product department by Q1 month (that is, 1, 2, and 3), the children of the Q3 member's first sibling Q1.

The results shown in Illustration 15 present an opportunity to examine another nuance we can use to make our presentation of data perhaps more user-friendly to its ultimate audience. Blank spaces often confuse information consumers, so let's remove them with a minor adjustment to Query No. 6 above.

8. Add the keywords NON EMPTY to Query No. 6 as shown below:

```-- MDX06-6M:  Tutorial Query No. 6 [MODIFIED TO REMOVE EMPTIES]
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling.Children} ON COLUMNS,
NON EMPTY {[Product].[Product Department].[Canned Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])
```

9. Press F5 to execute the newly modified query.

The Results pane appears as shown in Illustration 16 below.

Illustration 16: The Query Results

The query delivers the totals for the enumerated children of the Canned Foods product department by Q1 month, as before, but with the empty result intersects removed. Using the NON EMPTY query in this fashion allows us to suppress the empties; any tuples on the affected axis that are associated with empty intersects are efficiently removed before the results are displayed.