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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

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 2

By William Pearson

Calculated Cells in an MDX Query

As we learned in Part I, the value within a calculated cell is computed at run time through an MDX expression, which is specified when the calculated cell is defined. The expression can be conditionally applied to a cell or range of cells, based upon an MDX logical expression, which would also be specified at the point of definition of the calculated cell.

We explored the means of creating a calculated cell from the Analysis Manager, and practiced various aspects of the creation of a calculated cell in an illustrative example, in our last lesson. Our interaction with calculated cells at that juncture was through the graphic interface of the Analysis Manager, from which we input some of the supporting MDX syntax, via the appropriate Member Properties fields. In this lesson, we will perform all the steps for calculated cell creation directly in MDX.


As in the process of creating a calculated member within an MDX query (see my various DatabaseJournal articles, index page, for a listing), the WITH clause comes into play: We can also use the WITH clause to define calculated cells and their properties. Alternatively, calculated cells can be created in an MDX query using the CREATE CELL CALCULATION statement, but we will focus on the WITH clause approach. This would be a typical approach for creating a calculated cell with session scope, in contrast to the global scope one would expect to provide with a calculated cell created by using the Calculated Cells Wizard or by using the CREATE CELL CALCULATION statement. We discussed session and global scope in Part I.

Creating a Calculated Cell in MDX

We will begin our creation of a calculated cell by firing up the MDX Sample Application once again, as it provides a platform that is available to most MSAS users from which to build MDX queries.

1.  Go to the Start button on the PC, and then navigate to Microsoft SQL Server --> Analysis Services, then to the MDX Sample Application.

We are initially greeted by the Connect dialog, shown in Illustration 1 below.

Illustration 1: The Connect Dialog for the MDX Sample Application

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2.  Click OK.

(We might also choose to cancel the dialog box and connect later by clicking Connect on the File menu.)

The MDX Sample Application window appears.

3.  Clear the top area (the Query pane) of any remnants of queries that might appear.

4.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

5.  Select the Warehouse cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that shown in Illustration 2, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).

Illustration 2: The MDX Sample Application Window

We will create an MDX query that defines and creates a calculated cell, and that also returns cell properties as a part of the query result. Let's start with a simple core query, upon which we will base our work in the steps of the practice example.

6.  Type the following core query into the Query pane of the sample application:

-- Step 1, Calculated Cells Tutorial
SELECT  {[Measures].[Warehouse Cost]} ON COLUMNS, 
    [Warehouse].[Warehouse Name].Members ON ROWS 
FROM Warehouse

7.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 3.

Illustration 3: Results Set, Core Query

8.  Save the query as ANSYS18-1, leaving it open for the next step.

Now we will add a WITH clause, containing the definition of a calculated cell, which, for this exercise, we will call "Warehouse Cost Scope."

9.  Remove the comment line (-- Step 1, Calculated Cells Tutorial) from the top line of the query.

10.  Add the following syntax into the Query pane, before the SELECT clause:

-- Step 2, Calculated Cells Tutorial
   CELL CALCULATION [Warehouse Cost Scope] 
   FOR '([Warehouse].[Warehouse Name].MEMBERS)'
   AS 'CalculationPassValue (Warehouse.CurrentMember, 0)',
      CONDITION = 'CalculationPassValue ((Warehouse.CurrentMember, 
          [Warehouse Cost]), 0) > 11000'

The full query should now appear in the Query pane as shown in Illustration 4.

Illustration 4: Creating the Calculated Cell via the WITH Clause

MS SQL Archives

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