MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
December 6, 2004
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.
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:
The IIF() Function
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.
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().
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:
IIF(<<Logical Expression>>, <<Numeric Expression 1>>, <<Numeric Expression 2>> )
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.