Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 1, 2004

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

By William Pearson

The IsEmpty() Function


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.


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().


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.

   'IIF( ISEMPTY( [Measures].[Units Shipped]), "None", "Activity:")'
   {[Warehouse].[State Province].Members} ON COLUMNS,
      {[Product].[All Products].[Food].[Canned Foods].[Vegetables]},
         {[Measures].[Activity], [Measures].[Units Shipped]}) ON ROWS

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

Illustration 1: Returned Dataset for Example Query

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM