INFO: Bind the Office XP Chart Component to a Data Source (288907)


The information in this article applies to:

  • Microsoft Office XP Web Components
  • Microsoft Office 2003 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, "CategoryName"
    CSpace.SetData c.chDimValues, c.chDataBound, Array("Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4")
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:

Illustration of a Chart where PlotAllAggregates is chPlotAggregatesSeries.


Illustration of a Chart where PlotAllAggregates is 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, "CategoryName"
    CSpace.SetData c.chDimValues, c.chDataBound, Array("Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4")
Illustration of a Chart where PlotAllAggregates is chPlotAggregatesCharts.

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 "Qtr 1" and "Qtr 2" appears on one chart and the bound data for "Qtr 3" and "Qtr 4" 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, "CategoryName"
    CSpace.SetData c.chDimValues, c.chDataBound, Array("Qtr 3", "Qtr 4")
    CSpace.SetData c.chDimCharts, c.chDataBound, "Qtr4Increase"
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:

Illustration of a Chart with a chDimCharts Dimension.

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, "CategoryName"
    CSpace.SetData c.chDimValues, c.chDataBound, Array("Qtr 1", "Qtr 2")

    '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, "Goal"
       oSeries.SetData c.chDimValues, c.chDataLiteral, Array(30000, 20000, 25000)
    Next
The previous code plots "Qtr 1" sales and "Qtr 2" sales on separate charts, adds an additional series to each chart, and then loads the new series with literal data:

Illustration of a Bound ChartSpace with an Added Series for 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.
  1. 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.

    <html>
<head>
<style>
  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'}
</style>
<object classid="clsid:0002E553-0000-0000-C000-000000000046" id="DSC"></object>
</head>
<body>

<table align="Center" cellspacing="5">
<tr>
  <td>One Chart with a Series for Each Quarter</td>
  <td><button id="btnSeriesOnOneChart" style = "width:100">Go!</button> </td>
</tr>
<tr>
  <td>One Chart with One Series and Quarters As a Subordinate Category Level</td>
  <td><button id="btnCategoriesOnOneChart" style="width:100">Go!</button></td>
</tr>
<tr>
  <td>Multiple Charts with One Chart Per Quarter</td>
  <td><button id="btnMultiChart" style="width:100">Go!</button></td>
</tr>
<tr>
  <td>Multiple Charts with Plots Divided By Qtr4 Increase/Decrease</td>
  <td><button id="btnDivideSeries" style="width:100">Go!</button></td>
</tr>
<tr>
  <td>Series Loaded from Literal Data on Bound Chart</td>
  <td><button id="btnCustom" style="width:100">Go!</button></td>
</tr>  
<tr>
  <td ColSpan="2">
  Select a DataSource: &#xa0;&#xa0;
  <select id=DataSourceType size=1>
	<option selected value=0>Bind to an ADO Recordset</option>
	<option value=1>Use ChartSpace ConnectionString/CommandText Properties</option>
	<option value=2>Bind to a DataSourceControl</option>
  </select>
  </td>
</tr>
</table>

<p align="Center">
<object classid="clsid:0002E556-0000-0000-C000-000000000046" id="CSpace" width="85%" height="50%">
</object>
</p>

</body>

<script language="VBScript">

Dim c
Set c = CSpace.Constants

Dim oRS

Function Connect()

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

    Case 0:              'Bind to an ADO Recordset that is built at runtime.
      Set oRS = CreateObject("ADODB.Recordset")
      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, "QuarterlySales"
      End If
      Set CSpace.DataSource = DSC
      CSpace.DataMember = "QuarterlySales"
       
    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, "CategoryName"
    CSpace.SetData c.chDimValues, c.chDataBound, Array("Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4")
    CSpace.Charts(0).Type = c.chChartTypeBarClustered
    
    sTitle = "PlotAllAggregates = chPlotAggregatesSeries"
    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, "CategoryName"
    CSpace.SetData c.chDimValues, c.chDataBound, Array("Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4")
    CSpace.Charts(0).Type = c.chChartTypeBarClustered
    CSpace.Charts(0).SeriesCollection(0).Caption = "Sales"
    
    sTitle = "PlotAllAggregates = chPlotAggregatesCategories"
    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, "CategoryName"
    CSpace.SetData c.chDimValues, c.chDataBound, Array("Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4")
    CSpace.ChartWrapCount = 4

    sTitle = "PlotAllAggregates = chPlotAggregatesCharts"
    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, "CategoryName"
    CSpace.SetData c.chDimValues, c.chDataBound, Array("Qtr 3", "Qtr 4")
    CSpace.SetData c.chDimCharts, c.chDataBound, "Qtr4Increase"
    CSpace.ChartWrapCount = 4

    sTitle = "PlotAllAggregates = chPlotAggregatesSeries" & vbCrLF & _
             "chDimCharts Dimension for Increase/Decrease in Fourth Quarter"
    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, "CategoryName"
    CSpace.SetData c.chDimValues, 0, Array("Qtr 1", "Qtr 2")
    CSpace.Charts(0).SeriesCollection(0).Caption = "Qtr 1"
    CSpace.Charts(1).SeriesCollection(0).Caption = "Qtr 2"
    
    '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, "Goal"
       oSeries.SetData c.chDimValues, c.chDataLiteral, Array(30000, 20000, 25000)
    Next 
    
    sTitle = "Bound ChartSpace With An Added Series For Literal Data"
    FormatChart sTitle, True
    
End Function

</script>

</html>
  2. Start Internet Explorer and then move to BoundChart.htm.
  3. 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.

REFERENCES

For additional information about using the Office Web components, visit the following Microsoft Web site:

http://support.microsoft.com/ofd

For additional information about Office Developer Center, visit the following Microsoft Web site:

http://msdn.microsoft.com/office/

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

286320 HOWTO: Bind the Office XP Chart Component to a PivotTable

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbDataBinding kbGraphxLink kbinfo KB288907
©2004 Microsoft Corporation. All rights reserved.