Microsoft KB Archive/288907

= INFO: Bind the Office XP Chart Component to a Data Source =

Article ID: 288907

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Web Components

-



This article was previously published under Q288907



SUMMARY
The Microsoft Office 2003 Chart component and the Microsoft Office XP Chart component can bind to an ActiveX Data Objects (ADO) Recordset object, all other Office Web components (the Spreadsheet component, the PivotTable component, and the DataSource component), and any other controls that support the IDataSource interface. This article describes basic concepts for building charts that are data-bound and how you can use different properties and different methods for the ChartSpace object to designate how bound data is plotted on one or more charts in the ChartSpace. For illustration purposes, a recordset is used as the data source in the sample code. However, the concepts that are presented in this article about data binding can be applied to any other type of data source that the Chart component can bind to.



MORE INFORMATION
You can bind the Chart component to a data source in either of the following ways:


 * Set the DataSource property of the ChartSpace object to an Office DataSourceControl control, an Office Spreadsheet control, an Office PivotTable control, or an open ADO recordset that you build at runtime. Data sources that are bound to a chart by using the DataSource property are external data sources.
 * Set the ConnectionString property and the CommandText property of the ChartSpace object to create a data source for use by the Chart component. A data source that is created in this manner is considered an internal data source.

After you bind the Chart component, you can then set one or more properties of the ChartSpace object to designate how the data is plotted:
 * HasMultipleCharts property: Set this property to TRUE or to FALSE to indicate whether the specified ChartSpace contains multiple charts.
 * SetData method: Use SetData to indicate the fields and the totals that must represent categories and values for the charts. SetData also provides names for the chart series.
 * PlotAllAggregates property: Set the PlotAllAggregates property to designate how the data is plotted across one or more charts in the ChartSpace.

Bound ChartSpace with a Single Chart
Consider a ChartSpace that is bound to an ADO recordset that contains a column for product categories and four columns that represent quarterly sales for each category. If you want to display a single chart with the sales for each quarter that are plotted as a series on the chart, you can set HasMultipleCharts to FALSE and PlotAllAggregates to chPlotAggregatesSeries with the following code:

Set CSpace.DataSource = oRs  'Where oRs is an ADODB.Recordset object. CSpace.HasMultipleCharts = False CSpace.PlotAllAggregates = c.chPlotAggregatesSeries CSpace.SetData c.chDimCategories, c.chDataBound, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, c.chDataBound, Array(&quot;Qtr 1&quot;, &quot;Qtr 2&quot;, &quot;Qtr 3&quot;, &quot;Qtr 4&quot;) If you change PlotAllAggregates in the previous sample code to chPlotAggregatesCategories, you still have only one chart, but you have one series instead of four. The chart categories have two levels: one level for the CategoryName field and another level for the quarters. The illustrations that follow show you the differences in how data is plotted when PlotAllAggregates is chPlotAggregatesSeries or chPlotAggregatesCategories:





Bound ChartSpace with Multiple Charts
By using the same ADO recordset that is described in the previous section, you can display one chart for the sales of each quarter if you set HasMultipleCharts to TRUE and PlotAllAggregates to chPlotAggregatesCharts. For example, the result of the following code is four charts, where each chart contains a single series (one chart for Qtr 1, one chart for Qtr 2, one chart for Qtr 3, and one chart for Qtr 4):

Set CSpace.DataSource = oRs  'Where oRs is an ADODB.Recordset object. CSpace.HasMultipleCharts = True CSpace.PlotAllAggregates = c.chPlotAggregatesCharts CSpace.SetData c.chDimCategories, c.chDataBound, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, c.chDataBound, Array(&quot;Qtr 1&quot;, &quot;Qtr 2&quot;, &quot;Qtr 3&quot;, &quot;Qtr 4&quot;)

When you create a multichart ChartSpace by setting PlotAllAggregates to chPlotAggregatesCharts, fields (or totals) in the bound data source always appear as a series on their own chart. There is no way to have two or more fields from the data source plotted on the same chart. For example, there is no way to modify the previous code so that the bound data for &quot;Qtr 1&quot; and &quot;Qtr 2&quot; appears on one chart and the bound data for &quot;Qtr 3&quot; and &quot;Qtr 4&quot; appears on another chart. If you require that your data be presented in this manner, you may have to use a combination of bound data and literal data for the charts, or just use literal data completely.

Multiple charts can also be plotted in a ChartSpace by setting the chDimCharts dimension with the SetData method. By using a chDimCharts dimension, you can divide (or filter) the series among multiple charts that is based on values in another field. Consider again the ADO recordset that contains columns for product categories and quarterly sales, and then assume that there is one additional column that indicates whether the fourth quarter sales increased from the third quarter sales. The additional column can be used to divide series among multiple charts to group series that show a fourth quarter increase in one chart and to group series that show a fourth quarter decrease in another chart:

Set CSpace.DataSource = oRs  'Where oRs is an ADODB.Recordset object CSpace.HasMultipleCharts = True CSpace.PlotAllAggregates = c.chPlotAggregatesSeries CSpace.SetData c.chDimCategories, c.chDataBound, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, c.chDataBound, Array(&quot;Qtr 3&quot;, &quot;Qtr 4&quot;) CSpace.SetData c.chDimCharts, c.chDataBound, &quot;Qtr4Increase&quot; The result is a chart that has all series plotted on two charts. The first chart contains the series where the fourth quarter sales increased, and the second chart contains the series where the fourth quarter sales decreased. The illustration that follows represents the charts that are created:



Use the SetData Method with Bound Charts
In the OWC10 object model, the ChartSpace, a chart, and a series all expose a SetData method. When the ChartSpace is bound to a data source, you can only use the SetData method for the ChartSpace object to add series or values from the bound data source. If you try to use SetData for either a chart or a series that has been created as a result of the binding, you may receive either of the following run-time errors:  

Run-time error '-2147467259 (80004005)':

Method 'SetData' of object 'ChChart' failed.

 

Run-time error '-2147467259 (80004005)':

Method 'SetData' of object 'ChSeries' failed.



A general guideline for data-bound charts is that the SetData method can be applied to an individual chart or to a series if you added the chart or the series to the ChartSpace by using the Add method and you are loading literal data (comma-delimited strings or arrays) to the added chart or to the series.

Set CSpace.DataSource = oRs  'Where oRs is an ADODB.Recordset object. CSpace.HasMultipleCharts = True CSpace.PlotAllAggregates = c.chPlotAggregatesCharts CSpace.SetData c.chDimCategories, c.chDataBound, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, c.chDataBound, Array(&quot;Qtr 1&quot;, &quot;Qtr 2&quot;)

'Add an additional series based on literal data to each of the charts 'in the ChartSpace. Dim oChart, oSeries For Each oChart in CSpace.Charts Set oSeries = oChart.SeriesCollection.Add oSeries.SetData c.chDimSeriesNames, c.chDataLiteral, &quot;Goal&quot; oSeries.SetData c.chDimValues, c.chDataLiteral, Array(30000, 20000, 25000) Next The previous code plots &quot;Qtr 1&quot; sales and &quot;Qtr 2&quot; sales on separate charts, adds an additional series to each chart, and then loads the new series with literal data:



Demonstration
The following sample demonstrates binding a chart to a recordset that is obtained from the sample Access Northwind database. You can create the recordset in one of three ways:
 * Use an ADO recordset.
 * Use the ConnectionString property and the CommandText property of the ChartSpace object.
 * Use the Office DataSourceControl (DSC).

The charts that result are the same regardless of the data source type that you select. After the chart is bound to a data source, the bound data is plotted by using different combinations of the HasMultipleCharts property, the PlotAllAggregates property, and the SetData method to show you how each can affect the resulting charts and series in the ChartSpace.   Copy the following HTML code to a text editor and then save it as BoundChart.htm. If necessary, you can modify the assignment for the sConn variable in the Connect function so that the path of Northwind.mdb matches your installation of Office.

