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) <= .09
When Measures.[Scale] is 6.0, the following value is the result:
6.0 - 6 = 0
The 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.)
End of Article
Prev. page
1
2
[3]
next page -->
