INFO: Using Analysis Server Format Properties with the Office XP PivotTable Component (318122)

SUMMARY

The Microsoft Office XP PivotTable component supports two undocumented properties: UseProviderFormatting and DisplayCellColor. These properties allow you to use server formatting for cells and totals in a PivotTable view.

When you set the UseProviderFormatting property to True, the PivotTable component applies number formats that are stored on the server to the totals that appear in the PivotTable view. The default setting for this property is False.
When you set the DisplayCellColor property to True, the PivotTable component applies Forecolor and Backcolor settings that are stored on the server to the cells that appear in the PivotTable view. The default setting for this property is False.
The PivotTable component does not support other format properties that can be set on the server, such as FontName, FontSize, and FontFlags.

NOTE: The UseProviderFormatting and DisplayCellColor properties are hidden members in the Microsoft Office XP Web Components object model. These members are not documented and, therefore, these members are not supported by Microsoft Technical Support. The sample code in this article is provided "as-is."

MORE INFORMATION

Apply Cell Properties to a Cube

Start Analysis Manager, and then select the Sales cube in the Foodmart 2000 database.
On the Action menu, click Edit to open the Cube Editor.
In the Cube Editor, select the calculated measure Profit, and then click Properties.

Apply the following properties to Profit:

   Format String    Currency
   Forecolor        Iif([Measures].[Profit]>1000,4734347,16770244)
   Backcolor        Iif([Measures].[Profit]>1000,16770244,4734347)
   FontName         "Courier"
   FontSize         16
   FontFlags        Iif([Measures].[Profit]<100,7,0)

On the File menu in Cube Editor, click Save, and then close the Cube Editor.
Close Analysis Manager.

Create a Web Page Containing a PivotTable Component

Create the following Web page in any text or HTML editor:

<html>

<body>
<object classid="clsid:0002E552-0000-0000-C000-000000000046" id="PivotTable1">
</object>
</body>

<script language="VBScript">
   'Connect to the data source
   PivotTable1.ConnectionString = "Provider=MSOLAP.2;Data Source=YourServer;Initial Catalog=Foodmart 2000;"
   PivotTable1.DataMember = "Sales"
    
   Set oView = PivotTable1.ActiveView

   'Use number formats and cell fore/back colors from the server
   oView.UseProviderFormatting = True
   oView.DisplayCellColor = True
   
   'Add Dimensions to the row axis
   oView.RowAxis.InsertFieldSet oView.FieldSets("Customers")
   oView.RowAxis.FieldSets("Customers").Fields("Country").Expanded = True
   oView.RowAxis.FieldSets("Customers").Fields("State Province").Expanded = True
      
   'Add a calculated member to the data axis
   oView.DataAxis.InsertTotal oView.Totals("Profit")
</script>

</html>

NOTE: Change YourServer in the PivotTable connection string to the name of your server.

Start Microsoft Internet Explorer, and then locate the Web page that you created in step 1.

Notice that the PivotTable component observes the settings that are stored on the server for the Number format, the cell Forecolor, and the cell Backcolor.

Also notice that the PivotTable component does not observe the settings that are stored on the server for the font properties (FontName, FontSize, and FontFlags).

Considerations for Using Server Format Properties

Before you decide to use format properties that are stored on the server, consider the following points:

When you set UseProviderFormatting to True, the cell contents appear as text instead of appearing as numeric data. Therefore, by default, the cell contents are left-aligned. If the width of the column is too small for all of the contents of a cell, the cell displays text that is truncated, instead of displaying ########, as expected. This may create a false impression about the value in the cell. For example, the value 123456.78 might appear as either 1234 or 6.78, depending on the text alignment and depending on the column width.
When you copy a PivotTable object to the clipboard, the clipboard stores the PivotTable object as HTML. The clipboard does not retain the server formatting for the PivotTable object. Therefore, when you paste the HTML of the PivotTable object into another program, such as Microsoft Excel or Microsoft Word, the server format settings are not present.

To avoid these problems, you can use Microsoft ActiveX Data Objects (Multidimensional) to query against the cube. When you do this, you obtain the Format property of the measure, and then you apply that format using the correct PivotTable methods and properties. For more information about this approach, click the article number below to view the article in the Microsoft Knowledge Base:

301456 INFO: How to Use OLAP Number Formats with Office Web Components