Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I - Page 3
November 17, 2003
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.
Click for larger image
20. Click Next.
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.
23. Click Next.
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:
The completed Define the Calculation Formula dialog box appears, as shown in Illustration 8.