By: Scott Murray | Comments (2) | Related: > Analysis Services Development
Problem
The Case statement exists in many programming languages and it is also available when writing MDX for SQL Server Analysis Services cubes. There are two types of Case statements that can be used in MDX; the simple case and the search case. In this tip we will cover how each of these can be used when writing MDX code.
Solution
The MDX Case Statement allows for the logical comparison of one expression in relation to a second expression. When the comparison of values is evaluated to true, a specified value is returned; if the expression comparison is false, then the evaluation progresses to the next comparison, if included. If an additional comparison is included, then that expression is evaluated or an optional catch all "Else" clause can be included. The else clause returns the specified value when all other evaluated expression comparisons return false.
The processing of the comparison works from top to bottom returning the noted expression or value for the first comparison, in order, that evaluates to true. Once a comparison evaluates to true, no additional evaluation takes place for that row or input value, and the process moves on to the next input value to compare. If none of the expression comparisons return a true and no else clause is included, then the returned value is an empty value, i.e. no value.
There are two versions of the case statement available for use. The first, called the simple case statements, compares a single item versus each input value. The second statement, a search case statement, allows for more advanced Boolean logic for the comparison.
Simple MDX Case Statement
The Simple MDX case statement has three main components as shown below. First, after the Case keyword is the Input Expression. The input expression must be a MDX expression that results in a scalar value. The scalar value is then compared against each expression. Finally, if no values match, then the else expression is used. Finally, the End keyword finishes the Case statement.
CASE
<Input Expression>
WHEN <When Expression> Then <Value>
...
ELSE <Value>
END
The best way to understand the MDX case statement is to review some examples. These examples come from the AdventureWorksDW2012 SSAS database which is available on CodePlex, http://msftdbprodsamples.codeplex.com/releases/view/55330.
In the first example below, we are using a named member to run through the evaluation process; the Internet Order Quantity is evaluated against two numbers, 7620 and 5625. If the Internet order quantity is either of these numbers, in that order, then the set will return "Great" or "BAD", respectively. If neither of these values are matched, then the statement returns "OK". The field for the case statement set is named ContinentRollUP
WITH MEMBER [Measures].ContinentRollUP AS
CASE [Measures].[Internet Order Quantity]
WHEN 7620 THEN 'Great'
WHEN 5625 THEN 'BAD'
ELSE 'OK'
END
SELECT
{[Measures].[Internet Sales Amount]
, [Measures].[Internet Order Quantity]
, [Measures].ContinentRollUP
} ON COLUMNS
,{[Sales Territory].[Sales Territory Country].Members} ON ROWS
FROM [Adventure Works]
With this query, the results are as follows.
Note that the Canadian value for Internet Order Quantity matches 7620 and thus returns "Great" for the ContinentRollUP field. Likewise, the Germany value, which matches 5625, returns "BAD", while all the remaining rows are assigned the else value of "OK".
As an alternate, the case statement can actually be setup as a calculated measure as shown in the below screen print.
The new calculated measure value is displayed in the following screen print.
This functionality is great, but the simple case statement has limited functionality. You can only use a single expression for evaluation and only a scalar can be evaluated.
Search Case Statement
The search case statement expands the case statement functionality by allowing expanded Boolean type comparisons when attempting to a match an input value. The basic format is very similar to the simple case statement with just a few exceptions, as displayed below. The input expression is no longer right after the case keyword. Instead, the Boolean expression allows for the comparison of one or multiple expressions which are evaluated as true or false. This functionality allows the cube designer more flexibility as various logical comparisons can be made such as greater than, less than, or greater than and equal to. Also direct comparisons of non-scalar values can be made in the Boolean expression. One word of caution as you design the Boolean expression; you must be careful to note the scope of the item to be evaluated. More on this item later in the tip.
CASE
WHEN <When Boolean Expression> Then <Value>
...
ELSE <Value>
END
Again, an example would probably be the best way to show off the case statement functionality. In the below illustration, the Sales Territory Country is being evaluated again to provide a set of values in order to provide a field called Continent Rollup. Notice how the MemberValue property is used to compare against the specified value. If the country matches, then the statement returns the noted ContinentRollUp value, else move on to the next line. If none of the 4 value match, then the default value of "All Other" is used.
WITH MEMBER ContinentRollUP AS
CASE
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='United States' THEN 'North America'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='Canada' THEN 'North America'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='France' THEN 'Europe'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='Germany' THEN 'Europe'
ELSE 'All Other'
END
SELECT
{ ContinentRollUP
,[Measures].[Reseller Sales Amount]
} ON COLUMNS
,{
[Sales Territory].[Sales Territory Country].Members
} ON ROWS
FROM [Adventure Works]
The above query provides the below results. Notice how even the NA and All Sales Territories (all members rollup) are assigned a value.
We can easily expand the Boolean expression to evaluate 2 or more value expressions. Of course this process provides us with great flexibility; an example of this expanded flexibility is included below. This example adds a second member set which uses Boolean comparison for the Reseller Sales Amount in addition to the country rollup.
WITH MEMBER ContinentRollUP AS
CASE
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='United States' THEN 'North America'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='Canada' THEN 'North America'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='France' THEN 'Europe'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='Germany' THEN 'Europe'
ELSE 'All Other'
END
MEMBER ContinentRollUP2 AS
CASE
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='United States'
AND [Measures].[Reseller Sales Amount] >15000000 THEN 'North America-GOOD SALES'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='Canada'
AND [Measures].[Reseller Sales Amount] >15000000 THEN 'North America-OK SALES'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='France'
AND [Measures].[Reseller Sales Amount] >4000000 THEN 'Europe-GOOD SALES'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='Germany'
AND [Measures].[Reseller Sales Amount] >4000000 THEN 'Europe-GOOD SALES'
ELSE 'All Other'
END
SELECT
{ ContinentRollUP
, ContinentRollUP2
,[Measures].[Reseller Sales Amount]
} ON COLUMNS
,{
[Sales Territory].[Sales Territory Country].Members
} ON ROWS
FROM [Adventure Works]
This query's results are illustrated below. Notice how the second member returns the result of a two pronged comparison.
All the above examples have kept the current MDX query within the dimension being used for the query. However, when we remove our Sales Territory Country attribute from the rows axis and introduce an alternate dimension into the query, as displayed below, the results may not be what is expected. For this query, we have replaced the Sales Territory Country dimension attribute with the Calendar Year attribute.
WITH MEMBER ContinentRollUP AS
CASE
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='United States' THEN 'North America'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='Canada' THEN 'North America'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='France' THEN 'Europe'
WHEN [Sales Territory].[Sales Territory Country].CURRENTMEMBER.MEMBERVALUE='Germany' THEN 'Europe'
ELSE 'All Other'
END
SELECT
{ ContinentRollUP
,[Measures].[Reseller Sales Amount]
} ON COLUMNS
,{
[Date].[Calendar Year].Members
} ON ROWS
FROM [Adventure Works]
Now the ContinentRollUP expression is out of scope with the query rows which results in all the values being listed as "All Other: as noted below. Thus, we need to be careful when evaluating dimensions within the case statement. Likewise, if a measure is used, it must also be at the scope level of the query. This situation would not happen in T-SQL as we can still evaluate a value not returned in a Select portion of the query.
Conclusion
The MDX case statement is a powerful tool which can be used to create a logical comparison of values within a MDX query or SSAS calculated measure. The case statement can take two forms: 1) the simple case statement which allows for the comparison of a single value or 2) the search case statement which provides the ability to add a Boolean comparison of one or more expressions. If an expression is evaluated as true, then the value noted is returned. Otherwise, the statement moves on to the next comparison(s), if provided. A final else default value can be provided, so as to return a specified value if all the prior comparisons return false. The case statement can be included in a SSAS calculated measure as part of a cube or as a member within an MDX query. Even so, care must be taken to validate that the appropriate scope level is used in order to return expect results.
Next Steps
- Performance Comparison of Simple and Search Case Statements - http://cwebbbi.wordpress.com/2009/12/09/simple-vs-searched-case-statements/
- SQL Server Business Intelligence Tips - http://www.mssqltips.com/sql-server-business-intelligence-resources/.
- T-SQL Case Statement - http://www.mssqltips.com/sqlservertip/1455/using-the-case-expression-instead-of-dynamic-sql-in-sql-server/.
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
