Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 4, 2007

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date