MDX Essentials: Logical Functions: The IsEmpty() Function - Page 2

The IsEmpty() Function

Introduction

The ISEMPTY() function, according to the Analysis Services Books Online, "returns TRUE if the evaluated expression is the empty cell value, FALSE otherwise." We will examine the function's manner of accomplishing these evaluations, and discuss factors that we should consider based upon the function's behavior, in the sections that follow.

We will examine the syntax for the ISEMPTY() function in general, building to its use in meeting an issue that arises in the real world, where "empties get in the way" of our objectives of clear analysis and reporting. In this way, we will be able to clearly see that the ISEMPTY () function does, in fact, generate the results we might expect. Our objective is to gain a richer understanding of the capabilities found within the ISEMPTY () function, together with a feel for potential uses of the function. As a byproduct of our examination of ISEMPTY(), we will introduce a simple use of the IIF() function, which will serve to overview the basics of IIF(), as a preliminary for the two articles that follow this one.

Discussion

ISEMPTY () affords us a means of testing for empty cells. It becomes valuable in light of the fact that sparseness, as we have noted earlier, is a fact of life in multidimensional data sets, and is often an obstacle, as we shall discuss, in our analysis and reporting efforts. And with empty cells come various data presentation problems that we can remedy easily, in many cases, using ISEMPTY() to detect the empties and manage them in various ways.

ISEMPTY() is often accompanied by the conditional IIF() function, as we shall see in our practice section. It often appears, also, with the NOT keyword, in scenarios where we are attempting to manipulate cells, in some way, which are not empty.

Let's look at a syntax illustration to further clarify the operation of IS EMPTY().

Syntax

Syntactically, the expression upon which the evaluation of "empty or not empty" is to be applied by the ISEMPTY() function is placed within the parentheses to the right of ISEMPTY. The syntax is shown in the following string:

ISEMPTY(<<Value Expression>>)

A logical function, ISEMPTY() returns "True" if the Value Expression is null, and "False" if it is not null. That is, ISEMPTY() evaluates the expression we provide in the parentheses to its right, and returns either a negative one (-1) or a zero (0), depending upon whether the expression is revealed to be an empty cell or not, respectively.

If we seek to use a tuple, versus a basic member reference, as the Value Expression, we must surround the expression by parentheses, as shown in the following string:

ISEMPTY ( ([Measures].[Units Shipped], [Time].NextMember) )

The following simple example illustrates conceptually the operation of the ISEMPTY() function. (We will be doing a practice exercise in subsequent sections, but if you want to "test drive" a sample, you can certainly cut and paste, or type, the below into the MDX Sample Application).

We will query the Warehouse sample cube to determine, for operating year 1998, which State / Province locations recorded no shipping activity for canned vegetables. We will use a calculated member ("Activity") that indicates whether there was shipping activity by indicating "None," if there were no units shipped, and "Activity:" if, indeed, there were. We will display the number of Units Shipped for the year in a row below the string indicator, to demonstrate the fact that the calculated member generates the expected results.

WITH MEMBER
   [Measures].[Activity]
AS
   'IIF( ISEMPTY( [Measures].[Units Shipped]), "None", "Activity:")'
SELECT
   {[Warehouse].[State Province].Members} ON COLUMNS,
   CROSSJOIN( 
      {[Product].[All Products].[Food].[Canned Foods].[Vegetables]},
         {[Measures].[Activity], [Measures].[Units Shipped]}) ON ROWS
FROM
   [Warehouse]
WHERE 
   ([Time].[Year].[1998])

This query returns a set similar to that depicted in Illustration 1.

We note that the returned data indicates that two Mexican provinces, Jalisco and Yucatan, apparently experienced no shipping volume for canned vegetables within the operating year of 1998.

We will activate the concepts involved in the foregoing discussions by practicing the use of the ISEMPTY() function in the section that follows.