Note The following code uses the DataSourceControl object 10.0 and the ChartSpace object 10.0. For the Office System 2003 version of the DataSourceControl object and of the ChartSpace object, you must replace each value of the clsid with the version of Office Web Component 2003 respectively.

The programmatic identifier for the DataSourceControl object 11.0 is CLSID:CLSID:0002E55B-0000-0000-C000-000000000046.

The programmatic identifier for the ChartSpace object 11.0 is CLSID:0002E55D-0000-0000-C000-000000000046.

td {font-size:'x-small';font-family:'Sans-Serif'} select {font-size:'x-small';font-family:'Sans-Serif'} button {font-size:'x-small';font-family:'Sans-Serif'} 

 



Dim c Set c = CSpace.Constants

Dim oRS

Function Connect

sSQL = &quot;SELECT [Product Sales for 1997].CategoryName, &quot; & _ &quot;Sum(IIf([ShippedQuarter]='Qtr 1',[ProductSales],0)) AS [Qtr 1], &quot; & _ &quot;Sum(IIf([ShippedQuarter]='Qtr 2',[ProductSales],0)) AS [Qtr 2], &quot; & _ &quot;Sum(IIf([ShippedQuarter]='Qtr 3',[ProductSales],0)) AS [Qtr 3], &quot; & _ &quot;Sum(IIf([ShippedQuarter] = 'Qtr 4', [ProductSales], 0)) As [Qtr 4], &quot; & _ &quot;IIf([Qtr 4]>[Qtr 3],'Increase in Qtr4','Decrease in Qtr4') AS Qtr4Increase &quot; & _ &quot;From [Product Sales for 1997] GROUP BY [Product Sales for 1997].CategoryName &quot; & _ &quot;HAVING (CategoryName='Confections' Or CategoryName='Meat/Poultry' Or CategoryName='Beverages')&quot; sConn = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=c:\program files\microsoft office\office10\samples\northwind.mdb&quot; CSpace.Clear Select Case CLng(DataSourceType.value)

Case 0:             'Bind to an ADO Recordset that is built at runtime. Set oRS = CreateObject(&quot;ADODB.Recordset&quot;) oRS.Open sSQL, sConn, 3, 3 Set CSpace.DataSource = oRS

Case 1:             'Bind to a Recordset by using the CommandText/ConnectionString properties. CSpace.ConnectionString = sConn CSpace.CommandText = sSQL

Case 2:             'Bind to a DataSourceControl (DSC). DSC.ConnectionString = sConn If DSC.RecordsetDefs.Count < 1 Then DSC.RecordsetDefs.AddNew sSQL, DSC.constants.dscCommandText, &quot;QuarterlySales&quot; End If     Set CSpace.DataSource = DSC CSpace.DataMember = &quot;QuarterlySales&quot; End Select End Function

Function FormatChart(CSpaceTitle, bShowLegend) 'Hide field buttons. CSpace.DisplayFieldButtons = False 'Add the ChartSpace Title. CSpace.HasChartSpaceTitle = True CSpace.ChartSpaceTitle.Caption = CSpaceTitle CSpace.ChartSpaceTitle.Font.Size = 9 CSpace.ChartSpaceTitle.Font.Bold = True If bShowLegend Then For Each oChart in CSpace.Charts oChart.HasLegend = True oChart.Legend.Position = c.chLegendPositionBottom Next End If End Function

Function btnSeriesOnOneChart_OnClick Connect 'One Chart with four series (one series per Quarter) CSpace.HasMultipleCharts = False CSpace.PlotAllAggregates = c.chPlotAggregatesSeries CSpace.SetData c.chDimCategories, c.chDataBound, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, c.chDataBound, Array(&quot;Qtr 1&quot;, &quot;Qtr 2&quot;, &quot;Qtr 3&quot;, &quot;Qtr 4&quot;) CSpace.Charts(0).Type = c.chChartTypeBarClustered sTitle = &quot;PlotAllAggregates = chPlotAggregatesSeries&quot; FormatChart sTitle, True End Function

