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

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.

-- 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])

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.

-- 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])

The Results pane appears as shown in Illustration 15.

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.

-- 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])

The Results pane appears as shown in Illustration 16 below.

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.