4. Place the following two-parameter numeric format string in the Format String property box:
The above example has two sections: The first section dictates the format for positive values and zeros; we have included the dollar ($) sign to indicate that we wish to have it precede the number in the cell, the # characters to act as digit placeholders, and the thousand separator (,) and decimal placeholder (.) information in this section. The second section, in our example, defines the format for negative values. We have selected parentheses as the negative number indicator (instead of the negative sign). The other characters are set to behave as they do in the first section.
As we have discussed, the Format String property options are voluminous and far-reaching. For more information on them, as well as the other properties' settings, see the Books Online (installed with MSSQL 2000 Analysis Services, or available on the web and elsewhere).
The Properties pane - Advanced tab with our additions appears, as shown in Illustration 13.
Illustration 13: The Properties Pane - Advanced Tab with Additions
5. After filling in the Properties boxes above, press the Enter key (clicking outside the Properties pane also refreshes the Data viewing pane).
The partial results set returned in the Data viewing pane should resemble those shown in Illustration 14.
Illustration 14: Partial Results in the Data Viewing Pane, with Calculated Cell Effects
6. Save the modified cube, as desired.
We see that the new calculated cell has accurately performed its work, highlighting those Warehouse Costs exceeding our $11,000 threshold. We can almost certainly go further in determining information consumer requirements in our own working environments, such as to ascertain whether consumers would want to see highlighted "rollup" values (as a means of management and/or analysis by exception at a higher level), for totals whose membership contained highlighted exceptions, and so forth. In addition, there are many other settings that can be used for sophistication and flexibility in our displays. (Books Online serves as a great place to start exploring these powerful options.)
In this lesson, we explored calculated cells, which comprise functionality that is new with MSSQL Server 2000 Analysis Services, and which was previously reserved for calculated members, custom members and custom rollup formulas. Calculated cells allow us to apply formulas and property settings to a specific range of cells or even to a single cell, and optionally to base these assignments upon conditional criteria that we can stipulate in the creation of the calculated cell. We explored the construction of a calculated cell, touching upon some of the basic properties that make it up, as a part of our creating a calculated cell.
We manipulated calculated cells within our practice example, and exposed the procedures behind applying an MDX value expression selectively, and providing support for exception highlighting. We practiced the application of the expression to specific cells, based upon an MDX conditional expression.
We discussed the creation of calculated cells with a global scope, and practiced this "permanent" calculated cell creation process within the Calculated Cells Wizard. In our next session together, we will focus on calculated cells with a session scope, when we take up their creation using an MDX query. Within the lesson, we will practice the use of the WITH statement in creating calculated cells, noting that a calculated cell created in this way offers greater flexibility for client applications, in many cases.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.