Searched Case Expression

In the simple case expression example, a measure called PerfRating contains a numerical rating from 1 to 5 for each sales rep. This rating is based on the percentage by which each sales rep surpassed 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, however, the WHEN operand is a search condition; therefore, a CASE operand is no longer needed. The construct of the searched case clause is as follows:

<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 and the logic explained in the preceding paragraph to generate a performance rating for each sales rep:

WITH MEMBER [Measures].[PerfTemp] AS
   ([Measures].[Sales] - [Measures].[BudgetedSales])/
   [Measures].[BudgetedSales]*100
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,
   SalesRep.MEMBERS ON ROWS
FROM SalesCube
WHERE ([1997], [Geography].[All], [Products].[All])

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 checks only to see whether PerfTemp > 50 rather than whether PerfTemp > 50 AND PerfTemp <= 100.

As with the simple case, if no ELSE is specified and the search conditions of all the WHEN clauses are false, the result of the CASE is an empty cell value. For more information, see Empty Cells.

Finally, the simple case can be rewritten as a searched case. The simple case is shown first:

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

The following statement shows the equivalent searched case:

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