Advanced Topics in MDX - Professional SQL Server Analysis Services 2005 with MDX - page 290

ment. Examples of assignment_expression include MDX expressions as-
signed to a subcube or calculation properties applied to a subcube. END IF
indicates the end of the IF statement. In the preceding example, you first as-
sign values to all the cells corresponding to the subcube to be 1.3 times the
value of the Sales amount in the previous year. Later you have the conditional
IF statement to update the cell values corresponding to the first quarter to be
two times the Sales Amount.
You can get into more complex expressions which may require multiple IF
statements which can lead to updating cells multiple times. The cube space is
large, and applying cell calculations on a large cube space can lead to per-
formance degradation. Hence, Analysis Services provides the CASE expres-
sion to perform assignments.
The syntax for the CASE expression is:
Statement =
CASE <value_expression>
WHEN <value_expression> THEN <statement>
ELSE <statement>
END;
Here an MDX statement is assigned one of the values being returned by the
CASE expression. Assume for the Fiscal Year 2004, you need to specify the
Sales Amount Quota based on some condition, such as the Sales Quota for
the first quarter must be 1.3 times the previous year's sales amount, for the
second quarter the quota is 2 times the previous year's sales amount, and for
the third and fourth quarters the quota is 1.75 times the previous year's sales
amount. You can specify this condition easily using the CASE statement as
follows:
SCOPE
([Date].[Fiscal Year].&[2004],
[Date].[Fiscal].[Month].Members,
[Measures].[Sales Amount Quota]);
This =
CASE WHEN ([Date].[Fiscal].Currentmember.Parent is
[Date].[Fiscal].[Fiscal
Quarter].&[2004]&[1])
THEN