MDX Scripting Statements: Introducing the Simple CASE Statement

Monday Jun 4th 2007 by William Pearson
Share:

Use the new CASE statement to support conditional logic within calculations. BI Architect Bill Pearson introduces the simple CASE statement, and then leads a hands-on practice session with this new addition to the MDX toolset.

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

In 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.

The CASE 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.

Along 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 practice exercises;
  • a brief discussion of the results datasets we obtain in the practice examples.

The CASE Statement

Introduction

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 statement’s 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 employers.

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 cube’s 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 sections.

Discussion

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 statement.

Let’s look at a syntax illustration to further clarify the operation of CASE.

Syntax

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 following strings:

CASE <<Input Expression>>
    WHEN <<When Expression>>
       THEN  <<When True Result Expression>>
    [ ... other WHEN / THEN combinations ...]
    ELSE  <<Else Result Expression>>
END 

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 already familiar.

As an example, let’s 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.)

WITH 
   MEMBER [Measures].[Color]
   AS 
      CASE [Geography].[Country].Currentmenber
         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' 
         ELSE 'Gray'
      END

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

NOTE: For hands-on guidance in an Analysis Services - based approach for driving conditional formatting in the Reporting layer in general, see Positing the Intelligence: Conditional Formatting in the Analysis Services Layer, a member of the Database Journal Introduction to MSSQL Server Analysis Services series. Moreover, for an approach to using calculated members in Analysis Services to drive conditional formatting within Reporting Services specifically, see Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services, a member of the Database Journal MSSQL Server Reporting Services series.

We will practice some uses of the CASE statement in the section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will use the CASE statement within a couple of queries that illustrate its operation, focusing, within this article, upon scenarios where we use the simple CASE statement to meet the business requirements of a hypothetical client. (As we have noted earlier, we examine the “searched” type of the statement, which returns specific values based upon its evaluation of a set of Boolean expressions, in an independent article dedicated to the details surrounding that type.) We will undertake our practice exercises within scenarios that place the CASE statement within the context of meeting basic requirements similar to those we might encounter in our respective daily environments. The intent is to demonstrate the use of the statement in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. If you do not know how to access the SQL Server Management Studio in preparation for using it to query an Analysis Services cube (we will be using the sample Adventure Works cube in the Adventure Works DW Analysis Services database), please perform the steps of the following procedure, located in the References section of my articles index:

Prepare MSSQL Server Management Studio to Query Analysis Services

This procedure will take us through opening a new Query pane, upon which we will create our first query within the section that follows.

Procedure: Satisfy Business Requirements with MDX

Let’s assume, for purposes of our practice example, that we have received a request for assistance from representatives of our client, the Adventure Works organization. Analysts within the VP - Sales group, with whom we have worked in the past to deliver solutions to meet various reporting and analysis needs, inform us that they have received a request to generate some “tag” values for a specific analysis task that has been discussed at a recent meeting with Marketing group peers.

The analysts tell us that the values under immediate consideration involve Internet Order Quantities, but, as is typically the case in our collaborative sessions, they want to develop an approach that will work equally well with other measures that have similar analysis potential. (As we have noted in other sessions of our series, our client colleagues often derive parameterized queries in Reporting Services from the basic MDX syntax we assemble together, and can thus create self-serve reports that allow information consumers to dictate what measure they wish to analyze, and myriad other options, at run time.) The desired immediate end is to simply return the Internet Order Quantity recorded for the initial year of operations, Calendar Year 2001, for each Postal Code for a sample State-Province. (They have chosen Washington as a start, but assure us that they realize that the State-Province can be parameterized in the reports they eventually build, based upon the sample logic that we help them to devise.)

Our client colleagues tell us that they wish to classify the Internet Order Quantity for each of the Postal Codes. They will place the “activity label” in a column to the right of the Internet Order Quantity column of the returned dataset, using the logic found in Table 1.

Internet Order Quantity

Activity Label

0

None

1

Single

2

Low

3

Low

4

Low

5

Moderate

6

Moderate

7

Moderate

8

Moderate

9

Moderate

10 and Above

Substantial


Table 1: Desired Activity Labels for Quantities Associated with Each Postal Code

As is often the case, this basic need might be met multiple ways with an MDX query. Because the analysts have made known the desire to eventually evolve the query to allow parameterization of the State-Province, as well as Calendar Year and so forth, we want to propose a sample that lends itself to flexible modification later. Once again, the richness of MDX affords us a number of avenues to this objective. While parameterization is itself not a consideration in our current level of query design, we want to make it easy to accomplish within Reporting Services. (The same concepts would, of course, apply with other OLAP reporting tools that afford developer access to the MDX syntax that underlies them).

