Cell properties let analysis applications support exception highlighting
Exception highlighting, or conditional formatting, lets you apply different display characteristics to various parts of a query result. For example, you might display profitability by product and highlight negative profit numbers (i.e., products that are losing money). Exception highlighting can draw attention to important numbers in a printed report, making the numbers bold or displaying them in a particular color. Highlighting is also important in an online, interactive analysis application. This month, I discuss SQL Server 2000 Analysis Services' cell properties, a powerful feature that lets analysis applications support exception highlighting.
In analysis applications that support them, cell properties let you control the display characteristics of numeric results. You can use three techniques to configure cell properties: calculated cells, calculated members, and custom member options. If you define a cube on the OLAP server, you have access to all three techniques. Defining cell properties on the server is extremely powerful because you define the criteria for displaying numbers only once for the many applications that might draw information from a central OLAP cube. However, a desktop application can use only two of the techniques, calculated cells and calculated members, to configure exception highlighting on a query-by-query basis.
Calculated Cells
The calculated cells technique uses a formula that determines a cube's cell value, a condition that the cell must meet to apply the formula, and a region of the cube in which the calculated cell applies. A calculated cell's definition might also include cell properties information. Like the calculated cells formula, the cell properties information applies to an individual cell only if that cell meets the calculated cell's condition. The cell properties information might include a numeric format string, foreground color, background color, font name, font size, and font flags (i.e., bold, italics, underline, or strikethrough). You can define calculated cells on the server by using Analysis Manager or on the client by using MDX.
Calculated Cells through Analysis Manager. Analysis Manager's Cube Editor includes a Calculated Cells Wizard to help you create calculated cells. For example, let's create a calculated cell that highlights all nonconsumable products with Unit Sales greater than 12,000. Start Analysis Manager, right-click the FoodMart 2000 Sales sample cube, and select Edit to start the Cube Editor. In the Cube Editor window, drop down the Insert menu and select Calculated Cells, which launches the Calculated Cells Wizard.
The first step in the wizard is to define which subcube, or cube region, the calculated cell will affect. To choose the nonconsumable product member, select the Product Dimension from the list on the left, then select the Members Set type A single member on the right. Next, expand the All Products dimension tree to the second level, and select Non-Consumable, as Figure 1 shows. When you select Next, the wizard prompts you for the optional calculated-cell condition. To apply highlighting only when Unit Sales for the nonconsumable product are greater than 12,000, select Meet the following condition and enter the formula
CalculationPassValue((Product.CurrentMember, [Unit
Sales]), 0 ) > 12000
This condition formula might be more complicated than you expected. The difficulty in writing this formula is that it's self-referencing. The condition formula checks the value of the current cell, which references the calculated-cell formula again. To prevent this formula from endlessly evaluating itself, I used the CalculationPassValue() function to determine the cell's value before I applied the calculated cell. This approach works because MDX formulas require multiple passes to evaluate. Because Analysis Services evaluates calculated cells after pass 0, you can stop evaluating the condition formula at pass 0 to prevent it from referencing itself. The second parameter of CalculationPassValue() is the pass number on which you want to stop evaluating the MDX expression.
After you enter the condition formula and click Next, the wizard prompts you to enter the calculation formula. The calculation formula determines what the cell's value will be after Analysis Services evaluates the calculated cell. For this example, you don't want to change the cell's value, so enter the current cell's address. Again, you must use the CalculationPassValue() function to prevent MDX from being recursive. The calculation formula is
CalculationPassValue(Product.CurrentMember, 0 )
Click Next, name the new calculated cell—I named it NonConsumable Highlight—and click Finish. Now you have a calculated cell without display criteria.
To define the cell properties' display criteria, select the new calculated cell in the Cube Editor from the tree display on the left. Expand the Properties pane in the lower left corner, and select the Advanced tab, as Figure 2 shows. ForeColor and BackColor values are RGB values in which each hue is a value between 0 and 255 (0 is dark, and 255 is bright). The following formula lets you create your own color combinations:
Color Value = (Blue * 256 * 256) + (Green * 256) + (Red)
For example, if you want to create bright red, use a value of 255 for red and 0 for both blue and green, giving you a color value of 255. Bright green has the formula
Color Value = (0 * 256 * 256) + (255 * 256) + (0) = 65280
The possible values for the FontFlags property are 1 for bold, 2 for italic, 4 for underline, and 8 for strikethrough. You can combine flags by adding the numbers together. For example, 3 is 1 + 2, meaning bold plus italic.
The Format String property is more complicated. Its potential values differ for numeric-, string-, and date-type data. For numeric values, you supply one to four different parameters in a semicolon-delimited Format String for positive numbers, zero values, negative numbers, and null values, respectively. If you supply fewer than four parameters, each parameter applies to more than one category of values. For example, if you supply two parameters, the first parameter applies to positive numbers and zero values and the second parameter applies to negative numbers. Nulls receive default treatment, which is to return an empty string. An example of a two-parameter numeric Format String is
$#,##0;($#,##0)
This example uses parentheses to represent negative numbers instead of prefixing the number with a minus sign. For information about all the possible characters in a Format String, see SQL Server Books Online (BOL), "Using Cell Properties."
Calculated Cells through MDX queries. You can also use calculated cells in MDX queries to define cell properties. For example, the following MDX query demonstrates both how to define a calculated cell that has cell properties and how to request that the query return cell properties with the query result.
WITH CELL CALCULATION Sample FOR '({[Product],\[Product Family],&[Food]})'
AS 'CalculationPassValue(Product.CurrentMember,
0 )',
CONDITION = 'CalculationPassValue((Product
.CurrentMember,\[Unit Sales]), 0 ) > 12000',
FORE_COLOR = '255 * 256'
SELECT [Product].[Product Family].Members on
Columns FROM Sales
CELL PROPERTIES VALUE, FORMATTED_VALUE,
FORE_COLOR, BACK_COLOR