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 my first article, MDX at
First Glance: Introduction to MDX Essentials.
Note: Current updates are assumed for MSSQL
Server, MSSQL Server Analysis Services, and the related Books
Online and Samples.
Overview
Within the world of OLAP, sparsity is pervasive. There are
many reasons to want to display something other than an empty space in a
report, where a "blank" is about as welcome, to most readers, as
silence on a radio show. In an Analysis Services cube, as well as in the
reporting layer of an integrated business intelligence solution, we have
various options for the elimination or suppression of, and substitution for,
nulls. This article concerns itself with one of those options for "handling
the emptiness," the CoalesceEmpty() function.
In
this article, we will introduce and overview the CoalesceEmpty()
function. We will first comment upon the general operation of CoalesceEmpty(),
and then we will:
-
Examine the syntax surrounding the function;
-
Undertake illustrative examples of the rudimentary uses of the
function in practice exercises;
-
Briefly discuss the results datasets we obtain in the practice
examples.