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
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.
this lesson, we will introduce a new addition to MDX with Analysis Services
2005, the CASE statement. Classed as an MDX scripting statement
within the Microsoft MDX documentation, CASE can return values based
upon multiple comparisons. There are two general types of CASE
statements. The simple CASE statement (which returns specific values
based upon its comparison of an expression to a set of simple expressions) is
the subject of this article. We introduce the other type, the searched CASE
statement (which returns specific values based upon its evaluation of a set of
Boolean expressions) in another article of this series. In either of its
general types, CASE is similar to the IIF() function, which we
explored in String
/ Numeric Functions: Introducing the IIF() Function and in String
/ Numeric Functions: More on the IIF() Function (both members of the Database
Journal MDX Essentials series), but can handle more conditions,
as we shall see.
statement provides far-reaching capabilities within MDX, via its capacity to
perform conditional tests within multiple comparisons, whereby it determines
the values that it returns. The powerful capabilities of CASE as a
mechanism for influencing query results through the application of logical tests
become even more formidable when the statement is used in conjunction with
other MDX functions. I have found the CASE statement
to provide excellent support within the implementation of OLAP solutions
with MSSQL Server Analysis Services, as well as for extending enterprise
Business Intelligence within MSSQL Server Reporting Services, in a wide
array of client environments and business requirements.
with an introduction to the CASE statement, this lesson will include:
an examination of the syntax options surrounding the statement;
illustrative examples of the uses of the statement in rudimentary
a brief discussion of the results datasets we obtain in the
The CASE Statement
According to the MSSQL Server 2005 Books
Online, the CASE statement enables us ... to conditionally return
specific values from multiple comparisons. We will examine the statements
manner of accomplishing its comparison of an expression to a set of simple
expressions to return specific values in the sections that follow. Our
objective, of course, is to gain a richer understanding of the capabilities
found within the CASE statement, together with a feel for its many
diverse applications in supporting the business needs of our clients and
We will examine the syntax for the simple CASE statement
in general, building to representative uses to which it can be put in meeting needs
that arise in the real world. Part of our focus will be the use of the CASE
statement to extend a cubes metadata to support conditional formatting to the Reporting
layer, but the concepts behind how CASE works are applicable to other
general uses, as well.
We will examine in detail the
syntax for the simple CASE statement 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 simple,
hypothetical business needs that illustrate a use for the function. This will
afford us an opportunity to explore some the basic options that CASE can
offer the knowledgeable user. Hands-on practice with the CASE statement,
where we will create queries that employ the function, will help us to activate
what we have learned in the Discussion and Syntax
The simple CASE statement affords us a means
of comparing an input expression to one or more when expressions.
Beginning with the first comparison, if the input expression and a
when expression match, the CASE statement returns the value assigned
the respective when expression. If the two expressions do not match,
the next WHEN clause is compared to the input expression. The
evaluations continue until a match occurs, or, if comparison to all the WHEN
clauses evaluate to false," then the value of the ELSE clause (if
provided) is returned. When none of the tests has a true outcome and no default
(ELSE) clause is in place, NULL is returned by the CASE
Lets look at a syntax illustration to further clarify the
operation of CASE.
Syntactically, the Input Expression
upon which the evaluation of true or false is to be applied by the CASE statement
is placed to the right of the CASE keyword, and is then followed by the WHEN
/ THEN pairs against which comparisons are performed. Each occurrence
of WHEN is followed by a When Expression, and then followed by
the THEN keyword (with a When True Result Expression). After all
the WHEN / THEN pairs comes the ELSE keyword followed by
the corresponding Else Result Expression the optional default
result, to which we referred earlier, which is returned if none of the When
Expressions that precede it evaluate to true.
The syntax is shown in the
CASE <<Input Expression>>
WHEN <<When Expression>>
THEN <<When True Result Expression>>
[ ... other WHEN / THEN combinations ...]
ELSE <<Else Result Expression>>
To reiterate, the related When
True Result Expression is returned for
the first When Expression that is evaluated as true. When comparison
with a When Expression evaluates to false, then comparison testing
occurs against the next When Expression, and the process continues until
a true evaluation results (whereupon a When True Result Expression is
returned), or until comparison with all When Expressions have been
evaluated as false, at which point the Else Result Expression is
returned. (If this default clause is not supplied, the final result of the CASE
statement is NULL, as we noted earlier).
The Input Expression is an MDX
expression that resolves to a scalar value. The When Expression(s) are
scalar values against which the Input Expression is evaluated, with the When
True Result Expression, another scalar value, being returned when the
evaluation results in a true condition. The Else Result Expression is
a scalar value that is returned, as we have seen, when the WHEN clauses
preceding it have all evaluated to false. An empty cell results, once again,
in a scenario where all WHEN clauses evaluate to false and the ELSE
clause is absent.
Employing the CASE statement is
straightforward. It is considerably easier to construct and maintain than the
nested IIF() syntax that was our only practical option, prior to the
advent of the CASE statement with Analysis Services 2005, for conditionally
returning specific values from multiple comparisons. As we have seen, the CASE
statement affords us a means of testing against multiple criteria and changing
the flow of our MDX scripts based upon the outcomes using a construct that
resembles closely the SQL CASE statement, with which many of us are
As an example, lets preview an exercise
we will perform together in the Practice section below, within a
query executed against the sample Adventure Works cube. The following
pseudo-expression might be used within the definition of a calculated member we
could use in an MDX query to drive color coding of returned results. (We could
use the returned color code to set font / cell color properties, for example,
in a report we crafted in Reporting Services, so as to give each Country
a different color in a pie chart or other data region, as a means of presenting
operating results to information consumers.)
WHEN [Geography].[Country].[Australia] THEN 'Blue'
WHEN [Geography].[Country].[Canada] THEN 'Green'
WHEN [Geography].[Country].[France] THEN 'Yellow'
WHEN [Geography].[Country].[Germany] THEN 'Red'
WHEN [Geography].[Country].[United Kingdom] THEN 'Black'
WHEN [Geography].[Country].[United States] THEN 'White'
Were we to construct a query that positioned the
above-defined calculated member alongside the Reseller Sales Amount,
with our row axis specifying the members of the Country attribute
hierarchy of the Geography dimension, we might expect a returned dataset
similar to that depicted in Illustration 1.
Illustration 1: Results Dataset containing a Calculated
Member Based upon the CASE Statement
We will practice some uses of the CASE statement in
the section that follows.