Implementing a Dynamic Chart View

[Previous] [Next]

Now that you know how to adjust the PivotTable control to display different slices of information, you will probably want to let users view that data in a chart as well as in a table full of numbers. This is fairly easy to implement: you bind the Chart control to the PivotTable control, and the Chart control graphically displays the contents of the PivotTable control's report, adjusting automatically when the report's layout changes. For an overview of this topic, refer to Chapter 3.

In the Sales Analysis and Reporting solution, you can click the Chart View button to view the data as a chart or click the View Both button to see both the Chart control and PivotTable control at the same time. The code behind these buttons simply sets the Style.Display property of the control (Chart or PivotTable) to "none" to hide the control or to an empty string to show the control.

Binding the Chart control to the PivotTable control is simple:

 Sub BindChart(ptable, cspace)     Set cspace.DataSource = ptable     cspace.Charts.Add End Sub 'BindChart() 

Setting the Chart control's DataSource property to the PivotTable control tells the Chart control that it should retrieve its data values from the PivotTable control's report. I also add a Chart object to the chart space here but wait until another event to call the Chart object's SetData method. I use the PivotTable control's QueryComplete event, which fires after the PivotTable control has executed a query against the data source. This event provides a good opportunity to perform any task that might depend on the contents of the PivotTable control because it fires whenever the report's layout changes or when the report is filtered or sorted. The code in this event handler calls the LoadChart method, the first half of which is listed here:

 Sub LoadChart(ptable, cspace)     ' Local variables     Dim c           ' Constants object     Dim cht         ' Temporary WCChart reference     Dim ctTotals    ' Count of totals in the PivotTable view     Dim pview       ' Temporary PivotView reference     Dim ax          ' Temporary WCAxis reference     Dim ttl         ' Temporary PivotTotal reference          Set c = cspace.Constants     ' Bind the chart to the PivotTable if necessary     If cspace.ChartDataSources.Count = 0 Then         cspace.DataSource = ptable     End If           ' Get the chart or create one if needed     If cspace.Charts.Count = 0 Then         Set cht = cspace.Charts.Add()     Else         Set cht = cspace.Charts(0)     End If          ' Make sure the chart has a legend     cht.HasLegend = True          ' Get the PivotTable's active view     Set pview = ptable.ActiveView           ' Set the Chart title to the PivotTable title     cht.HasTitle = True     cht.Title.Caption = pview.TitleBar.Caption           ' First bind the series and categories dimensions     ' as you would do for any chart type     cht.SetData c.chDimSeriesNames, 0, c.chPivotColumns     cht.SetData c.chDimCategories, 0, c.chPivotRows     ' Get the current number of totals on the data axis     ctTotals = pview.DataAxis.Totals.Count       

The code begins by ensuring that a Chart object has been created in the Chart control, that the Chart object has a legend, and that its title is set to match the PivotTable control's title (which we automatically set earlier). The code then uses the SetData method to bind the chart's series to the column members in the PivotTable control's report and the chart's categories to the row members in the report. This tells the chart to create a series for each member displayed across the columns of the report and a category for each member displayed down the rows of the report.

