Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Introduction to Calculated Cells

In the last two articles of this series, "Building Calculated Members," we completed a review of many aspects of creating and using calculated members. In the first of the two articles, we practiced the creation of dynamic calculated members, both on measure and non-measure dimensions, comparing and contrasting the two general types in a way that makes selection and planning for real-world uses straightforward in most respects. We discussed ways of handling the creation of calculated members with MDX, and practiced adding multiple members in a single MDX query.

We exposed a means of controlling the order in which calculated members are solved, as well as discussing why precedence of calculation might be critical to generating expected results. As we launched the second article, we moved into the realm of aggregation functions, practicing with cumulative totals and other uses. Finally, we drew upon our exposure to member functions in earlier articles to calculate values based upon the retrieval of member properties with our calculated members, completing practice examples that demonstrated the potential value of this capability.

In this article, we will explore calculated cells, which enable us to apply functionality previously reserved for calculated members, custom members, and custom rollup formulas to a specific range of cells—or even to a single cell. We will describe the construction of a calculated cell, touching upon the basic properties that make it up.

Although we will discuss the creation of calculated cells with a global scope, we will focus mainly on their creation with a session scope, which limits their availability to MDX queries that meet specific criteria. In addition, we will practice the use of the WITH statement in these queries to create calculated cells, while discussing how a calculated cell created in this way offers greater flexibility for client applications in many cases.

Finally, we will create and manipulate calculated cells within MDX set expressions, and will expose the procedures behind applying an MDX value expression selectively and providing support for exception highlighting. Our objective will be to conditionally apply the expression to specific cells, based upon an MDX logical expression.

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

  • Create a calculated cell via the Cube Editor that provides for exception highlighting in an example business need scenario

  • Create a calculated cell via an MDX query to provide a parallel example of exception highlighting to meet select information consumer requirements

  • + Share This
  • 🔖 Save To Your Account