This example displays in green any Food cells with Unit Sales greater than 12,000. The Non-Consumable value is in blue, which you specified when you defined the calculated cell with Analysis Manager. The Drink value will be in red (as I show later).
The last section of the MDX query, starting with the keywords CELL PROPERTIES, specifies which cell properties to return with the query's result. If you use the MDX sample application, or any other OLAP application, you must include the CELL PROPERTIES keyword in your MDX query to have the color and font properties affect the display of your query results. Format String affects the result regardless of whether you include the CELL PROPERTIES keyword because the default cell properties that a query result returns are VALUE, FORMATTED_VALUE, and ORDINAL. VALUE and FORMATTED_VALUE are the numeric and string representations of the cell contents. FORMATTED_VALUE uses Format String regardless of whether the query requested it. ORDINAL is a unique index of the cell within the query result.
Notice one other thing about the MDX query. The FORE_COLOR property is set equal to a numeric expression enclosed in single quotes. Like all cell properties, string values define FORE_COLOR, so quotes always enclose FORE_COLOR when you define it in MDX. For a cell property such as FORE_COLOR, the string must be an MDX expression.
Calculated Members
When using exception highlighting, you might want to define different display characteristics for numbers in different numeric ranges. For example, you might want to display low sales values in bold red and extraordinarily high sales values in green italics. The following code demonstrates how you can use MDX in a cell properties' definition to create multiple display characteristics based on a cell's value; this example also uses a calculated member instead of a calculated cell:
WITH MEMBER [Measures].[Unit Sales Highlight] as
'[Unit Sales]',
FORE_COLOR = 'iif([Unit Sales] > 12000, 255 * 256,
iif([Unit Sales] > 5000, 255, 0))',
FONT_FLAGS = 'iif([Unit Sales] > 12000, 2,
iif([Unit Sales] < 5000, 1, 0))'
SELECT {[Measures].[Unit Sales Highlight]} on
COLUMNS,
[Drink],Children on ROWS
FROM Sales
CELL PROPERTIES VALUE, FORMATTED_VALUE,
FORE_COLOR, FONT_FLAGS
You use the calculated member in place of Unit Sales to highlight high and low values. The calculated member displays values less than 5000 in bold red and values greater than 12,000 in green italics. I explored the trade-offs between using calculated cells and calculated members in "The Power of Calculated Cells," November 2000. The most significant point is that only SQL Server 2000 Enterprise Edition supports calculated cells, whereas all SQL Server 2000 editions support calculated members.
You can define calculated members on the server with Analysis Manager or on the client in MDX. One technique is to use Analysis Manager to define a highlighted measure, then change the nonhighlighted measure (Unit Sales) to hidden. Then, client applications can select only the highlighted measure. Or, you can use Analysis Manager to make the new calculated measure the default measure for the cube (this feature is new to Analysis Services).
Custom Member Options
The final technique for defining cell properties, custom member options, lets you define cell properties in a dimension table, then use that table to create a cube. Custom member options are useful if you define the criteria for exception highlighting in the cube's source data. The technique is similar to defining a cell property that retrieves its value from a member property, but custom member options are more flexible.
Using a member property, you specify the value for one cell property. Custom member options let you define multiple cell properties in one dimension-table column. The implication is that different dimension-table members can have different sets of cell properties. For example, you might define one dimension-table member with font and foreground color and another one with a custom format string and a background color.
To use custom member options to create cell properties, take the following steps:
- Turn on Custom Members for a dimension or a level in a dimension.
- Turn on Custom Member Options for the same dimension or level.
- Fill in member options in the dimension table.
- Reprocess the dimension.
- Reprocess the cube.
Only custom members can have custom member options. Custom members are dimension members that use a dimension-table column containing MDX formulas to determine members' values. To enable custom members, start Analysis Manager and expand the treeview to the shared dimensions of the FoodMart 2000 database. Right-click the Product Dimension, and select Edit to start the Dimension Editor. Within the Dimension Editor, select Product Family in the treeview, then expand the Properties pane on the lower left. Click the Advanced tab, and scroll down until you see the property labeled Custom Members. Click the ellipsis next to the value of the Custom Members' property. The Define Custom Member Column dialog box, which Figure 3, page 73, shows, appears and lets you enable custom members and create a new column in the product's dimension table to hold them. Enter FamilyCustomMembers as the column name, and click OK.
After you set up custom members, you can enable custom member options by clicking the ellipsis next to the value of the custom member options' property. The Define Custom Member Options dialog box, which Figure 4 shows, appears and lets you enable custom member options and create another dimension-table column to contain the cell property name and value pairs. I called this column FamilyMemberOptions.
Then, save your changes and start Microsoft Access to view the FoodMart 2000 database (FoodMart 2000.mdb is in \program files\microsoft analysis services\samples). Next, run the following update query to define cell properties for dimension members in the Drink product family:
UPDATE product_class
SET product_class.FamilyMemberOptions =
"FORE_COLOR='255'",
product_class.FamilyCustomMembers =
"Aggregate(Product.CurrentMember.Children)"
WHERE (((product_class.product_family)="Drink"));
After you run this query, restart Analysis Manager and reprocess the product dimension and the Sales cube. Analysis Manager's data browser shows the Drink product family cell values in red.
A Powerful Tool
If you develop analysis applications, you should add support for cell properties. In other words, when your application generates MDX queries, you should request cell properties and retrieve them with the query results. Web Listing 1 (see the More on the Web box for download information) shows a Visual Basic (VB) program that demonstrates how to request and retrieve cell properties by using ADO. Exception highlighting is a powerful tool because it draws attention to some of an OLAP cube's most relevant information. Business analysis is about finding the trends, patterns, and exceptions in your business. Cell properties are the right tool for communicating the exceptions in your business data to your analysis application users. Put your exception-highlighting knowledge to the test in "March MDX Puzzle," page 72, and check out the answer to last month's puzzle in "February MDX Puzzle Solution Revealed," page 73.