Next, the code determines how many totals are included in the current report. This is significant because different chart types are designed to show different types of data sets. A Column, Bar, Line, Area, or Pie chart is good for displaying data that has one value per intersection of category and series. But if there are two data values for each intersection of category and series, a Scatter chart is a more effective visualization. Similarly, if three values make up an intersection of category and series, a Bubble chart or High-Low-Close chart is the appropriate chart type to use. The remainder of the LoadChart function uses this count of totals to dynamically adjust the chart type and bind the appropriate chart dimensions based on the type:

          ' Reset the chart type if necessary, and use SetData to     ' bind the appropriate value dimensions     Select Case ctTotals         Case 0             ' No totals--no change         Case 1             ' Use a Bar chart if there is one total             If cht.Type <> c.chChartTypeBarClustered Then                 cht.Type = c.chChartTypeBarClustered             End If                          ' Use the first (and only) total for values             cht.SetData c.chDimValues, 0, 0                          ' Label the axes, and set the value axis number format             ' to match the number format of the total             Set ax = cht.Axes(c.chAxisPositionBottom)             Set ttl = pview.DataAxis.Totals(0)             ax.NumberFormat = ttl.NumberFormat             ax.HasTitle = True             ax.Title.Caption = ttl.Caption                          ' Set the category axis to list categories             ' in the reverse order so that it matches the             ' way the PivotTable control is showing them             Set ax = cht.Axes(c.chAxisPositionLeft)             ax.Scaling.Orientation = c.chScaleOrientationMaxMin                      Case 2             ' Use a Scatter chart if there are two totals             If cht.Type <> c.chChartTypeScatterMarkers Then                 cht.Type = c.chChartTypeScatterMarkers             End If                      ' Use the first total for Y values and             ' the second for X values             cht.SetData c.chDimYValues, 0, 0             cht.SetData c.chDimXValues, 0, 1             ' Set the label and number format for the Y axis             Set ax = cht.Axes(c.chAxisPositionLeft)             Set ttl = pview.DataAxis.Totals(0)             ax.NumberFormat = ttl.NumberFormat             ax.HasTitle = True             ax.Title.Caption = ttl.Caption             ax.Scaling.Orientation = c.chScaleOrientationMinMax             ' Set the label and number format for the X axis             Set ax = cht.Axes(c.chAxisPositionBottom)             Set ttl = pview.DataAxis.Totals(1)             ax.NumberFormat = ttl.NumberFormat             ax.HasTitle = True             ax.Title.Caption = ttl.Caption             ax.Scaling.Orientation = c.chScaleOrientationMinMax         Case Else             ' Use a Bubble chart if there are three or more totals             If cht.Type <> c.chChartTypeBubble Then                 cht.Type = c.chChartTypeBubble             End If                          ' Use the first total for Y values,             ' second for X values, and third             ' for bubble size values                     cht.SetData c.chDimYValues, 0, 0             cht.SetData c.chDimXValues, 0, 1             cht.SetData c.chDimBubbleValues, 0, 2             ' Set the label and number format for the Y axis             Set ax = cht.Axes(c.chAxisPositionLeft)             Set ttl = pview.DataAxis.Totals(0)             ax.NumberFormat = ttl.NumberFormat             ax.HasTitle = True             ax.Title.Caption = ttl.Caption             ax.Scaling.Orientation = c.chScaleOrientationMinMax             ' Set the label and number format for the X axis             Set ax = cht.Axes(c.chAxisPositionBottom)             Set ttl = pview.DataAxis.Totals(1)             ax.NumberFormat = ttl.NumberFormat             ax.HasTitle = True             ax.Title.Caption = ttl.Caption             ax.Scaling.Orientation = c.chScaleOrientationMinMax                          ' Add the bubble size total caption to the chart title             Set ttl = pview.DataAxis.Totals(2)             cht.Title.Caption = cht.Title.Caption & _                 "  (Bubble Size Represents " & _                 ttl.Caption & ")"     End Select 'ctTotals     FormatChartFromStyles cspace      End Sub 'LoadChart() 

If the report has no totals, I leave the chart alone since there is no data to plot anyway. If one total exists, I change the chart type to Clustered Bar (though you can use many other types depending on the nature of the data). For example, a Line chart is most appropriate when time values are displayed down the rows of the report. A Pie chart might be more appropriate when no column fields exist because there is only one data dimension to plot. Note that I used the category axis's Scaling.Orientation property to reverse the order of the categories. By default, the Chart control displays categories in a Bar chart starting at the bottom of the Y axis and moving to the top. The PivotTable control naturally displays row members starting at the top row and heading down. To make the Chart control draw the categories in the reverse order, set the Orientation property to chScaleOrientationMaxMin.

If the PivotTable report has two totals, I switch the chart's type to Scatter and bind the first total to the Y values and the second to the X values. For three totals, I use the Bubble chart type and bind the third total to the bubble size values. The only chart type that can depict four values at once is the Open-High-Low-Close chart, but that obviously is only appropriate for certain kinds of data.

In all cases, I use the total's caption as the chart axis label and use the total's number format as the axis number format. This is an easy way to automatically title your chart elements and format numbers consistently among the different views. Finally, I call the FormatChartFromStyles function (detailed in Chapter 6) to format the chart based on the style sheet of the containing page.