"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.
The Solution
Format String differs from other cell properties because it can't have an MDX formula as a value (e.g., ForeColor lets you use an MDX conditional formula to change the font color, depending on the cube cell's contents or location). Format String allows only one format per calculated member or calculated cell. Therefore, you need four calculated cell definitions, each with its own Format String, to display a value that has the correct number of decimal places. You need four definitions because the World Bank data can have one of four possible levels of accuracy: 0, 1, 2, or 3 decimal places. If the data required more levels of accuracy, you would need more definitions.
This calculated cell solution has two parts. First, the loaded measure from the fact table, Measures.\[Scale\], specifies scaling and accuracy. This measure has values such as 0.2, 0.3, 6.1, 6.2, 9.1, 9.3 (where the integers before the decimal point specify what power of 10 to apply to the cell's value, and the number after the decimal point specifies how many decimal places to display). The number's decimal portion can have only values 0 through 3 (i.e., up to three decimal places). Second, you use a calculated member (part of the Measures dimension) to perform the scaling calculation and display the result. This value, Measures.\[Display Value\], has the following MDX formula:
Measures.\[Value\]/10^INT(Measures.\[Scale\])
Note that the calculated cells reference this calculated member. The calculated cells don't reference a loaded measure directly because the fact table doesn't include only one value for each fact record.
A fact record consists of five values—Yval, Qval, Mval, Wval, and Dval—the combination of which is one measure from the user's point of view. Analysis Services loads each of these values into a different measure, and the calculated measure—Measures.\[Value\]—picks the appropriate value depending on the current member of the Time dimension. The following MDX formula calculates Measures.\[Value\]:
IIF(Time.CurrentMember.Level.Ordinal = 1, Measures.Yval, IIF(Time.CurrentMember.Level.Ordinal = 2, Measures.Qval, IIF(Time.CurrentMember.Level.Ordinal = 3, Measures.Mval, IIF(Time.CurrentMember.Level.Ordinal = 4, Measures.Wval, IIF(Time.CurrentMember.Level.Ordinal = 5, Measures.Dval,NULL)))))
The four calculated cell definitions are ScaledVal0Decimal, ScaledVal1Decimal, ScaledVal2Decimal, and ScaledVal3Decimal; the names reflect the number of decimal places that each calculated cell handles. Each definition has the same Calculation Subcube and Calculation Value. However, Calculation Condition and Format String differ from one calculated cell definition to the next.
Creating calculated cells is a simple process. The Analysis Manager Calculated Cells Wizard is easy to use, even though the calculated cell concept can be difficult to understand. SQL Server Books Online (BOL) has a calculated cell overview that contains several complicated cube diagrams. Calculated cells, like calculated members, are parts of a cube, and you use the cube editor to add or modify them. Within the cube editor, the left pane shows the folders for Dimensions, Measures, Calculated Members, Calculated Cells (available only in Analysis Services Enterprise Edition), Actions, and Named Sets. Select the Calculated Cells folder, right-click, and select New Calculated Cells to launch the wizard.
To define the calculation Subcube, you use the calculated measure \[Measures\].\[Display Value\], but Analysis Manager can't display calculated measures—only loaded measures, as Figure 3 shows. So, you need to choose a loaded measure to use as a placeholder. When you finish using the wizard, you need to edit the Subcube property directly to designate Measures.\[Display Value\].
The next step in the wizard is to designate the calculation condition, which determines the number of decimal places to display for each cell (the decimal portion of Measures.\[Scale\] provides the input for this condition). If the cell meets the condition, the cell will use this calculated cell definition and apply the related Format String to the cell. The first calculated cell definition applies to those values that display no decimal positions (i.e., where the decimal portion of Measures.\[Scale\] equals 0).
For example, the calculation condition in the first calculated cell definition checks the decimal portion of Measures.\[Scale\] for a condition of less than .09, as the following MDX formula shows:
Measures.\[Scale\] - ROUND(Measures.\[Scale\],0)When Measures.\[Scale\] is 6.0, the following value is the result:
6.0 - 6 = 0The 0 value stands for 0 decimal places and is exactly the condition you want for this cell.
Next, the Calculated Cells Wizard requests the calculation formula, or calculation value property. You can use the same calculation you used for the calculated member Measures.\[Display Value\]:
Measures.\[Value\]/10^INT(Measures.\[Scale\])Then, you name the calculated cell and terminate the wizard.
Now, you need to set some calculated cell properties that the wizard doesn't set. The first property to change is the Calculation Subcube. The wizard required that you choose a loaded measure and use it as a placeholder for the Subcube. The loaded measure chosen earlier was unimportant and acted as a placeholder for our Subcube definition. You change the Subcube property to the calculated member Measures.\[Display Value\], as Figure 4 shows, but don't forget to encapsulate it in curly braces—\{\[Measures\].\[Display Value\]\}—because the calculated member is a set definition.
The second property to change is Format String, which you can find under the Advanced tab of the Properties window. Format String doesn't have predefined choices for selection, so you must type the value, as Figure 5 shows. For calculated cells that have 0 decimal places, Format String has a value of #,#.
The first calculated cell definition is complete. Now, you define the other three definitions, one for each of the other decimal place definitions. The Calculation Subcube and Calculation Value remain the same for each calculated cell, but the Calculation Condition and Format String change. Table 1 shows the values for the Calculation Condition and Format String for all four calculated cells.
Cell-by-Cell Accuracy
You can vary the accuracy of displayed numbers on a cell-by-cell basis by defining multiple calculated cells on the same range of cells, each with a different Calculation Condition and Format String. The ability to vary cell properties or values on a cell-by-cell basis is a strength of Analysis Services' calculated cells. Formatting isn't the only way to use calculated cells. To know when to use calculated cells, ask the question, Will values in the cube change within the cell's context? If the answer is yes, calculated cells are probably the right choice. (For the answer to the May puzzle, see the Web sidebar "May MDX Puzzle Solution Revealed," InstantDoc ID 20988. For the June puzzle, see the Web sidebar "June MDX Puzzle," InstantDoc ID 20989.)
