MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions

Monday Nov 6th 2006 by William Pearson
Share:

Use IsLeaf() to support conditional logic within filter expressions. BI Architect Bill Pearson looks beyond employing IsLeaf() in calculations, and provides hand-on practice in its use within the MDX Filter() function.

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials. Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

In MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations, another article within my MDX Essentials series, we introduced the IsLeaf() operator, from the perspective of its use within a calculation. We discussed the straightforward purpose of the operator, to ascertain whether a member is a leaf-level member of a dimension; the manner in which IsLeaf() manages to do this; and ways we can leverage the operator to support effective conditional logic to meet various business needs within our own environments. For a review of this introductory discussion, see MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations.

In this article, we will examine IsLeaf(), once again as a conditional logic modifier, but within the context of a filter. Combining IsLeaf() with the MDX Filter() function is another way we commonly see it in action in the business environment, and our exposure to the practical aspects of its employment in this way will serve to round out our overall awareness of the potential of IsLeaf(). From the perspective of its use in combination with Filter(), this article will include:

• A review of the general syntax surrounding the operator;
• Illustrative examples of uses of the operator in practice exercises;
• A brief discussion of the MDX results obtained within each of the practice examples.

The IsLeaf() Operator

Introduction

As we related in MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations, the Books Online tell us that the IsLeaf() operator "returns whether a specified member is a leaf member." A Boolean value of "True" is returned if the member expression to which it is applied is a leaf member; otherwise IsLeaf() returns "False." IsLeaf() is often employed in conjunction with the IIF() function (as we confirmed via our hands-on practice session in the article), to conditionally return data, such as a member or members (for example, children of a selected member, if they exist, or the selected member if it has no children), or values. As we shall see in the practice examples that come later, IsLeaf() can also be employed in conjunction with the Filter() function, where it serves up the same "True" value if the member expression to which it is applied represents a leaf member, and "False" if not.

We will examine in detail the syntax for the IsLeaf() operator after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support a hypothetical business need that illustrates a use for the operator. This will afford us an opportunity to explore some of the basic options that IsLeaf() can offer the knowledgeable user. Hands-on practice with IsLeaf(), where we will create queries that employ the function, will help us to activate what we have learned in the Discussion and Syntax sections.

NOTE: For more detail surrounding the Filter() function, see Basic Set Functions: The Filter() Function, a member of my Database Journal MDX Essentials series.

Discussion

To restate our initial description of its operation, IsLeaf() returns "True" if a specified member expression represents a leaf (or "level 0") member; otherwise, the operator returns "False." We can use IsLeaf() to apply conditional logic based upon the location or existence of members. As we have noted to be the case with most MDX functions and operators, pairing the IsLeaf() operator with other MDX operators and functions can help us to leverage its power even further. While we exploited a combination with the IIF() function in MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations, we will get some hands-on exposure to the use of IsLeaf() with the MDX Filter() function in the practice section of this article.

Let's look at syntax specifics to further clarify the operation of IsLeaf().

Syntax

To review the syntax involved with employing the IsLeaf() operator, we specify the member expression in parentheses to the immediate right of the operator. The operator takes the member expression which is appended to it as its argument, and returns True if the member denoted by the member expression is a leaf member (or, in other words, if the member resides at the lowest (0) level of the dimension). If the member specified by the member expression is not a leaf member (or if the member resides at a dimensional level higher than the "zero," or "bottom," level), a False is returned.

The general syntax is shown in the following string:

`IsLeaf(Member Expression)`

Employing IsLeaf() is, in itself, straightforward. As we have noted, we simply place the member expression under consideration in the parentheses to the right of the operator. As an example, within a query executed against the sample Adventure Works cube, for a dimension named Sales Territory (with a hierarchy of the same name), the following pseudo-expression:

`IsLeaf([Sales Territory].[Sales Territory].CURRENTMEMBER) `

Returns True if the current member of the Sales Territory dimension / Sales Territory hierarchy is at level 0.

NOTE: For information on several of the "relative" functions, of which .CURRENTMEMBER is an example, see my article MDX Member Functions: "Relative" Member Functions, within the Database Journal MDX Essentials series.

