MDX Essentials: String / Numeric Functions: Introducing the IIF() Function

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles