MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions


 

Retrieving Cell Properties

A cell can have more properties than the obvious VALUE. The complex and sophisticated nature of multidimensional cubes allows cells to have enhanced properties that enable smart formatting and coloring. Support for these enhanced properties can vary from provider to provider. OLE DB for OLAP recognizes the properties listed in the following table as typical properties that a provider might support for a cell.


Property name
Mandatory/
Optional

Data type

Description
VALUE Mandatory DBTYPE_VARIANT Actual data value.
FORMATTED_VALUE Mandatory DBTYPE_WSTR Character string that represents a formatted display of VALUE.
CELL_ORDINAL Mandatory DBTYPE_UI4 Ordinal number of the cell in the dataset. For more information, see "Navigating Cells in a Dataset" in Chapter 21, "OLE DB for OLAP Concepts."
FORMAT_STRING Optional DBTYPE_WSTR Format string used to create FORMATTED_VALUE. For more information, see "Contents of FORMAT_STRING" in this chapter.
FORE_COLOR Optional DBTYPE_UI4 Foreground color for displaying VALUE. For more information, see "Contents of FORE_COLOR and BACK_COLOR" in this chapter.
BACK_COLOR Optional DBTYPE_UI4 Background color for displaying VALUE. For more information, see "Contents of FORE COLOR and BACK COLOR" in this chapter.
FONT_NAME Optional DBTYPE_WSTR Font to be used to display VALUE.
FONT_SIZE Optional DBTYPE_UI2 Font size to be used to display VALUE.
FONT_FLAGS Optional DBTYPE_I4 Bitmask detailing effects on the font. Can be the result of a bitwise OR operation of one or more of the following:
  • MDFF_BOLD

  • MDFF_ITALIC

  • MDFF_UNDERLINE

  • MDFF_STRIKEOUT

A provider must support the properties labeled as "Mandatory" in this table. A provider may also support other properties not listed. To find out what cell properties a provider supports, an application can obtain the PROPERTIES rowset with a restriction on the PROPERTY_TYPE column set to MDPROP_CELL. The application can specify exactly what properties it wants to see by using the CELL PROPERTIES keyword after the WHERE clause. For example, the expression

SELECT NON EMPTY Products DIMENSION PROPERTIES Products.SKU, Products.SRP 
   ON ROWS,
CROSSJOIN (Years, (Sales, BudgetedSales)) ON COLUMNS
FROM SalesCube
WHERE (January, SalesPerson.[All], Geography.USA)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

will yield a dataset in which each cell has the properties VALUE, FORMATTED_VALUE, FORE_COLOR, and BACK_COLOR.

If the application does not specify any properties for a cell in the MDX statement, the provider assumes a default set of properties that must include VALUE, FORMATTED_VALUE, and CELL_ORDINAL, in that order. This property set can also include other properties that are provider-specific (for example, provider-specific mandatory properties). It must not include any of the optional properties specified in the table above. If the consumer uses the CELL PROPERTIES clause, only those properties selected in this clause are returned. (If the clause does not include any of the default properties mentioned above, those default properties are not returned.)

The properties present in the dataset, their data types, and so on are available by means of the IColumnsInfo interface on the dataset. You can determine the cell properties in the data source object that are available for use in the CELL PROPERTIES clause by using the PROPERTIES rowset and restricting it with the PROPERTY_TYPE column.