Let's focus now on the
capabilities of the calculated cell to perform exception highlighting.
For this example, we will create a calculated cell that highlights all USA
warehouse locations with Warehouse Costs exceeding $11,000 for 1997. The first step of defining the new calculated
cell is to define the range of cells in the cube, also known as the subcube
(or cube region) upon which the calculated cell will act.
17.
Highlight/select the Warehouse
dimension from the list on the left side of the Calculation Subcube
dialog box.
18.
From the Members Set drop-down
selector on the right side of the dialog box, select A Single Level.
19.
Expand the Warehouse
dimension tree, and select Warehouse Name.
The dialog box appears
as shown in Illustration 6.
The Define the
Calculation Condition dialog box appears.
At this stage, the
wizard prompts for the calculated cell condition. We want to put
highlighting in place for warehouse locations with Warehouse Costs exceeding
$11,000 for 1997. To establish the condition for the application of
highlighting, we will input the statement at this stage.
21.
Click the second radio button (Apply
the Calculation Formula to Cells in the Calculation Subcube that Meet the
Following Conditions) to select it.
22.
Enter the following into the MDX
Expression text box:
CalculationPassValue((Warehouse.CurrentMember,
[Warehouse Cost]), 0) > 11000
Our condition formula
uses the CalculationPassValue() function to ascertain the value of the
cell before we apply the calculated cell. We are using this approach as a means
of preventing the expression from being recursive, or referencing
itself, as would normally be the case in a situation like ours (where the
condition expression reads the value of the current cell, which itself
references the calculation formula again). Because multiple passes are required
for evaluation of MDX expressions, and because we know that evaluations of
calculated cells will occur beyond pass 0, we tell Analysis Services to stop
the evaluation at pass 0 (by placing 0 in the second argument position) and
return the associated value. This mainly serves to prevent the expression from
referencing itself ad infinitum, as if in a loop.
The completed Define
the Calculation Condition dialog box appears, as shown in Illustration 7.
Illustration 7: The
Completed Define the Calculation Condition Dialog Box
The Define the
Calculation Formula dialog box appears.
At this point, we are
prompted to enter the expression for the calculation itself. The outcome of
this formula will be the value that is assigned to the cells within the
selected range, after evaluation of those cells via the condition we have
assigned the calculated cell. Remember that the condition is a logical
expression, and that it therefore evaluates to True or False;
it is applied to every cell in the range defined in the calculation subcube.
For any cell for which the condition evaluates to True, the result of
the calculation formula applied to that cell is returned when the cell is
queried. A False condition, by contrast, returns the actual value of
the cell when it is queried. (Much of the power in calculated cells is that
no real alteration of the data itself is taking place).
24.
Enter the following into the MDX
Expression text box:
CalculationPassValue(Warehouse.CurrentMember, 0)
The completed Define
the Calculation Formula dialog box appears, as shown in Illustration 8.
Illustration 8: The
Completed Define the Calculation Formula Dialog Box
» 
