PRB: Unable to Display Visual Totals Using the PivotTable Component (306756)


The information in this article applies to:

  • Microsoft Office 2003 Web Components
  • Microsoft Office XP Web Components
  • Microsoft SQL Server 2000 Analysis Services
This article was previously published under Q306756

SYMPTOMS

When you use the PivotTable component and you choose the option to calculate totals based on visible items only, you may receive the following error message:
The data source does not support totalling only visible items.

CAUSE

The PivotTable component does not support visual totals when the computer connects to the data source by using version 1.0 of the OLE DB Provider for OLAP Services.

RESOLUTION

To resolve this problem, modify your connection string to use version 2.0 of the OLE DB Provider for OLAP Services. This resolution is illustrated in the "More Information" section.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Notepad.exe and paste the following HTML in a new file:
    <HTML>
<BODY>
<object classid=clsid:<Class Identifier of Pivot Table Control> 
id="PTable" Width="200" Height="200"></object>
</BODY>

<SCRIPT language=vbscript>
Function Window_OnLoad()

   PTable.AutoFit = False
   PTable.ConnectionString = _
     "provider=msolap.1;data source=YourServer;initial catalog=Foodmart 2000;"
   PTable.DataMember = "Sales"

End Function   

</SCRIPT>
</HTML>
    Note Replace the <Class Identifier of Pivot Table Control> in the above code with the Class ID of the Pivot Table control being used.
    For Microsoft Office PivotTable 10.0
    0002E552-0000-0000-C000-000000000046
  2. Change YourServer in the connection string to the name of your SQL server.
  3. Save the file as Pivot.htm.
  4. Start Microsoft Internet Explorer and browse to Pivot.htm.
  5. After the Web page is rendered, click Commands and Options on the PivotTable toolbar. On the Report tab, select the option to calculate totals based on Visible Items Only. You receive the error that is described in the "Symptoms" section.
To correct this problem, modify the connection string in the code as follows so that it uses the msolap.2 provider: 
PTable.ConnectionString = _
  "provider=msolap.2;data source=YourServer;initial catalog=Foodmart 2000;"
Modification Type:MinorLast Reviewed:7/13/2004
Keywords:kbOfficeWebPivot kbprb KB306756
©2004 Microsoft Corporation. All rights reserved.