We will practice some uses of the IsLeaf() operator, focusing on its combination with the Filter() function, in the section that follows,

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will use the IsLeaf() operator in a couple of queries that illustrate its operation, this time focusing on combinations with the MDX Filter() function. We will do so in simple scenarios that place IsLeaf() within the context of meeting basic requirements similar to those we might encounter within our respective daily environments. The intent is to demonstrate the use of the operator in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. If you do not know how to access the SQL Server Management Studio in preparation for using it to query an Analysis Services cube (we will be using the sample Adventure Works cube in the Adventure Works DW Analysis Services database), please perform the steps of the following procedure, located in the References section of my articles index:

Prepare MSSQL Server Management Studio to Query Analysis Services

Procedure: Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request for assistance from representatives of our client, the Adventure Works organization. Analysts within the Controllers' Group, with whom we have worked in the past to deliver solutions to meet various ad hoc reporting and analysis needs, inform us that they have determined a further need for our assistance in their use of the IsLeaf() function, which we introduced to them in MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations.

Our client colleagues tell us that they need, once again, to understand a means, within MDX, of distinguishing leaf-level members. This time, they need a general way to filter non-leaf-level members from a broader dimension membership that includes many leaf-level members. As an example, they have an immediate need to determine a measure, Reseller Sales Amount, for Calendar Year 2004, for the lowest Sales Territory members within the Sales Territory dimensional hierarchy.

The Sales Territory dimension within the Adventure Works cube contains members at different levels. Reseller Sales Amount is aggregated no lower than the Country level for some territories, while the "lowest level value" exists for one, the United States, at a Regional level (Central, Northeast and Southwest United States, for example). The Sales Territory dimensional structure is shown in Illustration 1.

Illustration 1: The Sales Territory Dimensional Hierarchy

The Adventure Works analysts tell us that they need to present the Reseller Sales Amount for each territory's lowest level. They wish to do so with a single query, and ask us if, based upon what they have learned about the IsLeaf() function, the same sort of logic might be used in a filter of the Sales Territories within a query crafted to return the Sales information.

We review the concepts behind the IsLeaf() operator that we introduced in our last discussion with our client colleagues, and then we offer to illustrate the use of IsLeaf() to meet the immediate needs. The client representatives acquiesce, and we set about the assembly of our first example to illustrate the use of IsLeaf() in combination with the Filter() function.

Procedure: Use the IsLeaf() Operator to Perform Conditional Logic within a Filter Expression

Per the request of our client colleagues, we will first construct a simple query to provide an illustration of the use of the IsLeaf() operator within a common context, the definition of a filter based upon conditional logic. Our first example will serve as an introduction to a means of distinguishing leaf-level members within the Sales Territory dimension. This will address the request of the analysts; the results of this determination will form the basis for meeting their business requirement to filter non-leaf members from the dimension for presentation purposes.

1.  Type (or cut and paste) the following query into the Query pane:

```
/*  MDX049-001-1 IsLeaf() Operator:
Conditional Logic within Filter() Function  */
SELECT
{[Measures].[Reseller Sales Amount]} ON AXIS(0),

{FILTER(
[Sales Territory].[Sales Territory].MEMBERS,
ISLEAF([Sales Territory].[Sales Territory].CURRENTMEMBER))

}ON AXIS(1)
FROM
WHERE
[Date].[Calendar].[Calendar Year].[CY 2004]
```

The Query pane appears, with our input, as depicted in Illustration 2.

Illustration 2: Our Initial Query in the Query Pane ...

The above query selects the Reseller Sales Amount for all Sales Territory members, filtered by the condition " ... that are leaf-level," as our IsLeaf() function forms the "search condition" of "members at leaf-level."

2.  Execute the query by clicking the Execute button in the toolbar, as shown in Illustration 3.

Illustration 3: Click Execute to Run the Query...

The Results pane is populated by Analysis Services, and the dataset depicted in Illustration 4 appears.

Illustration 4: Results Dataset – IsLeaf() Operator within Filter() Function

In the returned dataset, we see that the query delivers the intended result: the Reseller Sales Amount is returned for each of the individual Sales Territory members that exist at leaf level. This happens in spite of the fact that "leaf level" means different things for different countries, as we see; the results dataset presents the measure at country level for all countries except the United States, for which it presents the value at the region level. Because the non-US countries do not subanalyze below country level within the Sales Territories dimension, their respective leaf-level values appear at the country level.

3.  Select File --> Save MDXQuery1.mdx As ..., name the file MDX049-001, and place it in a meaningful location.