After we initially explain the use of the CASE statement as a candidate for meeting the requirement, our client colleagues state that they are interested in understanding how they might apply conditional logic via this function, within the context of a practical scenario such as the immediate requirement. The simple CASE statement appears an adequate mechanism for evaluating the Internet Order Quantity measure for each individual Postal Code against several WHEN clauses, and for returning the “label” result appropriate for the respective Postal Code’s value for the year. For Internet Order Quantities that do not match a scalar value specified in any of the When Expressions – that is, quantities of ten (10) and above – the scalar value of the Else Result Expression (“Substantial”) will be returned.

We discuss our reasoning with the analyst group, and then offer to illustrate the use of the CASE statement to meet the immediate need, both to solidify the analysts’ new understanding and to assist in rounding their overall MDX “vocabularies.” We then set about the assembly of our example to illustrate the use of CASE.

Procedure: Use the Simple CASE Statement within a Calculation

Per the request of our client colleagues, we will construct a simple query to provide an illustration of the use of the simple CASE statement within a common context, the definition of a calculation based upon conditional logic.

Our initial example will serve as an introduction to a means of “bucketizing” the respective measure value for each of a group of members (in our case, the Postal Code members of the State-Province of Washington), as requested by the analysts. This will serve as a basis for meeting the business requirement to present the activity labels assigned to each Internet Order Quantity value, so that the label appears side-by-side with the value upon which it is based.

1.  Type (or cut and paste) the following query into the Query pane:


-- MDX056-001 Simple CASE Statement: Conditional Logic in 
--   the Definition of a Calculation
WITH 
MEMBER
   [Measures].[Activity]
AS
   CASE [Measures].[Internet Order Quantity]
      WHEN 0 THEN 'None' 
      WHEN 1 THEN 'Single'
      WHEN 2 THEN 'Low' 
      WHEN 3 THEN 'Low' 
      WHEN 4 THEN 'Low' 
      WHEN 5 THEN 'Moderate' 
      WHEN 6 THEN 'Moderate' 
      WHEN 7 THEN 'Moderate'
      WHEN 8 THEN 'Moderate'
      WHEN 9 THEN 'Moderate' 
      ELSE 'Substantial' 
   END 

SELECT 
   {[Measures].[Internet Order Quantity],
[Measures].[Activity]} ON AXIS(0) ,
   {DESCENDANTS([Customer].[Customer Geography]
.[State-Province].[Washington],
      [Customer].[Customer Geography].[Postal Code])} ON AXIS(1)
FROM 
   [Adventure Works] 
WHERE 
   ([Date].[Calendar].[Calendar Year].[CY 2001])

The Query pane appears, with our input, as shown in Illustration 2.


Illustration 2: Our Initial Query in the Query Pane ...

The above query returns the Internet Order Quantity for each member of the Customer dimension (Customer Geography attribute hierarchy, Postal Code level of the State-Province of Washington) in the cube. We use the WHERE clause to physically screen the results to show our “focus” Calendar Year of 2001.

Recall that we have said that we might accomplish our ends through alternative methods. The approach we are taking here allows us to parameterize the State-Province component within the row axis specification – we could even parameterize the level within the Descendants() function to allow control of the detail presented, among other elements of our query, to accomplish potential extended ends of our client colleagues. In doing so, we could set up a hierarchical picklist within Reporting Services, whereby information consumers might select a given Country, State-Province, City, Postal Code, and even Customer Name, and so forth, to drive the level whose members’ values are returned, among other possibilities. The obvious advantage is that consumers can dictate the dimensional level as well as the member(s) of that level (Reporting Services 2005 supports multiple selection within properly constructed picklists), upon which the “focus” is enacted. In some circumstances, “double leverage” could be provided by modifying the query to do more within a single parameter – which might be seen as a desirable efficiency within the realm of simulated dynamic drilldown effects and so forth.

In the Activity calculation, we put the CASE statement to work in applying conditional logic to generate the Activity label value: if the value of the Internet Order Quantity evaluates to “true” against any of the WHEN clauses, the label result appropriate for the respective Postal Code’s value for the year is returned. For Internet Order Quantities that do not match a value specified in any of the When Expressions – that is, quantities of ten (10) and above – the value of the Else Result Expression (“Substantial”) is returned.

NOTE: For more information about, and hands-on practice within, working examples of MDX query parameterization, see various member articles of my MSSQL Server Reporting Services series.

2.  Execute the query by clicking the Execute button in the toolbar, as depicted in Illustration 3.


Illustration 3: Click Execute to Run the Query...

The Results pane is populated by Analysis Services, and the dataset partially shown in Illustration 4 appears.


Illustration 4: Results Dataset (Partial View) – CASE Statement within a Calculation

In the partial view of the returned dataset, we see that the calculation accomplishes the intended purpose - generating the Activity labels for the Internet Order Quantity associated with the individual Customer Geography hierarchy members that belong to Postal Code level of the U. S. State-Province of Washington. Again, the conditional tests that support label generation are applied via a calculated member within which we have leveraged the simple CASE statement.

