Simple Case Expression

The simple case expression takes the following form:

<simple_case> ::= CASE <case_operand>
                       <simple_when_clause>...
                       [ <else_clause> ]
                  END

<simple_when_clause> ::= WHEN <when_operand> THEN <result>

<else_clause> ::= ELSE <value_expression>

<case_operand> ::= <value_expression>

<when_operand> ::= <value_expression>

<result> ::= <value_expression>

This clause evaluates the CASE operand and returns a result based on its value, as specified by the WHEN clauses.

The following example demonstrates its use. This example requests the sales of each sales rep across all products and across all regions for FY97. Assume that there is a measure called PerfRating that contains a number that rates each sales rep. The number is from 1 to 5, with 5 being the best. To use an adjective that describes the rating rather than a numerical code, the CASE statement maps each performance rating to terms such as "Excellent" or "Very Poor."

WITH MEMBER [Measures].[Performance] AS CASE [Measures].[PerfRating]
                                     WHEN 5 THEN "Excellent"
                                     WHEN 4 THEN "Very Good"
                                     WHEN 3 THEN "Good"
                                     WHEN 2 THEN "Poor"
                                     WHEN 1 THEN "Very Poor"
                                     ELSE "Unknown"
                                     END
SELECT {[Measures].[Sales], [Measures].[Performance]} ON COLUMNS,
   SalesRep.MEMBERS ON ROWS
FROM SalesCube
WHERE ([1997], [Geography].[All], [Products].[All])

If there is no ELSE specified and none of the WHEN operands match the CASE operand, the result of the CASE is an empty cell value. For more information, see the section Empty Cells.