MDX Scripting Statements: Introducing the Simple CASE Statement - Page 3

June 4, 2007

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.

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

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.

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

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.

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.

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.

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

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.

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.

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

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.

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.

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.