Create offline cube files (.cub) with OlapCube. Download Now!


table of contents

Using Cell Properties

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.

The following example displays the syntax of the MDX SELECT statement, with the CELL PROPERTIES keyword syntax included.

SELECT [<axis_specification>
       [, <axis_specification>...]]
  FROM [<cube_specification>]
[WHERE [<slicer_specification>]]
[<cell_props>]

The syntax of the <cell_props> value is displayed here, and it employs the CELL PROPERTIES keyword along with one or more intrinsic cell properties:

<cell_props> ::= CELL PROPERTIES <property> [, <property>...]

The supported intrinsic cell properties used in the <property> value are listed in the following table, with brief descriptions on the content of the cell property.

Property Description
BACK_COLOR The background color for displaying the VALUE or FORMATTED_VALUE property.
CELL_EVALUATION_LIST The semicolon-delimited list of evaluated formulas applicable to the cell, in order from lowest to highest solve order.
CELL_ORDINAL The ordinal number of the cell in the dataset.
FORE_COLOR The foreground color for displaying the VALUE or FORMATTED_VALUE property.
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.
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:
  • MDFF_BOLD  = 1

  • MDFF_ITALIC = 2

  • MDFF_UNDERLINE = 4

  • MDFF_STRIKEOUT = 8

For example, the value 5 represents the combination of bold (MDFF_BOLD) and underline (MDFF_UNDERLINE) font effects.

FORMAT_STRING The format string used to create the FORMATTED_VALUE property value.
FORMATTED_VALUE The character string that represents a formatted display of the VALUE property.
NON_EMPTY_BEHAVIOR The measure used to determine the behavior of calculated members when resolving empty cells.
SOLVE_ORDER The solve order of the cell.
VALUE The unformatted value of the cell.

Providers are not required to support all intrinsic cell properties; only the CELL_ORDINAL, FORMATTED_VALUE, and VALUE cell properties must be supported. All cell properties, intrinsic or provider-specific, are defined in the PROPERTIES schema rowset, including their data types and provider support.

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].[Unit Sales], [Measures].[Store Size]} ON COLUMNS,
   {[Store].[Store Name].Members} ON ROWS
FROM Sales
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.

Custom Member Options

Cell properties can be set through Analysis Manager by using the Custom Member Options property of Dimension Editor or Cube Editor. The Custom Member Options property accepts a column reference containing, for each member, a comma-delimited list of cell properties. The cell properties are represented as string expressions, shown in the following example.

FORE_COLOR='255',BACK_COLOR='65535'

The example will provide, for the specified member, a yellow background with a red foreground. 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.