Using Cell Properties (MDX)
Cell properties in Multidimensional Expressions (MDX) contain information about the content and format of cells in a multidimensional data source, such as a cube.
MDX supports the CELL PROPERTIES keyword in an MDX SELECT statement to retrieve intrinsic cell properties. Intrinsic cell properties are most commonly used to assist in the visual presentation of cell data.
CELL PROPERTIES Keyword Syntax
Use the following syntax for the CELL PROPERTIES keyword of the MDX SELECT statement:
SELECT [<axis_specification>
[, <axis_specification>...]]
FROM [<cube_specification>]
[WHERE [<slicer_specification>]]
[<cell_props>]
The following syntax shows the format of the <cell_props> value and how this value uses the CELL PROPERTIES keyword along with one or more intrinsic cell properties:
<cell_props> ::= CELL PROPERTIES <property> [, <property>...]
Supported Intrinsic Cell Properties
The following table lists the supported intrinsic cell properties that are used in the <property> value.
Property |
Description |
|---|---|
ACTION_TYPE |
A bitmask that indicates which types of actions exist on the cell. This property can have one of the following values:
.gif "Note") Note
Drillthrough actions are not included for queries containing a set in the where clause.
|
BACK_COLOR |
The background color for displaying the VALUE or FORMATTED_VALUE property. For more information, see FORE_COLOR and BACK_COLOR Contents (MDX). |
CELL_ORDINAL |
The ordinal number of the cell in the dataset. |
FONT_FLAGS |
The bitmask detailing effects on the font. The value is the result of a bitwise OR operation of one or more of the following constants:
For example, the value 5 represents the combination of bold (MDFF_BOLD) and underline (MDFF_UNDERLINE) font effects. |
FONT_NAME |
The font to be used to display the VALUE or FORMATTED_VALUE property. |
FONT_SIZE |
Font size to be used to display the VALUE or FORMATTED_VALUE property. |
FORE_COLOR |
The foreground color for displaying the VALUE or FORMATTED_VALUE property. For more information, see FORE_COLOR and BACK_COLOR Contents (MDX). |
FORMAT |
Same as FORMAT_STRING. |
FORMAT_STRING |
The format string used to create the FORMATTED_VALUE property value. For more information, see FORMAT_STRING Contents (MDX). |
FORMATTED_VALUE |
The character string that represents a formatted display of the VALUE property. |
LANGUAGE |
The locale where the FORMAT_STRING will be applied. LANGUAGE is usually used for currency conversion. |
UPDATEABLE |
A value that indicates whether the cell can be updated. This property can have one of the following values:
|
VALUE |
The unformatted value of the cell. |
Only the CELL_ORDINAL, FORMATTED_VALUE, and VALUE cell properties are required. All cell properties, intrinsic or provider-specific, are defined in the PROPERTIES schema rowset, including their data types and provider support. For more information about the PROPERTIES schema rowset, see MDSCHEMA_PROPERTIES Rowset.
By default, if the CELL PROPERTIES keyword is not used, the cell properties returned are VALUE, FORMATTED_VALUE, and CELL_ORDINAL (in that order). If the CELL PROPERTIES keyword is used, only those cell properties explicitly stated with the keyword are returned.
The following example demonstrates the use of the CELL PROPERTIES keyword in an MDX query:
SELECT
{[Measures].[Reseller Gross Profit]} ON COLUMNS,
{[Reseller].[Reseller Type].[Reseller Name].Members} ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING, FORE_COLOR, BACK_COLOR
Cell properties are not returned for MDX queries that return flattened rowsets; in this case, each cell is represented as if only the FORMATTED_VALUE cell property were returned.
Setting Cell Properties
Cell properties can be in Microsoft SQL Server Analysis Services through the Calculations tab on the Cube Designer or the Dimension Designer. The cell properties are represented as string expressions, shown in the following example that provides, for the specified member, a yellow background with a red foreground:
FORE_COLOR='255',BACK_COLOR='65535'
Cell properties usually roll up to parent members, unless the parent is a custom member with cell properties. In this case, the parent cell properties override the cell properties derived from its children.