CREATE CELL CALCULATION Statement
This statement creates a calculated cell formula for a specified set of tuples within a cube.
BNF
<create cell formula> ::= CREATE CELL CALCULATION
<cube name>.<formula name> <formula body> [<conditions>]
| ALTER CUBE <cube name> CREATE CELL CALCULATION
<formula name> <formula body>
<with cell formula> ::= WITH CELL CALCULATION <formula name>
<formula body>
<formula body> ::= FOR '(<set description clause>)' AS '<formula clause>'
[, <cell property list>]
<cell property list> ::= <condition property> <disabled property> <description property>
<pass number property> <pass depth property>
<condition property> ::=[CONDITION = '<Conditions Expression>']
<condition expression> = <boolean member expression> [ & <condition expression>]
<disabled property> = [, DISABLED = {TRUE | FALSE}]
<description property> = [, DESCRIPTION = '<user-friendly description>']
<pass number property> ::= [, CALCULATION_PASS_NUMBER = <long integer>]
<pass depth property> ::= [, CALCULATION_PASS_DEPTH = <long integer>
Remarks
By using calculated cells, the client application can specify a rollup value for a particular set of cells, instead of for an entire set of cells as in the case of a custom rollup formula or a calculated member. For example, it is possible to specify that any cell in the slice defined by {[Canada],[Time].[2000]} can contain a value that is defined by a formula. Any other cells that are not contained within this slice would be computed normally.
Examples
A. Creating a Calculated Cell with a Condition Clause
The following example creates a calculated cell that adjusts values for cities in Mexico. It contains a condition that causes the formula to be applied only to members that reside in the year 2000 time period.
CREATE CELL CALCULATION [Sales].[Mexico Adjustments]
FOR '(Descendants([Mexico], [City], SELF))'
AS '<expression>',
CONDITION = '[Time].CURRENTMEMBER.NAME=[2000]'B. Creating a Calculated Cell Without a Condition Clause
The same calculated cell can be defined by moving the CONDITION clause into the set description clause. This is the recommended method and is demonstrated in the following example:
CREATE CELL CALCULATION [Sales].[Mexico Adjustments]
FOR '(Descendants([Mexico], [City], SELF), {[2000]})'
AS '<expression>'