How to Get Extended Properties with SSAS OLE DB Provider
Many report developers find the SSAS data provider too restrictive for authoring reports from UDM. Instead, they replace it with the native Microsoft OLE DB Provider for Analysis Services 9.0 which gives them maximum flexibility at the expense of convenience (the query has to be hand-generated, parameters are not supported, extended properties are not available, etc).
By default, the OLE DB provider doesn’t return the extended cell properties, such as format and color settings. Thanks to Robert Bruckner from the SSRS team, here is a precious tip to get the extended properties:
- Append Extended Properties=”ReturnCellProperties=true” to the OLE DB connection string of your data source, e.g.:
Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=”Adventure Works DW”;Extended Properties=”ReturnCellProperties=true” - Request the cell properties in your MDX query as the SSAS provider does, e.g.:
SELECT NON EMPTY { [Measures].[Internet Sales Amount], … } ON COLUMNS,
NON EMPTY { [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS , …. }
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
- Execute the query. The OLE DB Provider returns the extended properties as fields in the dataset.
You will notice that the OLE DB provider returns the cell colors as numeric values. To convert the SSAS numeric colors to RGB colors for use on your report, you can use this helper function in your report.
int intValue = ConvertToInt32(value, out isValid);
if (isValid) {
return String.Format(“#{0:x2}{1:x2}{2:x2}”,
(intValue & 0x000000ff),
(intValue >> 8) & 0x000000ff,
(intValue >> 16) & 0x000000ff);
}