Function btnCategoriesOnOneChart_OnClick Connect 'One Chart with One Series and Quarters As a Subordinate Category Level CSpace.HasMultipleCharts = False CSpace.PlotAllAggregates = c.chPlotAggregatesCategories CSpace.SetData c.chDimCategories, c.chDataBound, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, c.chDataBound, Array(&quot;Qtr 1&quot;, &quot;Qtr 2&quot;, &quot;Qtr 3&quot;, &quot;Qtr 4&quot;) CSpace.Charts(0).Type = c.chChartTypeBarClustered CSpace.Charts(0).SeriesCollection(0).Caption = &quot;Sales&quot; sTitle = &quot;PlotAllAggregates = chPlotAggregatesCategories&quot; FormatChart sTitle, True End Function

Function btnMultiChart_OnClick Connect 'Four Charts with One Chart Per Quarter CSpace.HasMultipleCharts = True CSpace.PlotAllAggregates = c.chPlotAggregatesCharts CSpace.SetData c.chDimCategories, c.chDataBound, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, c.chDataBound, Array(&quot;Qtr 1&quot;, &quot;Qtr 2&quot;, &quot;Qtr 3&quot;, &quot;Qtr 4&quot;) CSpace.ChartWrapCount = 4

sTitle = &quot;PlotAllAggregates = chPlotAggregatesCharts&quot; FormatChart sTitle, False End Function

Function btnDivideSeries_OnClick Connect 'Build two charts (one for Qtr3 and one for Qtr4) and then divide 'the plots based on Qtr4 increase/decrease. The result is   'four charts. CSpace.HasMultipleCharts = True CSpace.PlotAllAggregates = c.chPlotAggregatesSeries CSpace.SetData c.chDimCategories, c.chDataBound, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, c.chDataBound, Array(&quot;Qtr 3&quot;, &quot;Qtr 4&quot;) CSpace.SetData c.chDimCharts, c.chDataBound, &quot;Qtr4Increase&quot; CSpace.ChartWrapCount = 4

sTitle = &quot;PlotAllAggregates = chPlotAggregatesSeries&quot; & vbCrLF & _ &quot;chDimCharts Dimension for Increase/Decrease in Fourth Quarter&quot; FormatChart sTitle, True End Function

Function btnCustom_OnClick Connect 'Build two charts (one chart each for Qtr1 and Qtr2 sales). CSpace.HasMultipleCharts = True CSpace.PlotAllAggregates = c.chPlotAggregatesCharts CSpace.SetData c.chDimCategories, 0, &quot;CategoryName&quot; CSpace.SetData c.chDimValues, 0, Array(&quot;Qtr 1&quot;, &quot;Qtr 2&quot;) CSpace.Charts(0).SeriesCollection(0).Caption = &quot;Qtr 1&quot; CSpace.Charts(1).SeriesCollection(0).Caption = &quot;Qtr 2&quot; 'Add an additional series that is based on literal data to each of the charts 'in the ChartSpace. Dim oChart, oSeries For Each oChart in CSpace.Charts Set oSeries = oChart.SeriesCollection.Add oSeries.SetData c.chDimSeriesNames, c.chDataLiteral, &quot;Goal&quot; oSeries.SetData c.chDimValues, c.chDataLiteral, Array(30000, 20000, 25000) Next sTitle = &quot;Bound ChartSpace With An Added Series For Literal Data&quot; FormatChart sTitle, True End Function

 Start Internet Explorer and then move to BoundChart.htm.</li> Click each button on the Web page to examine each of the different chart combinations.

Note You may also select different data source types. The resulting charts are the same regardless of the data source type that you select.</li></ol>

<div class="references_section">