Calculated cells let you vary numerical accuracy on a cell-by-cell basis
"Setting Display Characteristics" (March 2001) discussed cell properties, which control the display characteristics of cube cells. By customizing cell properties, you can control whether the content of a particular cube's cell displays in bold, italics, or a particular foreground or background color. You can also go deeper in your customization and control each cell property. In particular, let's look at how to control each cell's numeric scaling and accuracy.
Economists at the World Bank Group work with data in a range of values. Two primary challenges in presenting this information are appropriate formatting (the number of decimal places) and scaling (e.g., thousands, millions, billions). The solution we propose uses calculated cells to control the cell's Format String. First, let's briefly review calculated cells, then explore how to use calculated cells to alter the values in each cell based on conditional criteria.
Calculated Cells In Brief
A calculated cell uses an MDX formula to calculate the cell's value at runtime. A calculated cell can also include an MDX condition, which determines whether the cell should apply the MDX formula. Calculated cells offer more flexibility in defining which cells to modify than do calculated members, custom members, or custom rollup formulas. (e.g., a calculated cell formula can affect anywhere from one cell to an n-dimensional cube).
To construct a calculated cell, you must define three properties: a calculation subcube, a condition, and a value. When you define a calculated cell on a server, you can use the Decision Support Object (DSO) model or the Microsoft Management Console (MMC) Analysis Manager snap-in to set the three properties programmatically (we used the MMC version). After you define these properties, you can set others (e.g., exception-highlighting properties, the Format String property).
Calculated cells can control the BackColor, ForeColor (font color), FontName, FontSize, and FontFlags (e.g., italic, bold) cell properties if you apply the same exception highlighting that's in Microsoft Excel and other reporting tools. However, calculated cells are much more powerful for altering exception highlighting on a cell-by-cell basis because of their foundation in MDX. In addition, when calculated cells are on the server, centralized control of the exception-highlighting rules simplifies maintenance and control.
The Business Problem
The World Bank collects economic, environmental, and social data about developed and third-world countries around the globe. Economists consider this and other information when they make economic decisions that guide developing countries onto paths of more stable, sustainable, and equitable growth. Two of the primary challenges in presenting this information are appropriately formatting and scaling, both of which can vary by cell.
For example, the World Bank might need to report the Gross Domestic Product (GDP) for the United States in trillions and with three decimal places; however, the requirements for a smaller country might be to report the GDP in billions with only one decimal place. The World Bank wants each of its clients to automatically see its country's data in the correct scale and format. So, the World Bank loaded a measure with information that SQL Server 2000 Analysis Services could use to correctly scale and format cube measures.
A calculated member can provide the scaled value, but formatting that value appropriately is a different problem. The number of decimal places you display can change from cell to cell, so using calculated cells to solve this problem makes sense. Why can't you use a simple calculated member to solve this problem, setting Format String to the appropriate number of decimal places? If you have a calculated member and set Format String to #,#.000, all the values that the calculated member returns have the same three-decimal-place accuracy, as Figure 1, page 64, shows. But you might need to vary the accuracy displayed for an individual value. The World Bank needs to display some values with different numbers of decimal places, as Figure 2, page 64, shows.
The views from scaling and formatting perspectives differ. The cell's value changes in scale and accuracy. When the scaling factor changes by a power of 10 (e.g., the GNP at market prices for Botswana in Figures 1 and 2), both cells might have the same value, but the World Bank displays them with different scales and accuracy.
To achieve the appropriate scale with calculated cells, you can apply the following MDX calculation:
Measures.[Value]/10^INT(Measures.[Scale])
where Measures.[Value] is the raw value of the GDP as it is in the fact table and Measures.[Scale] is the loaded measure specifying this value's scale and accuracy. In this example, Measures.[Value] is 4,869,069,537 and Measures.[Scale] is 6.1. If you apply the simple MDX calculation
4,869,069,537/10^6.1 = 4,869
you get the answer's whole number. But how do you get the correct number of decimal places? Applying Format String to a calculated member affects all the cube's returned values, but the accuracy needs to vary by cell. Therefore, you use calculated cells.