About the Series ...
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.
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.
We will practice some uses of the IsLeaf() operator,
focusing on its combination with the Filter() function, in the section
that follows,
» 
Comment and Contribute
