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 article, we will perform 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.

Read the Article ...

About the Series:

This article is a member of the series Introduction to MSSQL Server 2000 Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, 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.

MSAS, MSSQL Server Analysis Services, Cube, Calculated Cell, Cell, MDX , Subcube, Condition, Formula, Session, Global , Scope, CalculationPassValue(), ForeColor, BackColor, RGB, FontName, FontSize, FontFlag, Properties

All Contents Copyright Island Technologies Inc. ^®  Island Technologies^® and Island Technologies Inc.^®  are registered trademarks of Island Technologies Inc., protected in the United States and other countries.  For information, contact Island.