About the Series ...

This is the eighteenth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("MSAS"), with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.


In this article, we will extend the exploration of calculated cells that we began in our last session, Using Calculated Cells in Analysis Services, Part I. In Part I we learned that calculated cells offer functionality previously reserved for calculated members, custom members, and custom rollup formulas (all of which we have explored in previous articles) to a specific range of cells-or even to a single cell. We will revisit the construction of a calculated cell, touching upon another means of creating one: through an MDX query.

In this lesson, we will do the following:

  • Overview the creation of calculated cells from the perspective of an MDX query
  • Discuss two approaches to the creation of a calculated cell via an MDX query
  • Discuss the ramifications of the two approaches, within the context of scope assigned by each
  • Practice creation of a calculated cell using the WITH clause in an MDX query
  • Parallel the methods within the MDX query to mirror the actions we performed in Using Calculated Cells in Analysis Services, Part I within the Analysis Services Manager and associated subcomponents, including:
    • creation of a calculated cell;
    • formatting of calculated cell contents;
    • manipulation of other cell properties, such as FORE COLOR and BACK COLOR, to enable exception highlighting.