Our client colleagues express satisfaction with the example we have provided, and agree with our suggestion that another example will further reinforce their understanding. This time, we suggest, we will derive the MDX to meet a requirement, and then add a Named Set to contain the logic, a practice that can mean flexible reuse of the code in a reporting scenario.

As an illustration, we formulate a business requirement that relates to Sales Associates, one of several Employee groups at Adventure Works. Let's say that we wish to present the Reseller Sales Amount value for individual sales people for Calendar Year 2004. We are given to understand that only Employees involved in Sales have a Reseller Sales Amount associated with them, although the values associated with non-managers – the actual salespeople – are the values with which we are interested. (The values associated with management personnel typically contain "rolled up" values for those sales people within their management spheres as at least part of their totals, so we wish in this case to exclude them).

To paraphrase the requirement, then, we are interested in retrieving the Reseller Sales Amount for employees in the sales department who also reside at the leaf level within the Employee dimension. (While many other Employees reside at the leaf level in this dimension, we confirm our understanding that, since only sales Employees can have an associated Reseller Sales Amount value, it will be sufficient to retrieve leaf-level employees with the associated values; filtering for leaf-level members will also serve the tandem function of eliminating sales managers from consideration.)

We will begin a new query, and build a proposed approach in multiple steps.

4.  Select File --> New from the main menu.

5.  Select Query with Current Connection from the cascading menu that appears next, as shown in Illustration 5.

Illustration 5: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

Let's begin with an "intuitive" approach – as a means of crafting a core query, as well as generating a result that will form a basis for contrast between a listing of "all Sales employees with an associated Reseller Sales Amount value" (including the sales managers I mentioned earlier) and our ultimate objective of "leaf-level members of the Sales organization with an associated Reseller Sales Amount value."

6.  Type (or cut and paste) the following query into the Query pane:

```
-- MDX049-002-1  Initial Attempt at a Solution

SELECT
{[Measures].[Reseller Sales Amount]} ON AXIS(0),

NONEMPTY( {[Employee].[Employees].MEMBERS})ON AXIS(1)

FROM

WHERE
[Date].[Calendar].[Calendar Year].[CY 2004]
```

The Query pane appears, with our input, as depicted in Illustration 6.

Illustration 6: Our Initial Query in the Query Pane ...

7.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset partially shown in Illustration 7 appears.

Illustration 7: Results Dataset – Unfiltered Employee Members

In the returned dataset, we see the unfiltered list of Employees with an associated Reseller Sales Amount value. As we have discussed, these members happen to be sales personnel, but the presented list contains non-leaf level Employees. We can verify this by inspecting the dimensional structure in the Analysis Services Cube Browser, a view of which appears in Illustration 8.

Illustration 8: The Employee Dimension Hierarchy – Relevant Members

We can see that, while fourteen employees exist at the bottom level (Level 5), a total of twenty members exist when we count higher levels (including the "All" level) within the hierarchy. Our ultimate objective is to deliver the leaf-level members – in this case, the fourteen individuals appearing within Level 5.

8.  Select File --> Save MDXQuery2.mdx As ..., name the file MDX049-002-1, and place it in a meaningful location.

Our next step will be to filter the non-leaf members from the Employees listed in the returned dataset. We will do this within the query first, before finalizing the solution by placing the working logic into a Named Set we create for that purpose in the last step.

9.  Replace the top line of the query (commented out) with the following:

`-- MDX049-002-2  Adding the Filter() / IsLeaf() Combination`

10.  Select File --> Save MDX049-002-1.mdx As ..., name the file MDX049-002-2, and place it in a meaningful location.

11.  Place the cursor to the immediate right of the left curly brace - " { " – following the NONEMPTY keyword (currently on the fourth line of the query).

12.  Press the ENTER key four times to "push down" the rest of the line, and to add space between the remaining "NONEMPTY(" and the rest of the line.

13.  Between what is now the fourth (containing "NONEMPTY(" ) line and the fifth (containing "{[Employee].[Employees].MEMBERS})ON AXIS(1)") line of the query, type in the following syntax:

`      FILTER(`

14.  Place the cursor to the immediate right of the MEMBERS keyword (currently on the sixth line of the query), between "MEMBERS" and the right curly brace - " } " - that is at its right.

15.  Insert a comma ( "," ) to the immediate right of the MEMBERS keyword.

16.  Press the ENTER key four times, once again to "push down" the rest of the line, and to add space between the remaining "MEMBERS," and the rest of the line.