3.  Select File -> Save MDXQuery1.mdx As ..., name the file MDX056-001, and place it in a meaningful location.

Our client colleagues express satisfaction with the contextual backdrop we have established for introducing the CASE statement. We will next use a similar query within another such example, to confirm understanding of the concepts. This query will provide an illustration of the use of the CASE statement within the context we have already seen, the definition of a calculated member based upon multiple comparisons. As before, we will base our example upon a local scenario posed by the client representatives: a need to support conditional formatting within properties of a report they have in mind to be created in Reporting Services.

The analyst group tells us that they wish to be able to drive color properties for a given report, based upon geography of the reporting entity. The client representatives tell us that the level – even the dimension – may be modified beyond the initial requirement. Their primary focus is, as usual, to master concepts that can be extended beyond the immediate example as the need arises.

Our client colleagues tell us that they wish to drive color properties in the Reporting layer for each Country using the logic found in Table 2.

Country

Color

Australia

Blue

Canada

Green

France

Yellow

Germany

Red

United Kingdom

Black

United States

White

Other

Gray


Table 2: Desired Color Formatting Properties for Respective Countries

The analysts tell us that the values under immediate consideration, from the perspective of the initial query, involve Reseller Sales Amounts, although, as always, the idea is to develop an approach that will work equally well with other measures of interest. Per the request of our client colleagues, we will construct a simple query to provide an illustration of the use of the simple CASE statement, once again within the definition of a calculation based upon conditional logic, to meet this end.

4.  Press key combination ALT + N, to open a tab for a new query within the current Analysis Server connection.

5.  Type (or cut and paste) the following query into the Query pane:


--  MDX056-002 Simple CASE Statement:  Conditional Formatting Support
WITH 
MEMBER 
   [Measures].[Color]
AS 
   CASE [Geography].[Country].Currentmenber
      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' 
      ELSE 'Gray' 
   END 

SELECT 
   {[Measures].[Reseller Sales Amount], [Measures].[Color]} ON AXIS(0) ,
 {[Geography].[Country].Members} ON AXIS(1)
FROM 
   [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 5.


Illustration 5: Our Second Practice Query in the Query Pane ...

The above retrieves the calculated member Color for each member of the Geography dimension, Country attribute hierarchy, alongside the corresponding Reseller Sales Amount. Within the calculation involved, we put the CASE statement to work to apply conditional logic to generate the Color value, providing all that is required to support color properties for the value text, its background cell, and so forth within the associated property of a report in Reporting Services. The same sort of logic might, of course, be applied within far more elaborate scenarios, a fact that we emphasize to our client colleagues.

NOTE: For detailed guidance in using calculated members in Analysis Services to drive conditional formatting within Reporting Services, see Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services, a member of my Database Journal MSSQL Server Reporting Services series.

Finally, and rather obviously, because we have specified that the Geography.Country members are to populate the rows axis, we have afforded ourselves a quick means of reasonability testing of the logic within the calculation that we have defined, as we shall see.

6.  Execute the query by clicking the Execute button in the toolbar, as shown earlier.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 6 appears.


Illustration 6: Results Dataset – Simple CASE Statement within a Calculation

In the view of the returned dataset, we see that the calculation accomplishes the intended purpose – generating the desired Color specification for the individual Countries alongside a standard measure, Reseller Sales Amount.

7.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX056-002, and place it in a meaningful location.

In the returned dataset, we see that the query appears to meet the business requirements outlined by the client representatives. We have delivered a simple calculation, based upon conditional logic, which supplies a color specification that we can use to set properties within Reporting Services – properties that can drive a range of report attributes, including text or cell coloring, color representation within pie and bar charts, and so forth, as we have noted.

The client representatives confirm that the immediate goal of the practice example has been met: the creation of a calculation, which is dictated by the CASE statement in a manner that lends itself to the parameterization opportunities that are expected to arise at the Reporting layer. Moreover, they state that the illustration we have provided will be easily extrapolated to other scenarios where they need to perform an action, or to present a value, based upon the outcome of a group of comparison tests to which an Input Expression can be subjected.

8.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we introduced the CASE statement, another addition to MDX in Analysis Services 2005. We learned that this MDX scripting statement can return values based upon multiple comparisons, and that 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), and the searched CASE statement (which returns specific values based upon its evaluation a set of Boolean expressions). In this article, we focused upon the former type, discussing the straightforward purpose of the statement, the manner in which CASE manages to accomplish its purpose, and ways we can leverage the statement to support effective conditional logic to meet various business needs within our own environments.

After introducing CASE, we examined the syntax with which we employ the statement. We then undertook illustrative examples whereby we put the simple CASE statement to work to meet the business needs of a hypothetical client. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed, and potential uses for parts of these datasets within the Reporting layer of an integrated business intelligence solution.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved