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