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 the first article, MDX at
First Glance: Introduction to MDX Essentials.
Note: Service Pack 3 updates are assumed for MSSQL
Server 2000, MSSQL Server 2000 Analysis Services, and the related Books
Online and Samples.
Overview
In
this lesson, we will introduce a function that can technically be classified as
either a string or a numeric function, depending upon which of two choices we
make in its syntax. Whichever of the two options we select, the IIF()
function returns one of two possible values, based upon whether a logical test
it performs upon a specified expression is evaluated to be true or false.
The IIF()
function provides far-reaching capabilities within MDX, both as a mechanism for
influencing query results through the application of a logical test, and as a
means for providing sophisticated solutions when used in conjunction with other
MDX functions. I have found IIF() to be an excellent tool for
implementing OLAP solutions with MSAS, as well as for implementing enterprise
Business Intelligence with MSSQL Server Reporting Services, in a wide array of
client environments and business requirements. Along with an introduction to
the IIF() function, this lesson will include:
-
an examination of the syntax options surrounding the function;
-
illustrative examples of the uses of the function in practice
exercises;
-
a brief discussion of the results datasets we obtain in the
practice examples.
The IIF() Function
Introduction
The IIF() function, according to
the Analysis Services Books Online, "returns one of two numeric or
string values determined by a logical test." We will examine the function's
manner of accomplishing its evaluations,
and discuss factors that we should consider based in the selection of the
options that IIF() offers, in the sections that follow.
We will examine
the syntax for the IIF() function in general, building to its use in meeting
an issue that arises in the real world, where we use it in a somewhat more
sophisticated scenario to overcome an obstruction to analysis and reporting. In
this way, we will be able to clearly see the rudiments of operation without
distraction, then to see a second instance where we replicate a problem we
might encounter in working with MDX and our OLAP data sources, and then how we
can use the IIF() function to extend a cube's metadata, and to generate
the results we need.
In our last article, Logical
Functions: The IsEmpty() Function,
we witnessed the use of the IIF() function, in combination with the ISEMPTY()
function, in handling empty tuples, a common occurrence in the often sparse
OLAP data sources that we encounter in the business environment. The use of IIF()
in the latter part of the article served to expose the function to us prior to
our getting to spend some "quality time" with the topic. (It might be
advantageous to return to the practice example we undertook together in that
article, after working through this one, in order to activate the concepts from
the perspective of our discussions here.) Our objective, of course, is to gain a
richer understanding of the capabilities found within the IIF() function,
together with a feel for its many diverse applications in supporting the
business needs of our clients and employers.
Discussion
IIF() affords us a means of testing a logical
expression (or "search condition" ) for a true / false outcome,
and then returning one of two specified values, based upon that outcome. IIF()
is restricted to either a pair of potential numeric return values or a
pair of potential string return values; we cannot mix the two in a single
use of the function, as we shall see in later sections.
The test of the specified logical expression in the IIF()
function cannot itself have null as an outcome, because the comparison
operators that are inherent to a logical expression effectively convert any
nulls to zeroes, for purposes of the comparison. Moreover, by similar
reasoning, in cases where one of the two return values is a null, the function
itself is limited to the numeric (versus string) option that we mentioned
earlier. The consequence of this is that we cannot specify a null return value
if we have chosen the string option, as we can when going the numeric route.
Keeping these considerations in mind can help us to avoid errors in our uses of
the IIF() function.
Let's look at a syntax illustration to further clarify the
operation of IIF().
Syntax
Syntactically, the expression upon
which the evaluation of "true or false" is to be applied by the IIF()
function is placed within the parentheses to the right of IIF, and is
followed by the true and false return values, respectively. As
we have noted, we can select between the numeric and string options, but cannot
mix the two. The syntax is shown in the following strings:
Numeric Option:
IIF(<<Logical Expression>>, <<Numeric Expression 1>>, <<Numeric Expression 2>> )
String Option:
IIF(<<Logical Expression>>, <<String Expression 1>>, <<String Expression 2>> )
As is somewhat obvious, "Expression
1" is returned for the appropriate option if the Logical Expression
is evaluated as "True." If the logical test is determined to
be "False," then "Expression 2" is returned.
Keep in mind that both return values in either option have to be of the same
expression type, number or string. Attempting to mix numeric and string values
here will result in failure, as will attempting to pair a null with a string,
as we have mentioned earlier.
» 
Comment and Contribute
