Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II - Page 3
December 15, 2003
The WITH clause contains several of the required elements that were "filled in" by the Calculated Cells Wizard, as we saw in Part I. Let's look at each in turn to reinforce our understanding.
The first part of the WITH clause function
WITH CELL CALCULATION [Warehouse Cost Scope]
functions just like it did for the creation of a calculated member, as we saw in Part I. It defines the calculated cell (CELL CALCULATION is the syntax), giving it a name (Warehouse Cost Scope). The next line of the WITH clause,
FOR '([Warehouse].[Warehouse Name].MEMBERS)',
equates to the first step of the Calculated Cells Wizard, and is the point of definition of the first of the three required elements for a calculated cell, the subcube (or cube section / region). We are defining our subcube as the Warehouse Names members of the Warehouse dimension. (Note that we kept it simple for the exercise: we can have multiple dimensions specified here, together with additional, more sophisticated syntax for other purposes).
The next line of the clause,
AS 'CalculationPassValue (Warehouse.CurrentMember, 0)',
represents the calculation formula, the second of the three main requirements. As we learned in Part I, the calculation formula is responsible for the ultimate value of the cell once it is evaluated by Analysis Services - and provided that any given cell concerned 1) lies within the subcube definition and 2) passes the conditional test (if any) imposed by the third main part of the calculated cell definition, the condition statement itself. We are not seeking to change the values themselves, merely to highlight them; therefore, we are basically making the cells targeted "equal to themselves." (See the comments in Part I for the corresponding step in the Calculated Cells Wizard, to review the reasoning behind the use of the CalculationPassValue function.)
The last line of the clause,
CONDITION = 'CalculationPassValue ((Warehouse.CurrentMember, [Warehouse Cost]), 0) > 11000',
fills the third (optional) requirement in defining the calculated cell, and supplies the calculated cell condition that is imposed upon the subcube cell selection to determine if the value in the calculation formula is assigned to the respective cells. (The comments in the earlier section surrounding the use of the CalculationPassValue function apply here, as well.)
11. Execute the query using the Run Query button.
The results dataset appears as depicted in Illustration 5.
As we can see, it is difficult to tell if any benefit is obtained through our newly created calculated cell: No tangible evidence of its presence appears. We see no highlighting at this stage of creation, because we have yet to define the setpoints for the cell properties we wish to put in place to make the highlights appear. We will accomplish this in the following steps.
12. Save the query as ANSYS18-2; leave it open, once again, for the next step.
13. Change the comment line to read as follows:
-- Step 3, Calculated Cells Tutorial
14. Immediately following the CONDITION = 'CalculationPassValue ((Warehouse.CurrentMember, [Warehouse Cost]), 0) > 11000', (ensure that there is a comma after > 11000') line of the WITH clause, insert:
FORE_COLOR = '16711935', BACK_COLOR = '0', FORMAT_STRING = '$#,##0.00;($#,##0.00', FONT_FLAGS = '3'
Note: Many of the keywords use underscores - in this case all with spaces between two-worded keywords.