Searched Case

In the simple case expression example, a measure called “PerfRating” exists, which contains a numerical rating from 1 to 5 for each salesperson. Assume that this rating is based on the percentage by which each salesperson beat her budgeted sales. Anything above 100% is a performance rating of 5; from 51% to 100% is 4, from 1% to 50% is 3, from -24% to 0% is 2, and -25% or less is 1.

The simple CASE statement is not capable of expressing this logic because the when operand of the simple WHEN clause is a value expression, rather than a search condition. In the searched case, the when operand is a search condition. Clearly, a case operand is no longer needed.

The construct of the searched case clause is:

<searched_case> ::= CASE
                    <searched_when_clause>...
                    [ <else_clause> ]
                    END

<searched_when_clause> ::= WHEN <search_condition> THEN <result>

The expansion of other nonterminals is the same as that for the simple case. The following example uses searched case to generate a performance rating for each sales rep, based on the logic explained above:

WITH MEMBER [Measures].[PerfTemp] AS 
      ([Measures].[Sales] - [Measures].[BudgetedSales])/
      [Measures].[BudgetedSales]*100
WITH MEMBER [Measures].[PerfRating] AS 
      CASE
         WHEN [Measures].[PerfTemp] > 100 THEN 5
         WHEN [Measures].[PerfTemp] > 50  THEN 4
         WHEN [Measures].[PerfTemp] > 0   THEN 3
         WHEN [Measures].[PerfTemp] > -25 THEN 2
         ELSE 1
      END
SELECT {[Measures].[Sales], [Measures].[PerfRating]}
      ON COLUMNS,
      SalesPerson.MEMBERS ON ROWS
FROM SalesCube
WHERE ([1997], [Geography].[All], [Products].[All])

Note that the WHEN clauses are evaluated in the order specified; that is, the value of CASE is the result of the first searched when clause whose search condition is TRUE. That happens when the second WHEN clause in the above example just checks to see if PerfTemp > 50, rather than checking if PerfTemp > 50 AND PerfTemp <= 100.

As with simple case, if no ELSE is specified, and the search conditions of all the WHEN clauses is FALSE, then the result of the CASE is an empty cell value. For more information, see “Empty Cells” in this chapter.

Finally, the simple case can be rewritten as a searched case. The simple case

CASE case_operand
  WHEN when_operand1 THEN result1
  WHEN when_operand2 THEN result2
  ...
END

is equivalent to the following searched case:

CASE
  WHEN case_operand = when_operand1 THEN result1
  WHEN case_operand = when_operand2 THEN result2
  ...
END