MDX Function and Operator Reference - Professional SQL Server Analysis Services 2005 with MDX - page 1048

This operator provides a multiway conditional test for values to return. It is
similar to the iif () function, but can handle more conditions. The different im-
plementations provide different capabilities.
The expression in the first form can be either a numeric expression or a string
expression, and each test must the same type (numeric or string). Each test
from test1 through testN is evaluated in sequence until the result of a test is
equal to the reference-expression. When that happens, the corresponding
result following the THEN clause is returned. If no test expression is equal to
reference expression , then the DefaultResult , if any, is returned. If none is
specified, then the result of the function is NULL.
In the second form of CASE, each testExpr is evaluated as a Boolean expres-
sion in sequence until one evaluates as true. The result expression of the cor-
responding THEN clause is returned. If no testExpr evaluates to true, then the
DefaultResult , if any, is returned. If none is specified, then the result of the
function is NULL.
In AS2005, the type of result from any THEN clause can be different from that
of any other THEN clause. A single CASE operator could return strings, num-
bers, and/or any of the other types listed depending on the reference-expres-
sion and/or test expressions. When no test evaluates to true and there is no
default clause, a NULL is returned.
AS2005 allows the CASE expression to serve as an axis expression or as the
input to other functions that use sets, such as Avg () or Generate (). For
AS2005, since CASE can return most MDX objects, you can write expres-
sions such as the following, which uses the CASE construct to pick an attrib-
ute dimension whose members end up in a named set [ASet]:
WITH SET [ASet] AS
CASE
WHEN condition1
THEN [Customer].[Region]
WHEN condition2
THEN [Customer].[State]
ELSE [Customer].[City]
END .Members
...