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.
Illustration 5: Results
Set, MDX Query Step 2
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.
» 
Comment and Contribute
