MDX Operators: The IS Operator

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 session progressively adding features and capabilities designed to meet specific real-world needs.

Virtually all of the MDX we have constructed in earlier articles can now be used in the SQL Server Management Studio, SQL Server Business Intelligence Studio, and various other areas within the Microsoft integrated Business Intelligence solution, and much of what we construct going forward can be executed in the Analysis Services 2000 MDX Sample Application (assuming connection to an appropriate Analysis Services data source). MDX as a language continues to evolve and expand: we will focus on many new features in articles to come, while still continuing to examine business uses of MDX in general. Exploiting MDX to meet the real-world needs of our business environments will continue to be my primary concentration within the MDX Essentials series.

For more information about the series in general, as well as the software and systems requirements for getting the most out of its member lessons, please see Set Functions: The DrillDownMember() Function, where important information is detailed regarding the applications, samples and other components required to complete our practice exercises.


While MDX functions comprise the lion’s share of the MDX Essential series, numerous operators are supported within the language. (We examined a group of basic operators in my Database Journal article MDX Operators: The Basics). Of the logical, comparison, set, string, and unary operators provided by MDX, we will examine a logical operator in this session. The IS operator, like other logical operators, evaluates values and returns a Boolean value. The IS operator’s utility becomes clear when we leverage it to perform comparisons between objects of any kind within Analysis Services.

In this article, we will extend our examination of MDX functions to concentrate upon the useful IS operator. We will discuss the straightforward purpose of the operator, to ascertain the equivalence of two object expressions, the manner in which IS manages to do this, and ways we can employ the operator to perform efficient comparisons, and to help us to meet various business needs in our own environments.

Along with an introduction to the IS operator, this lesson will include:

  • an examination of the 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 Is Operator


According to the Books Online, the IS operator “performs a logical comparison on two object expressions.” Moreover, the Books Online state that “the IS operator is often used to determine whether tuples and members are idempotent,” or unchanged in value following multiplication by themselves. A Boolean value of “true” is returned if both object expressions refer to the same object, otherwise IS returns “false.”

Another common use of the IS operator is to compare a given object expression with the NULL keyword. When we use NULL with IS, the operator allows us to ascertain existence, returning “true” if the expression to which it is compared is null, and returning “false” if not.

We will examine in detail the syntax for the IS 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 hypothetical business needs that illustrate uses for the operator. This will afford us an opportunity to explore some of the options that IS can offer the knowledgeable user. Hands-on practice with IS, where we will create queries that employ the function, will help us to activate what we have learned in the Discussion and Syntax sections.


To restate our initial description of its operation, the IS operator performs a comparison between two object expressions, returning “true” if the object expressions are identical, and “false” if not. Using IS to compare an object expression to the NULL keyword is often used as an “existence” test for the object under consideration. As we have noted to be the case with most MDX functions and operators, pairing the IS operator with other MDX operators and functions can help us to leverage its power even further.

Let’s look at syntax specifics to further clarify the operation of IS.


Syntactically, we employ the IS operator between the two object expressions upon which we wish to perform a logical comparison. The general syntax is shown in the following string:

Object_Expression IS ( Object_Expression | NULL )

Both Object Expressions are valid MDX expressions that represent, or return, an MDX object reference. As we have noted, the operator returns a Boolean value of “true” if both Object Expressions refer to the same object; otherwise it returns “false.” If the NULL keyword is substituted for the right Object Expression, then the IS operator returns “true” if the left Object Expression is null; otherwise it returns “false.”

Let’s take a look at an illustration. The following snippet, taken from the WITH section of a query, where it helps to define a calculated member that is subsequently specified in the SELECT section of the query, employs the IS operator:

[Measures].[Annual Adjusted Sales]
  'IIF([Date].[Calendar].CURRENTMEMBER IS 
     [Date].[Calendar].[Calendar Year].[CY 2004],
         [Measures].[Internet Sales Amount], 
            ([Measures].[Internet Sales Amount] * .8)

Let’s say that the above-defined calculated member, together with the existing Internet Sales Amount measure, is crossjoined with the members of the Calendar Year level of the Date dimension (Calendar hierarchy) within the rows-axis specification. Moreover, we will assume that we specify All Products in the column-axis specification, and that the query that emerges is executed against the Adventure Works sample cube. Our query would produce a results dataset similar to that depicted in Illustration 1.

Illustration 1: Results Dataset – IS Operator Used in Definition of the Calculated Member …

In the example dataset, we see that the IIF() function, using the IS operator, is effectively comparing the current year member (the Object Expression to the left of the IS operator), to Calendar Year 2004. The calculated member whose definition contains this comparison thus returns Annual Adjusted Sales equal to the Internet Sales Amount for 2004, while returning the adjusted Internet Sales Amount (80 percent of the measure) as Annual Adjusted Sales for the other years within the cube.

NOTE: For details surrounding the .CurrentMember function, see MDX Member Functions: “Relative” Member Functions, a member of my MDX Essentials series here at Database Journal.

We will examine the workings of the operator in more detail in the Practice section that follows.

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.

Latest Articles