Using WITH to Create Calculated Cells
Similar to the way it is used in calculated members, the WITH keyword in Multidimensional Expressions (MDX) is used to describe calculated cells.
The following syntax is used to add the WITH keyword to the MDX SELECT statement:
[WITH <formula_specification>]
[, <formula_specification>]
SELECT [<axis_specification>
[, <axis_specification>...]]
FROM [<cube_specification>]
[WHERE [<slicer_specification>]]
The <formula_specification> value for calculated cells is further broken out in the following syntax definition:
<formula_specification> ::= CELL CALCULATION <formula_name>
FOR '(<calculation_subcube>)'
AS '<calculation_formula>'
[,<calculation_property_list>]
The <cell_property_list> is further defined by the following syntax:
<cell_property_list> ::= <property_name = '<value>'
[, <property_name> = '<value>'...]
The <formula_name> value is the name of the calculated cells. The <calculation_subcube> contains a list of orthogonal, single-dimensional MDX set expressions, each of which must resolve to one of the following categories of sets.
| Category | Description |
|---|---|
| Empty set | An MDX set expression that resolves into an empty set. In this case, the set is ignored. |
| Single member set | An MDX set expression that resolves into a single member. |
| Set of level members | An MDX set expression that resolves into the members of a single level. An example of this is the «Level».Members MDX function. To include calculated members, use the «Level».AllMembers MDX function. |
| Set of descendants | An MDX set expression that resolves into the descendants of a specified member. An example of this is the Descendants(«Member», «Level», «Desc_flags») MDX function. |
If a dimension is not described in the <calculation_subcube> argument, it is assumed that all members are included for the purposes of constructing the calculation subcube. Therefore, if the <calculation_subcube> argument is NULL, the calculated cells definition applies to the entire cube.
The <calculation_formula> argument contains an MDX expression that evaluates to a cell value for all of the cells defined in the <calculation_subcube> argument.
The <calculation property list> argument contains a list of member properties to be applied to the cells specified in the <calculation_subcube> argument.
The following properties apply specifically to calculated cells.
| Property | Description |
|---|---|
| CALCULATION_PASS_DEPTH | The pass depth for the calculation formula, this property determines how many passes are needed to resolve the calculation formula. For more information about pass order, see Understanding Pass Order and Solve Order. |
| CALCULATION_PASS_NUMBER | The pass number for the calculation formula, this property determines on which pass the calculation formula will begin calculation. The default for this property is 1. For more information about pass order, see Understanding Pass Order and Solve Order. |
| CELL_EVALUATION_LIST | The semicolon-delimited list of evaluated formulas applicable to the cell, in order from lowest to highest solve order. For more information about solve order, see Understanding Pass Order and Solve Order |
| CONDITION | The calculation condition of the calculated cells, this property receives an MDX logical expression, which is evaluated on each cell in the calculation subcube. If it returns True, the calculation formula is applied and the cell returns the resulting value. If it returns False, the cell returns the original cell value. If not specified, CONDITION defaults to True (in other words, the calculation formula applies to all cells in the calculation subcube). |
| DESCRIPTION | A human-readable text description of the calculated cells definition. |
| DISABLED | A Boolean property which indicates whether or not the calculated cells are disabled. DISABLED defaults to False. |
Other standard cell properties, such as FORE_COLOR and BACK_COLOR, can be used as well.
For more information about using cell properties and using member properties, see Using Cell Properties and Using Member Properties.
Additional Considerations
The calculation condition, specified by the CONDITION property, is processed only once, depending on the creation scope of the calculated cells definition. This provides increased performance for the evaluation of multiple calculated cells definitions, especially with overlapping calculated cells across cube passes.
If created at global scope, as part of a cube, the calculation condition is processed when the cube is processed. If cells are modified in the cube in any way, and the cells are included in the calculation subcube of a calculated cells definition, the calculation condition may not be accurate until the cube is reprocessed. This can occur through the use of writebacks, for example. The calculation condition is reprocessed when the cube is reprocessed.
If created at session scope, the calculation condition is processed when the statement is issued during the session. As with calculated cells definitions created globally, if the cells are modified, the calculation condition may not be accurate for the calculated cells definition.
If created at query scope, the calculation condition is processed when the query is executed. The cell modification issue applies here, as well, although data latency issues are minimal at best due to the low processing time of MDX query execution.
The calculation formula, on the other hand, is processed whenever an MDX query is issued against the cube involving cells included in the calculated cells definition, no matter the scope.