Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 15, 2003

Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II - Page 3

By William Pearson

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

   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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM