How To Bind the Office Chart Component to a PivotTable (286320)


The information in this article applies to:

  • Microsoft Office 2003 Web Components
  • Microsoft Office XP Web Components
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q286320

SUMMARY

This article demonstrates how the Office Chart component can be bound to an Office PivotTable component at run time. The sample code uses data from the FoodMart sample provided with Analysis Services for Microsoft SQL Server 7.0 and SQL Server 2000.

MORE INFORMATION

The Chart component can be bound to a PivotTable so that the totals that are displayed in the PivotTable are plotted. When a chart is bound to a PivotTable, you can expand or collapse members on the PivotTable axes to show more or less detail in the ChartSpace. Likewise, when you filter members on the PivotTable axes, the data plotted in the bound ChartSpace automatically reflects your changes.

By default, a bound Chartspace displays field buttons and drop zones that enable you to modify the layout and contents of the chart(s) in the same way that you would a PivotTable. The drop zones that are present in the ChartSpace vary with the chart types you apply; for example, the drop zones for a clustered bar chart differ from those of a pie chart. Whether or not field buttons and drop zones appear in the ChartSpace is determined by the DisplayFieldButtons property of the ChartSpace object.

The following sample code demonstrates a chart bound to a PivotTable connected to data in the SQL Server FoodMart sample database. The code shows how you can build the PivotTable at run time; it also shows how you can manipulate the way in which the data is plotted in the bound chart by using the HasMultipleCharts property, the PlotAllAggregates property, and the SetData method of the ChartSpace object. For additional information about using these properties and methods with bound charts, click the following article number to view the article in the Microsoft Knowledge Base:

288907 INFO: Bind the Office XP Chart component to a data source

Sample Code

  1. Paste the following code into any text editor such as Notepad and save the file as PivotChart.htm. Modify the assignment for the sServerName variable to the name of your SQL Server.
    <html>

<body>
<p align="Center">
<button id="btnOneChart" style="width:200">All Totals On One Chart</button>&#xa0;&#xa0;
<button id="btnMultiChart" style="width:200">Each Total on a Separate Chart</button>&#xa0;&#xa0;
<button id="btnBreakOut" style="width:200">Break Out Series into Quarters</button>
<br/><br/>
<object classid="clsid:0002E556-0000-0000-C000-000000000046" id="CSpace" width="95%" height="55%">
</object>
<br/><br/>
<object classid="clsid:0002E552-0000-0000-C000-000000000046" id="PTable" width="95%" height="35%">
<param name="AutoFit" value="False"/>
</object>
</p>
<script language="VBScript">

Dim c
Dim sServerName
Set c = CSpace.Constants
sServerName = "YourSQLServer"
btnBreakOut.disabled = True
BuildPivot

Function BuildPivot()
    
  'Connect the PivotTable to the data source and bind the chart to the PivotTable.
   PTable.ConnectionString = "Provider=msolap;Initial Catalog=FoodMart 2000;Data Source=" & sServerName
   PTable.DataMember = "Sales"
   Set CSpace.DataSource = PTable

   'Build the PivotTable.
   Set oView = PTable.ActiveView
   With oView

      'Add Dimensions to the column and row axes.
      .ColumnAxis.InsertFieldSet .FieldSets("Time")
      .RowAxis.InsertFieldSet .FieldSets("Product")

      'Add three totals to the data area.
      .DataAxis.InsertTotal .Totals("Store Sales")
      .Totals("Store Sales").NumberFormat = "_($* #,##0_)"
      .DataAxis.InsertTotal .Totals("Profit")
      .Totals("Profit").NumberFormat = "_($* #,##0_)"
      .DataAxis.InsertTotal .Totals("Store Cost")
      .Totals("Store Cost").NumberFormat = "_($* #,##0_)"
      
      'Expand the Year on the Column axis so that Quarterly information is displayed.
      .Fieldsets("Time").Fields("Year").Expanded = True
      .Fieldsets("Product").Fields("Product Family").Expanded = True

      'Hide the Product Category, SubCategory, Brand Name, and Product
      'Name in the Product hierarchy so that those levels do not appear on
      'the report.
      .FieldSets("Product").Fields("Product Category").IsIncluded=False
      .FieldSets("Product").Fields("Product Subcategory").IsIncluded=False
      .FieldSets("Product").Fields("Brand Name").IsIncluded = False
      .FieldSets("Product").Fields("Product Name").IsIncluded = False
      
      'Hide the Month in the Time hierarchy so that it does not appear
      'on the report.
      .Fieldsets("Time").Fields("Month").IsIncluded=False

      'Remove the Product Department subtotals that would appear when 
      'expanding a Product Family member.
      .Fieldsets("Product").Fields("Product Department").SubTotals(1) = False
      
      'Filter the row and column axes to limit the data shown in the PivotTable.
      .RowAxis.FieldSets("Product").Fields(0).IncludedMembers = Array("Drink")
      .ColumnAxis.FieldSets("Time").Fields("Quarter").IncludedMembers = Array("Q1", "Q2")

    End With    
   
   CSpace.Charts(0).Type = c.chChartTypeBarClustered
   CSpace.Border.Color = c.chColorNone
   
End Function

Function btnOneChart_OnClick()
    'Show totals as series on one chart.  
    BuildPivot
    CSpace.HasMultipleCharts = False  
    CSpace.PlotAllAggregates = c.chPlotAggregatesSeries
    CSpace.HasChartSpaceLegend = True  
    btnBreakOut.disabled = False
End Function

Function btnMultiChart_OnClick()
    'Each total on a separate chart.
    BuildPivot
    CSpace.HasMultipleCharts = True
    CSpace.PlotAllAggregates = c.chPlotAggregatesCharts
    CSpace.HasChartSpaceLegend = False  
    CSpace.ChartWrapCount = 3
    CSpace.HasChartSpaceLegend = True     
    NormalizeAxes
    btnBreakOut.disabled = False
End Function

Function btnBreakOut_OnClick()
    CSpace.HasMultipleCharts = True  
    CSpace.SetData c.chDimCharts, 0, PTable.ActiveView.ColumnAxis.FieldSets("Time").Fields("Quarter")
    CSpace.HasChartSpaceLegend = False      
    NormalizeAxes
    btnBreakOut.disabled = True
End Function

Function NormalizeAxes()
   'Normalize the maximum for the value axis of all charts in the 
   'ChartSpace.
   If CSpace.Charts.Count>0 Then
      Dim ValueMax
      For each oCht in CSpace.Charts
         max = oCht.Axes(c.chAxisPositionValue).Scaling.Maximum
         If ValueMax < max Then ValueMax = max
      Next   
      For each oCht in CSpace.Charts
         oCht.Axes(c.chAxisPositionValue).Scaling.Maximum = ValueMax
      Next
   End If
End Function

</script>

</html>

    Note In the preceding code, the classid property refers to Office XP component. To add the reference to Office 2003 component, replace the classid with the following:
    • Replace CSpace object classid with

      0002E55D-0000-0000-C000-000000000046

    • Replace PTable object classid with

      0002E55A-0000-0000-C000-000000000046

  2. Start Microsoft Internet Explorer and browse to PivotChart.htm. Internet Explorer renders a Web page that contains a PivotTable and a chart. When the page loads, the script builds the PivotTable so that it contains filtered data on the row and column axes and totals on the data axis. The initial script also binds the ChartSpace to the PivotTable; as a result of the binding, the Chartspace displays field buttons and drop zones and there is one chart in the ChartSpace but no data is yet plotted.
  3. Click All Totals On One Chart to build the PivotTable and bind it to the ChartSpace. The result is a single chart in the Chartspace. Each total that appears in the PivotTable is plotted as one series in the chart. In the PivotTable, Q1 and Q2 each display a total for Store Sales, Profit, and Store Cost; therefore, the chart contains six series.
  4. Click Each Total on a Separate Chart. Now the ChartSpace contains three charts: one chart for Store Sales, one chart for Profit, and another for Store Cost. Each chart contains a series for Q1 and a series for Q2.
  5. Click All Totals On One Chart again to create one chart with six series. Then click Break Out Series into Quarters. This results in two charts: one chart for Q1 and another for Q2. Each chart contains a series for each total (in other words, three series per chart).
  6. Click Each Total On a Separate Chart to create three charts (one chart for each total). Then click Break Out Series into Quarters. The ChartSpace now contains six charts because the totals have been broken out into separate plots for Q1 and Q2.


(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

REFERENCES

For more information, see the following Microsoft Web sites:

Using Office Web Components
http://support.microsoft.com/ofd

Microsoft Office Developer Center
http://msdn.microsoft.com/office/

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbhowto kbOfficeWebChart kbOfficeWebPivot kbPivotTable KB286320 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.