17.  Between what is now the sixth (containing "Employee].[Employees].MEMBERS,") line and the seventh (containing "})ON AXIS(1)") line of the query, type in the following syntax:

`      ISLEAF([Employee].[Employees]))`

The complete query is as follows, if cutting and pasting is the preference:

```
-- MDX049-002-2  Adding the Filter() / IsLeaf() Combination

SELECT
{[Measures].[Reseller Sales Amount]} ON AXIS(0),

NONEMPTY({

FILTER(

[Employee].[Employees].MEMBERS,

ISLEAF([Employee].[Employees]))

})ON AXIS(1)
FROM

WHERE
[Date].[Calendar].[Calendar Year].[CY 2004]
```

The Query pane appears, with our input, as depicted in Illustration 9.

Illustration 9: Our Modified Query in the Query Pane ...

18.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset shown in Illustration 10 appears.

Illustration 10: Results Dataset – Leaf-Level Employee Members

In the returned dataset, we see the now-filtered list of Employees. We can see that the Employees that appear in the returned dataset comprise leaf-level (Level 5, as shown in Illustration 8 above) members with an associated Reseller Sales Amount value.

19.  Select File --> Save MDX049-002-2 to save the file.

Let's finalize our efforts by placing the logic within a Named Set. Creating the Named Set within Analysis Services will allow for easy reuse of the leaf-level Sales Employees in Reporting Services, as well as other client applications, where we might, for example, define a row in a report through the use of a single object.

20.  Replace the top line of the query (commented out) with the following:

`-- MDX049-002-3  Reusable Named Set using Filter() / IsLeaf() Combination`

21.  Select File --> Save MDX049-002-2.mdx As ..., name the file MDX049-002-3, and place it with the queries saved earlier.

22.  Place the cursor to the immediate right of the statement inserted above (in the top row).

23.  Press the ENTER key four times to "push down" the rest of the line, and to add space between the comment line and the SELECT keyword.

24.  Insert the following into the space between the comment line and the SELECT keyword:

```
WITH
SET
[SALES OPERATIVES]

AS
FILTER(
[Employee].[Employees].MEMBERS,

ISLEAF([Employee].[Employees]))
```

Here we have simply defined a Named Set containing the logic that we used within our row axis definition in the previous example.

25.  Replace the following three lines (currently lines 12, 13, and 14):

```
FILTER(

[Employee].[Employees].MEMBERS,

ISLEAF([Employee].[Employees]))
```

with the following:

`[SALES OPERATIVES]`

The complete query is as follows, if cutting and pasting is the preference:

```
-- MDX049-002-3  Reusable Named Set using Filter() / IsLeaf() Combination
WITH
SET
[SALES OPERATIVES]
AS
FILTER(
[Employee].[Employees].MEMBERS,

ISLEAF([Employee].[Employees]))

SELECT
{[Measures].[Reseller Sales Amount]} ON AXIS(0),

NONEMPTY({

[SALES OPERATIVES]

})ON AXIS(1)
FROM

WHERE
[Date].[Calendar].[Calendar Year].[CY 2004]
```

The Query pane appears, with our input, as depicted in Illustration 11.

Illustration 11: Our Modified Query in the Query Pane ...

26.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is populated by Analysis Services. This time, the dataset shown in Illustration 12 appears.

Illustration 12: Results Dataset – Leaf-Level Employee Members via Named Set

In the returned dataset, we see the same filtered list of employees. This serves to illustrate how we might meet the business need with a Named Set, which, once created within the cube involved, would support easy, consistent reporting via a single object for row or column definitions and the like.

27.  Select File --> Save MDX049-002-3 to save the file.

The client representatives confirm that their immediate goals have been met, and that the illustrations we have provided can be easily extended to local business scenarios where filtering for leaf-level members is useful in meeting reporting and analysis requirements.

28.  Select File --> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we extended our examination of the IsLeaf() operator, exploring its use, once again, as a conditional logic modifier - but this time within the context of a filter, through its combination with the MDX Filter() function. We stated that, along with the IIF() function, this is another commonly employed approach for using IsLeaf() within the business environment.

We next reviewed the general syntax involved in using IsLeaf(). Finally, we undertook illustrative examples whereby we put the IsLeaf() operator to work, in combination with the Filter() function, initially within a simple illustration to illustrate its general operation; we followed that practice example with another where we began by employing the IsLeaf() / Filter() combination within a direct row-axis definition, before placing the combination within a Named Set, to meet a hypothetical business need. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site