Microsoft KB Archive/286320

= How To Bind the Office Chart Component to a PivotTable =

Article ID: 286320

Article Last Modified on 10/23/2006

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Web Components
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



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
  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.

 All Totals On One Chart &#xa0;&#xa0; Each Total on a Separate Chart &#xa0;&#xa0; Break Out Series into Quarters    

Dim c Dim sServerName Set c = CSpace.Constants sServerName = &quot;YourSQLServer&quot; btnBreakOut.disabled = True BuildPivot

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

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

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

'Add three totals to the data area. .DataAxis.InsertTotal .Totals(&quot;Store Sales&quot;) .Totals(&quot;Store Sales&quot;).NumberFormat = &quot;_($* #,##0_)&quot; .DataAxis.InsertTotal .Totals(&quot;Profit&quot;) .Totals(&quot;Profit&quot;).NumberFormat = &quot;_($* #,##0_)&quot; .DataAxis.InsertTotal .Totals(&quot;Store Cost&quot;) .Totals(&quot;Store Cost&quot;).NumberFormat = &quot;_($* #,##0_)&quot; 'Expand the Year on the Column axis so that Quarterly information is displayed. .Fieldsets(&quot;Time&quot;).Fields(&quot;Year&quot;).Expanded = True .Fieldsets(&quot;Product&quot;).Fields(&quot;Product Family&quot;).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(&quot;Product&quot;).Fields(&quot;Product Category&quot;).IsIncluded=False .FieldSets(&quot;Product&quot;).Fields(&quot;Product Subcategory&quot;).IsIncluded=False .FieldSets(&quot;Product&quot;).Fields(&quot;Brand Name&quot;).IsIncluded = False .FieldSets(&quot;Product&quot;).Fields(&quot;Product Name&quot;).IsIncluded = False 'Hide the Month in the Time hierarchy so that it does not appear 'on the report. .Fieldsets(&quot;Time&quot;).Fields(&quot;Month&quot;).IsIncluded=False

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

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(&quot;Time&quot;).Fields(&quot;Quarter&quot;) 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

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

</li></ul> </li> 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.</li> 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.</li> 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.</li> 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).</li> 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.</li></ol>

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

<div class="references_section">