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.

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers