## Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part INovember 17, 2003 ## About the Series ...This is the seventeenth
article of the series, , 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 ("Creating Our First CubeMSAS"),
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
. Samples## IntroductionIn this article, we
will explore In this lesson, we will do the following: - Overview calculated cells and discuss some of the advantages and benefits that attend their use
- Discuss the properties that must be defined to create a calculated cell
- Discuss several optional property options that can be selected after definition of the calculated cell
- Overview briefly the practical use of Microsoft Windows operating system red-green-blue format (RGB) values
- Modify a calculated cell to enable exception highlighting in an example business need scenario
## Calculated Cells in Analysis ServicesThe value within a calculated cell is computed at run time through a specified MDX expression. The expression 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. In these cases, the logical expression is also specified at the point of definition of the calculated cell. ## OverviewThe advent of MSSQL
Server 2000 Analysis Services witnessed the arrival of Calculated cells consist of three main elements: - Calculation subcube
- Calculation condition
- Calculation formula
The calculation -
All members of a dimension
(including the
**Measures**dimension): Calculated members can be included (using the**.AllMembers**MDX function). -
A single specified member of a
dimension (
**Measures**, again, is included). -
All members at a specified
level within a dimension: Calculated members can be included (again, using the
**.AllMembers**MDX function). - The descendants of a specified member within a dimension.
- The descendants of a specified member at a specified level within a dimension.
- An MDX expression that generates a set containing one of the above sets.
This list of dimension sets, combined with the default member of all other unspecified dimensions in the cube, defines the calculation subcube. Within the subcube, the
True, and the cell returns the
calculated value. The member cell returns its original value if the calculation
condition evaluates as False. The combination of the calculated cells
condition and the calculation subcube is termed the calculation scope.The third component of
a calculation cell, the To summarize the
interaction between the parts of a calculated cell, we define a specific |