The Basics of the Chart Component
The Chart component is a COM control that was built by the same team that developed charting in Microsoft Excel 2000. It provides basic business charting both as an onscreen COM control and as an in-memory, GIF-generating engine. In this first version, the Chart control supports all the two-dimensional chart types found in Excel (except for the Contour type) with the addition of the Polar, Stacked Pie, and Filled Scatter chart types. This version does not contain any three-dimensional chart types or effects.
One new feature that I will discuss in this chapter is the Chart control's ability to display more than one chart within the overall chart space of the component. The Chart control is actually composed of an entire chart space containing one or more charts that all share the same set of categories. (I will explain the term "categories" in more detail later in this section.) Normally, you will have only one chart in the control at a time; however, having multiple charts in the same chart space makes it easier to compare similar information at a glance. I will describe chart spaces in more detail toward the end of the chapter.
Like the Spreadsheet component, the Chart component has a number of basic features and some distinct terminology that you should become familiar with before we move on to more advanced topics.
The Nomenclature of Charting
During the development of a new feature in Excel 2000 charting, the OWC team conducted a number of usability tests to determine whether our design was easy to use. For those who have never heard of such tests, we take real customers from companies and homes throughout the Seattle area and put them in front of a prototype for a new feature. We ask the people to perform a number of tasks and watch to see how they approach them and whether the design was effective in helping them accomplish the assignment. Often, we find that our expectations were far from reality—meaning we have to go back and redesign the feature.
In one particular test, we showed people various charts we created in Excel with certain parts circled. We asked them to tell us what they thought the name of the circled part should be. Logically, you would expect us to find some commonality and discover the "name" that most people already associate with a particular element. We could then use that name in our documentation, programming models, onscreen user interface, and so on. Much to our dismay, we discovered absolutely no standard names for elements within a chart. You might expect that people would know which is the X axis and which is the Y axis, but many people don't remember much from their math classes and commonly mix them up.
NOTE
For those whose minds are now racing to remember which is which, the X axis is horizontal and the Y axis is vertical. Of course, most charts have what's called a category axis and a value axis, and their orientation depends on the particular chart type. Scatter and Bubble charts have X and Y axes since they compare two (or three) values against each other. In chart types such as Radar or Polar, the axes actually extend from the center of the chart, so X and Y are meaningless.
Since no common language seems to exist for describing elements of a chart, it is quite hard to talk about creating and manipulating charts. To gain any understanding of the Chart component's features and programming model, we must first define a number of key terms used in the component and look at what elements they represent. Many of the terms have specific connotations in different chart types, so if you see a chart type in the following discussion that you are not familiar with, refer to the "Supported Chart Types" for a screen shot and a description.
Series
A series is one of the most important constructs of the Chart component. In fact, most of the internal structures in this component are oriented around the series. Figure 31 points out the series in a Column chart. Notice that each series correlates to an entry in the chart's legend.
Figure 3-1. A Column chart containing two series.
A series represents a sequence of data points that you want to display in a certain manner. People commonly think that a chart has a particular type (such as a Line chart, Bar chart, or Pie chart). But in the Chart control, it's the series that has a particular type—which means you can create a combination chart by setting one series as a Line type and another as a Column type. All the data points in a series commonly have the same color (though you can override this, as we will see later). Plus, elements such as trendlines and error bars are attached to particular series.
By default, an entry in the legend exists for each series, but you can hide specific entries in the legend if you want. (I will explain how to do that later.)
Categories
Categories are a little harder to explain than series. Figure 3-2 points out the category labels contained in a Column chart.
Figure 3-2. Categories in a Column chart.
All charts have a notion of categories, but not all charts have a category axis. In Figure 3-2, the sales representatives' names are the categories, and each series contains a single data point in each category. In most charts, the intersection of a category and a series creates a data point. Note, however, that a particular series might not have a data point for a given category although the other series do. When this is the case, the Chart control merges all categories from all series and simply does not plot a data point at that series-category intersection.
Category axes differ from value axes in three important ways:
- There is no inherent ordering of categories.
- There is no minimum and no maximum category.
- Data points are neatly assigned to a specific category.
If Salesperson is your category axis, specific sale amounts are naturally attributed to specific salespeople, and no data points sit between salespeople. A value axis has a defined minimum and maximum, and the space along the axis is evenly divided into units that increase as you move from the minimum to the maximum. Each data point is therefore plotted wherever it lies along the axis.
A Scatter or Bubble chart does not have a category axis because its data points are defined by X and Y coordinates as well as a bubble size for Bubble charts. Although these chart types do not have category axes, the data points can still belong to specific categories and you can retrieve the category name for a given data point. This mechanism is useful for encoding extra information into the data points of a Scatter or Bubble chart, allowing you to display that information when the user's mouse hovers over a data point.
NOTE
Of course, in the real world, sales often can be attributed to more than one sales representative. However, most sales information systems perform an allocation and store each representative's contribution to the sale as a specific value that is then plotted on the chart. The point about data fitting neatly into a category is made to contrast discrete categories from continuous values. For example, a value of 1.4567454 can be plotted along a value axis between 0 and 2, but it does not fit into a discrete "bucket" on the axis the way data in categories do.
Values, Values, Values
In most of the simpler chart types, you have only one set of values to worry about. Figure 3-3 shows the sales values plotted by year by salesperson. This chart contains only one value per data point—which holds true for most of the simple chart types.
Figure 3-3. Values in a Column chart.
Scatter and Bubble charts, on the other hand, introduce the necessity for having two or three values for every data point. In a Scatter chart, each data point has an X value and a Y value, the combination of which defines an (X,Y) point in two-dimensional, Cartesian space. A Bubble chart adds a third value: a bubble size value that determines the radius of the bubble centered at the (X,Y) point. Optionally, the bubble size value can be set to represent the area of the bubble instead of the radius.
Things get a little more complicated when using a High-Low-Close (HLC) chart because again three values make up each data point, except that they are now called the high value, low value, and close value. An Open-High-Low-Close (OHLC) chart has four values associated with each data point, and the extra value is called, not surprisingly, the open value. (These types of charts are often called Stock charts because they are most commonly used to display such data.)
When dealing with a Polar chart, you need to provide another set of values: R values and Theta values. The R value defines the distance of the data point from the center of the chart, and the Theta value represents the angle away from a horizontal line passing through origin.
Axes
The term "axes" is probably more familiar to you. In the Chart component, axes have essentially the same meaning that they do in the world of geometry. However, the Chart control adds another layer of meaning to an axis by referring to it either as a category axis or a value axis. In Figure 3-4, the category and value axes are labeled.
Figure 3-4. Category and value axes in a Column chart.
A category axis is subdivided into equal segments—one for each distinct category, and data points are plotted in the middle of each category. No notion of a minimum or maximum exists for a category axis. A value axis, on the other hand, is a continuous axis that has a minimum and a maximum. Along a value axis, data points are plotted where they would fall between the minimum and maximum points.
Since the placement of the category and value axes depends on the chart type, "category" and "value" are logical names that do not directly map to X and Y. For example, a Column chart has the category axis extending horizontally along the bottom (X) and the value axis rising vertically on the left (Y). But in a Bar chart, the placements are reversed, with the category axis on the left and the value axis along the bottom. For more on the various chart types, see "Supported Chart Types."
Scaling
Although you have certainly heard the term "axis," you might not have heard the term "scaling" before. Every axis has a scaling, though you commonly use a scaling with a value axis. A scaling defines a measurement scale for its axis, determining the minimum and maximum values for the axis. The scaling also determines whether the intervals along the axis are linear or logarithmic. A linear scale subdivides the axis into even segments that increase linearly from the minimum value to the maximum value (for instance, 20, 40, 60, 80, 100). A logarithmic scale also divides the axis into even segments; however, the increment from one segment to the next is logarithmic instead of linear (for example, 1, 10, 100).
We will discuss axis scaling in more detail later in the chapter when we talk about split axes, as well as in Chapter 6, where we will see how to "zoom in" to show a portion of your data in more detail.
Trendlines
The Chart component supports the creation of one trendline for each series in your chart. As in Excel, a trendline is used to show the trend of data in a series. Trendlines are commonly used in trend analysis and forecasting when you want to predict what a certain value will be in the future if it keeps increasing or decreasing at the historical rate. Like Excel, the Chart control offers a few different trendline calculations, including linear, logarithmic, polynomial, exponential, and power. However, the Chart control does not offer the moving average trendline type found in Excel. Figure 3-5 shows an example of a trendline.
Error Bars
Error bars, shown in Figure 3-6, are short line segments extending from the data points that indicate some uncertainty about your data, known as the error amount. Each data point can display an error bar, which indicates that the data point's true value can be anywhere within the error range.
Why a Separate Scaling Object?You might be wondering why the concept of scaling is not just part of an axis. After all, it seems that the scaling determines the minimum and maximum values of an axis. However, the scaling's minimum and maximum values determine the dimensions of the viewable region called the plot area. An axis displays tick marks and labels, but it is the scaling that determines the exact pixel/value ratio for that dimension of the plot area. By separating the scaling from the axis, the Chart control can support charts that have no visible axes.
Figure 3-5. A trendline in a Scatter chart.
Figure 3-6. Error bars in a Column chart.
Like trendlines, error bars are attached to a series. An error bar is displayed for each data point in the series, and the collection of error bars for that series can be set to show a positive error amount, a negative error amount, or both. The error amount can be expressed as a percentage (such as +10%, -10%, or +/-10%), a relative value (such as +2, -2, or +/-2), or a custom amount (for example, an upper bound of 12 and a lower bound of 8 for a data point of 10). Error bars can also be data-bound, in which case the Chart control treats the values in the result columns as custom error values for each data point. Although Excel charting also provides Standard Error and Standard Deviation options for the error amounts, the Chart control does not yet natively support these. Of course, you could calculate these values yourself and use custom error amounts to display them in the chart.
Data Labels
A data label is a small piece of text placed next to a data point that you can set to display the data point's value, percentage in the series, category name, series name, or bubble size. (See Figure 3-7 for an example.) You can display any combinations of these pieces of information in a data label. Plus, you can control the font, color, and border formatting attributes—even which separator character string to use between each piece of information.
Figure 3-7. Data labels in a Column chart.
Data labels are obviously useful when you want to show numbers next to your data points, especially for comparing data points that might be very close to each other. The percentage contribution is of course useful any time you want to show the percent a data point contributes to the overall series, such as in a Pie chart. Showing the category name is a nice way to display extra categorical information in chart types that do not have category axes, such as Scatter and Bubble charts.
Like trendlines and error bars, data labels are attached to a series. You cannot format, hide, or show a data label for an individual data point. All manipulations to data labels affect all data points in the series.
Supported Chart Types
The first question I usually hear from a developer's mouth when I speak about the Chart component is, "What chart types are supported?" Chart types are the bread and butter of charting, so the more types the merrier. I have a reference book on my shelf that contains almost 450 pages of different chart types, chart elements, and information graphics techniques! (This book, Information Graphics: A Comprehensive Illustrated Reference [Management Graphics, 1997], is an excellent resource for anyone involved in information graphics, as are any of Edward Tufte's books.)
The first version of the Chart component includes the full set of two-dimensional chart types found in Excel 2000 (except for the Contour type), with the addition of Polar, Stacked Pie, and Filled Scatter chart types. The Chart control does not have any three-dimensional chart types or effects in this version, nor does it support the fancy fill effects offered in Excel.
Let's look at several examples of the supported chart types and discuss what kind of data they are useful for displaying.
Column and Bar Charts
The most typical chart types used in business, Column and Bar charts, show a filled bar for each data point, extending from the zero point on the value axis to the data point.
Most people don't make much of a distinction between Column and Bar charts—after all, they are essentially the same, they just extend in different directions. The Chart component uses the term "Column" to refer to a vertical column that extends up and down the screen and the term "Bar" for a horizontal bar that extends across the screen. Figure 3-8 shows an example of a Column chart and a Bar chart.
Figure 3-8. A Column chart (top) and a Bar chart.
These chart types typically are useful for data containing categories that do not need to appear in any particular order. Unlike Line charts, Column and Bar charts don't portray a sense of order or progression.
As with many of the chart types, there are a few Column and Bar chart subtypes. The default subtype, called Clustered Column or Bar, plots bars from different series adjacent to each other within each category. (Figure 3-8 shows a Clustered Column chart and a Clustered Bar chart.) The Clustered subtype is the most useful subtype when the different series are fairly unrelated or when they should not be aggregated visually. For example, if you plot a budget in one series and the actual amount spent in the other, you do not want to aggregate those values. Instead, you would want to compare the bars or columns side by side.
Stacked Column and Bar charts display the different series as stacked upon one another. In such charts, the length of the column or the bar represents the sum of the data points for the category. Figure 3-9 shows an example of a Stacked Column chart.
Figure 3-9. A Stacked Column chart.
Stacked Column and Bar charts are useful for displaying data in which the series' values can and should be aggregated to depict a visual total for each category. For example, if you plot sales information by country and by product, you might want to use a Stacked Column chart to show the total sales for each country (the category) across all products. The bar is still segmented by the exact value each product contributes—meaning top-selling products will have longer segments, while products that do not sell well will have shorter segments. Stacked charts are useful when it is not as necessary to assess the relative contribution as it is to assess the total for each category.
Finally, the 100% Stacked subtype is a bit like a Pie chart: it draws a bar or column all the way across the plot area and then subdivides the bar or column into segments representing the percent contribution of each series' data point. The key difference between this subtype and the Stacked subtype is that the length of each segment is the percentage of the data point's contribution to the total of the data points in that category, not the literal value. Since all the bars are the same length (100%), such a chart is not useful for comparing one category's total to another. However, this type of chart is useful for viewing the same type of information a pie chart shows, but for many categories and series at once.
Most of the other chart types described here have the same set of subtypes—Clustered, Stacked, and 100% Stacked. I will not redefine each of these three subtypes in the descriptions that follow, but I will indicate when they are available. Refer back to this section for a description of the subtypes and which types of data are appropriate to display using them.
Pie, Stacked Pie, and Doughnut Charts
Pie charts are also common in business charting, which is almost a shame since they provide the least dense (and the least efficient) display of information available. However, their simplicity also makes them very understandable, and often, very persuasive. For example, when showing a breakdown of market share information, the effect of an extremely large or small slice is indeed powerful. Figure 3-10 shows a typical Pie chart.
Figure 3-10. A Pie chart.
The important quirk of Pie charts is that the legend shows the category values instead of the series values. Most charts show the various series in the legend, but since a Pie chart shows only one series, the legend is used to show the category labels that correspond to the colored pie "slices."
A Pie chart is obviously useful for showing the percentage contribution or breakdown of a total. A Pie chart shows only one dimension of data because, as stated a moment ago, it can display only one series of data points.
The Stacked Pie and Doughnut chart types, however, can show multiple series at once, much like the 100% Stacked Column chart can display data for many series and categories at once. The only real difference between the Stacked Pie chart and the Doughnut chart is that the Doughnut chart has a hole in the middle (the "doughnut hole" if you will). Figure 3-11 depicts the same information in first a Stacked Pie chart and then a Doughnut chart.
Figure 3-11. A Stacked Pie chart and a Doughnut chart.
I admit that these are somewhat bizarre chart types; in fact, I recommend using them only in those unique circumstances under which no other chart type will suffice (for example, displaying the percentage chemical makeup of soil in concentric rings around a bomb explosion site). The concentric circles can be misleading for abstract data because their relative size and order are not based on any numeric value.
Line, Smooth Line, and Area Charts
Line charts and Area charts fall within the group of simpler chart types but unfortunately are not used as often as they should be. Figure 3-12 shows what a typical Line chart and Area chart look like.
Line and Area charts are useful for displaying data in which the categories actually have a meaningful order, such as a series of dates or times. For example, plotting sales over a series of dates or plotting stock prices over a series of hours is more effectively displayed in a Line chart than in a Column chart. This is because it is easier to tell whether there's a trend up or down when lines are drawn between the data points in a chart.
The only real difference between a Line chart and an Area chart is that in the Area chart, the section between the category axis and the line is filled with the series color. The occlusion that occurs when one series' values are higher than another's can make Area charts somewhat difficult to work with—unless you are using the Stacked subtype we discussed earlier. Since the series are drawn over each other in order, the last series drawn will cover any series previously drawn. Use nonstacked Area charts only when you know that a series has consistently higher values than all the series that follow it.
Figure 3-12. A Line chart and an Area chart.
As in the Column and Bar chart cases, Line charts and Area charts have the Clustered, Stacked, and 100% Stacked subtypes. However, the term "Clustered" is not commonly used to describe the default subtypes, and these defaults are simply called Line chart and Area chart without any special distinction.
Line charts have one other subtype that Column and Bar charts do not. Lines in a chart can be drawn either "straight" or "smoothed." It should come as no surprise that charts drawn with smoothed lines are called Smooth Line charts. Instead of drawing the line straight from one data point to another, the Chart component draws the line on a curve so that there are no jagged peaks or valleys.
Scatter and Bubble Charts
Scatter charts are used less often in business presentations, which is unfortunate considering that they can be a more powerful analysis tool than the simpler chart types described earlier. Although a Scatter chart has series and categories, it also has two values (rather than one) that determine the location of a data point. Each data point in a Scatter chart has an X value and a Y value, and the combination of the two determines its placement on the plot area.
The key difference between a Scatter chart and a Bubble chart is that the data points in a Bubble chart are circles that have dynamic size. A data point in a Bubble chart contains a third value called bubble size, which determines either the radius or the area of the bubble. Figure 3-13 shows typical Scatter and Bubble charts.
Figure 3-13. A Scatter chart and a Bubble chart.
Scatter charts and Bubble charts are useful for comparing two different values to discover a correlation or distribution pattern. For example, using a Scatter chart to plot a department's morale budget allocation against its revenue might reveal a strong correlation, showing that a high morale budget commonly increases revenue. (At least, most employees would like to think this is true!)
Scatter charts have a few unique subtypes. The default subtype is simply called Scatter Markers and uses markers (small geometric shapes such as diamonds or squares) to indicate the data points. Different shapes are used to indicate the different series. You can choose to connect the markers of each series with a smoothed or straight line. Furthermore, you can choose to have lines without any markers at all. The Chart component includes a final subtype that is not included in Excel. It allows you to fill in the polygon made from the data points and connecting lines, creating a Filled Scatter chart.
Fun with Filled Scatter ChartsYou can try a rather creative Filled Scatter chart demo on the companion CD. Open the DrawWithChart.htm file in the Chap03 folder, and click the chart surface to create points of a filled polygon. Double-click the mouse to end the shape. This demo was written by Jeff Couckuyt, one of the extremely talented Chart component developers, and was created using only the Chart control and a Filled Scatter chart type.
Keep in mind that Bubble charts have the same problem of occlusion as Area charts. A large bubble will hide any data points underneath it, so only use Bubble charts when you know the chance of occlusion is low; otherwise, consider setting the bubble fill to transparent.
Radar Charts
Radar charts do not seem to be typically used in the United States, but I understand that they are much more common in Asian countries for portraying data such as nutritional information about food products. Figure 3-14 shows what this interesting and useful chart type looks like.
Figure 3-14. A Radar chart.
A Radar chart has categories, series, and values like the other simple chart types; however, Radar charts plot the category labels in a circle surrounding the chart and contain spokes extending from the center of the chart out to each category label. Each spoke is a value axis. The data points for each category are plotted on the corresponding spoke's scale at the appropriate point and in the appropriate series color. The chart then joins the data points of each series with a line and optionally fills the series color from the line toward the origin. Filled Radar charts have the same old problem of occlusion that Area and Bubble charts have, so beware of using the Filled subtype unless you know that your data will not cause occlusion (or unless you are not concerned about it).
Radar charts also support the Smooth Line subtype we discussed earlier. Plus, you have the option of plotting data point markers in both the Smooth Line and Straight Line subtypes.
High-Low-Close and Open-High-Low-Close Charts
Anyone displaying information about stocks or financial securities will be interested in these two chart types. The High-Low-Close chart type (or HLC chart) displays a line segment for each category. Each line segment extends from the low to the high value and features a small tick mark that denotes the close value. Figure 3-15 shows an example of an HLC chart.
Figure 3-15. A High-Low-Close chart.
You should consider using this chart type any time you have data containing a range of values for a given period and a special value that needs to be marked within the range. For example, you could use an HLC chart to display temperature readings over an extended period of time.
An Open-High-Low-Close chart (or OHLC chart) adds one extra piece of information to the data of an HLC chart: the open value. The chart indicates the open value by displaying a filled rectangle between the open value and the close value, as Figure 3-16 shows.
The filled rectangle either will be the series color or it will be black, depending on whether the difference between the close value and the open value is positive or negative. Positive differences get the normal series color, while negative differences are shown as black. This shows the viewer whether the values increased or decreased during the specified period so that he or she can ascertain whether the value improved or worsened and by how much.
Figure 3-16. An Open-High-Low-Close chart.
Both the HLC and the OHLC chart types still maintain the notion of series. However, in an HLC chart type, the Chart component places different series in the same horizontal position. In other words, the control will draw multiple series over one another. Generally, this chart type is most useful with only one series of data points. OHLC charts, on the other hand, can manage multiple series and draw the bars of multiple series next to one another.
Polar Charts
The Polar chart type is the one new chart type that appears in the Chart component but not in Excel charting. Polar charts, which Microsoft Office users have been requesting for some time, are useful for displaying relationships between angles and distances. Polar charts are commonly used in the audio and radio fields, for example, to show the power and direction of a microphone's pickup. Figure 3-17 depicts a typical Polar chart.
Figure 3-17. A Polar chart.
The Polar chart includes the Smooth Line subtype, and you can choose whether to display data point markers with the lines.
Combination Charts
As mentioned at the beginning of the chapter, one of the great secrets of charting is that a chart does not really have a chart type. Instead, each individual series has a type, and if all series in a chart happen to have the same type, the Chart object's Type property returns that type. However, you can use this distinction to create more complex combination charts, in which you plot some series as columns or bars while plotting others as lines.
Not all chart types can be combined, and in this version of Office the Chart component allows you to combine only the Column, Line, and Area chart types. The most common combination chart used for business data is a mixture of the Column and Line chart types.
Loading Data
Now that you know what the various chart elements are called and what chart types the Chart control can display, you need to learn how to load a chart with data. Like most of the Office Web Components, the Chart control can load data from a variety of sources, and the loading can be performed by using the Chart Wizard in a design environment or by writing code. The Chart component binds to all the other Office Web Components—the Spreadsheet, PivotTable, and Data Source components—as well as to all other controls that implement the IDataSource interface (the standard interface for a data source control in Microsoft Internet Explorer or Microsoft Visual Basic, documented in the Microsoft Developer Network Libraries and the OLE DB SDK), ADO Recordset objects, and even literal arrays or delimited strings of data.
The general approach to loading data into the Chart control is to tell the chart where it should retrieve data from and what "parts" of the data source should be used for the series, categories, and values the current chart type requires. In the programming model, the Chart control refers to these chart elements as dimensions that you can bind to some part of the data source. When binding to a spreadsheet, the "part" of the data source you specify is a range reference, such as A1:C1. For an OLE DB data source, you specify what column name or ordinal index of the resultset to use. For the PivotTable control, you specify which pivot axis to use. (We will discuss this further in Chapter 4.) In literal data, only one "part" exists: the array or delimited string itself.
The Chart Wizard performs much of the binding for you, presenting you with a simple user interface for specifying this information. However, the Chart Wizard merely calls the public programming model of the Chart control, so by writing your own code you can do anything the Chart Wizard does—and more. The Chart Wizard is fairly self-explanatory, and its online help covers much of its use. Since this book focuses on developing custom solutions with the Office Web Components, I will not detail using the Chart Wizard here. Instead, I'll dive into the code you need to write when programmatically filling the chart with data.
Loading the Chart with Literal Data
The following subroutine, taken from the LoadFromLiteral.htm file in the Chap03 folder on your companion CD, shows how to load the Chart control with literal data:
'------------------------------------------------------------------------ ' LoadChartWithLiteral() ' Purpose: Loads the chart with literal data ' In: cspace reference to the ChartSpace object ' vSeries variant array or ' tab-delimited string of series names ' vCategories variant array or ' tab-delimited string of category names ' avValues array of variant array or ' tab-delimited string of values; ' one entry in the outer array per series ' Sub LoadChartWithLiteral(cspace, vSeries, vCategories, avValues) ' Local variables Dim cht ' Chart object we'll create in the chart space Dim ser ' Temporary series ' Grab the Constants object so that we can use constant names in ' the script. Note: This is needed only in VBScript -- do not include ' this in VBA code. Set c = cspace.Constants ' Clear out anything that is in the chart space cspace.Clear ' Create a chart in the chart space Set cht = cspace.Charts.Add() cht.HasLegend = True ' Now call SetData to bind the various dimensions ' Second parameter is c.chDataLiteral, meaning the last parameter ' is a variant array or a tab-delimited string cht.SetData c.chDimSeriesNames, c.chDataLiteral, vSeries cht.SetData c.chDimCategories, c.chDataLiteral, vCategories ' When loading the chart with literal data, you must ' load each series with values individually For each ser In cht.SeriesCollection ser.SetData c.chDimValues, c.chDataLiteral, avValues(ser.Index) Next 'ser End Sub 'LoadChartWithLiteral() |
When loading the chart with literal data, the data can be contained either in an array of variants or in a tab-delimited string, each element or token representing a different value. In the file on your companion CD, I pass the literal data as an array using the Array function that is supported in Microsoft VBScript as well as in Microsoft VBA.
The SetData method is used to pass the literal data, but note that the second argument (normally the data source index) is the constant chDataLiteral. This constant, which is equal to -1, tells the chart that the next argument is literal data and not part of a data source.
Also note that you must use the SetData method of the WCSeries object (the object representing a series) when passing literal values to the chart. Since the Chart control itself can accept only a one-dimensional array of values, if it allowed you to pass literal values to the SetData method of the WCChart object (the object representing a chart), it would have no way of knowing which values belong to which series. The previous procedure handles this by simply looping through the series collection and passing the appropriate array of values to the current series' SetData method.
Binding to the Spreadsheet Component
The following subroutine, taken from the LoadFromSpreadsheet.htm file in the Chap03 folder on your companion CD, shows how to bind a Chart component to ranges in the Spreadsheet component:
'------------------------------------------------------------------------- ' BindChartToSpreadsheet() ' ' Purpose: Binds a chart to specified ranges in the source spreadsheet ' In: cspace reference to the ChartSpace object ' sheet reference to the Spreadsheet object ' srngSeries string-based range reference to where the ' series names come from ' srngCategories string-based range reference to where the ' category names come from ' srngValues string-based range reference to where the ' values are ' Sub BindChartToSpreadsheet(cspace, sheet, srngSeries, srngCategories, _ srngValues, fSeriesInCols) ' Local variables Dim cht ' Chart object that we'll create in the chart space Dim ser ' Temporary series Dim rngValues ' Range object of values ' Grab the Constants object so that we can use constant names in ' the script. Note: This is needed only in VBScript -- do not include ' this in VBA code. Set c = cspace.Constants ' Clear out anything that is in the chart space cspace.Clear ' First tell the chart that its data is coming from the spreadsheet Set cspace.DataSource = sheet ' Create a chart in the chart space Set cht = cspace.Charts.Add() cht.HasLegend = True ' Now call SetData to bind the various dimensions ' Second parameter is zero, meaning the first data source should be ' used if there are multiple data sources cht.SetData c.chDimSeriesNames, 0, srngSeries cht.SetData c.chDimCategories, 0, srngCategories ' The spreadsheet can bind to one-dimensional ranges only, ' so loop through the series collection and set the values ' for each series individually Set rngValues = sheet.Range(srngValues) For Each ser In cht.SeriesCollection If fSeriesInCols Then ser.SetData c.chDimValues, 0, _ rngValues.Columns(ser.Index + 1).Address Else ser.SetData c.chDimValues, 0, _ rngValues.Rows(ser.Index + 1).Address End If Next 'ser End Sub 'BindChartToSpreadsheet() |
I would like to point out a few things about this example. First, to bind the Chart and Spreadsheet controls, I set the Chart control's DataSource property to the instance of the Spreadsheet control. By receiving the pointer to the Spreadsheet control instance, the Chart control can now ask the Spreadsheet control for cell values in the specified ranges.
Second, the SetData method is used to bind first the Series Names dimension and then the Categories dimension. You must bind the chart dimensions in this order: series names first, then categories, then values. In the rare case in which you have only one series of information, you can skip the Series Names dimension and just bind categories and values. Doing so will create one series for you with the default name Series, which you can change by setting the Name or Caption property of the WCSeries object.
My last point is that when binding to the Spreadsheet component, the Chart component needs to receive explicit range references for the values in each series. Unfortunately, you cannot just hand the chart a two-dimensional range of values and let it automatically figure out which series and categories the values belong to. Instead, you must pass a one-dimensional range reference to the last parameter in the SetData method for each WCSeries object. The previous example does this in a generic fashion by using the Spreadsheet component's Range object to yield a range reference for each column or row in the two-dimensional range, which in turn gets passed to the series' SetData method. The fSeriesInCols flag indicates whether the values for a given series are arranged down a column or across a row in the spreadsheet. If the flag is True, the Columns collection is used; if False, the Rows collection is used.
Also note that this example adds 1 to the series' index value. This is because the Columns and Rows collections are 1-based for compatibility with Excel's programming model, while the Chart component's series index is 0-based. Adding 1 to the series index yields the corresponding column or row in the range.
Binding to the Data Source Component
The following method, taken from the LoadFromDSC.htm file in the Chap03 folder on the companion CD, shows how to bind a Chart component to a Recordset returned from the Data Source component (DSC). (We'll discuss the DSC more thoroughly in Chapter 5.)
'------------------------------------------------------------------------ ' BindChartToDSC() ' ' Purpose: Binds a chart to a Recordset in the Data Source component. ' (This example creates a Pie chart.) ' In: cspace reference to the ChartSpace object ' dsc reference to the Data Source control ' sRSName name of Recordset to bind to in the ' Data Source control ' sCategories name of the result column containing categories ' sValues name of the result column containing values ' Sub BindChartToDSC(cspace, dsc, sRSName, sCategories, sValues) ' Local variables Dim cht ' Chart object that we'll create in the chart space Dim ser ' Temporary series ' Grab the Constants object so that we can use constant names in ' the script. Note: This is needed only in VBScript -- do not include ' this in VBA code. Set c = cspace.Constants ' Clear out anything that is in the chart space cspace.Clear ' First tell the chart that its data is coming from ' the Data Source control Set cspace.DataSource = dsc ' Next tell it what Recordset within the Data Source control ' it will bind to cspace.DataMember = sRSName ' Create a Pie chart in the chart space Set cht = cspace.Charts.Add() cht.HasLegend = True cht.Type = c.chChartTypePie ' Now call SetData to bind the various dimensions ' Second parameter is zero, meaning the first data source should be ' used if there are multiple data sources ' In this example of a Pie chart, we will add one ' series manually and use the SetData method there Set ser = cht.SeriesCollection.Add() ser.SetData c.chDimCategories, 0, sCategories ser.SetData c.chDimValues, 0, sValues ' Finally, for this example, add some ' data labels since it's a Pie chart Set dls = ser.DataLabelsCollection.Add() dls.HasPercentage = True dls.HasValue = False End Sub 'BindChartToDSC() |
The DSC can retrieve data from and provide data to both the Chart component and the PivotTable component. It also implements the same data source COM interface (IDataSource) that Visual Basic and Internet Explorer data source controls implement, so this code can be used for any other valid data source control used in those environments.
The DataSource and DataMember properties of the ChartSpace object are fundamental to this example. Since they are part of the data-binding standards established by Visual Basic and Internet Explorer, you will commonly see them on other data-bound controls in those environments. The DataSource property is set to point at the control providing the data (in this case the DSC), and the DataMember property is set to a string value naming the specific data set desired. Because a DSC can expose many data sets at once, the DataMember property is used to tell the Chart control which data set to request. If you leave the DataMember property blank, the Chart control will ask for the default data set, the identity of which is determined by the DSC.
The SetData method is used in this example much the same way it was used in the BindChartToSpreadsheet method, except that the "data part" (the last parameter to SetData) is now the name of a column in the Recordset returned from the DSC. Alternatively, this data part can be the ordinal index of the column (0, 1, 2, and so on). The Chart control will pass this to the ADO Fields collection object's Item method, so any value that is valid for that method can be used as the last parameter to the SetData method. Typically, you use column names if you don't expect them to change over time but do expect the ordinal positions to change; you use ordinal indexes if you don't expect them to change but do expect the column names to change.
As stated earlier, an interesting twist of Pie charts is that the entries in the legend are categories, not series. Therefore, when binding a Pie chart, you should bind the column you want to appear in the legend to the Categories dimension rather than the Series dimension. This also applies to Stacked Pie charts and Doughnut charts.
The last code block in this example creates some data labels (which we just discussed) to show the percentage each data point contributes to the whole. The Chart control can show a number of values for each data point, and by default the actual number value is displayed. Since I wanted to show only the percentage contribution of each slice, I set the HasPercentage property to True and HasValue to False.
Although this particular example creates a Pie chart, you can use any of the supported chart types when binding to the DSC. I chose to create a Pie chart in this example only to show how binding is performed using the Pie chart type.
Binding to a Recordset
The following function, taken from the LoadFromRecordset.htm file in the Chap03 folder on the companion CD, shows how to bind a Chart control to an ADO Recordset object:
'------------------------------------------------------------------------ ' BindChartToRecordset() ' ' Purpose: Binds a chart to a Recordset. (This example creates a ' Scatter chart.) ' In: cspace reference to the ChartSpace object ' rst reference to Recordset object to bind to ' sfldSeries name of the series field ' sfldXValues name of the field containing the X values ' sfldYValues name of the field containing the Y values ' Sub BindChartToRecordset(cspace, rst, sfldSeries, sfldXValues, _ sfldYValues) ' Local variables Dim cht ' Chart object that we'll create in the chart space Dim ser ' Temporary series pointer Dim ax ' Temporary axis pointer ' Grab the Constants object so that we can use constant names in ' the script. Note: This is needed only in VBScript -- do not include ' this in VBA code. Set c = cspace.Constants ' Clear out anything that is in the chart space cspace.Clear ' First tell the chart that its data is coming from the Recordset Set cspace.DataSource = rst ' Create a Scatter chart in the chart space Set cht = cspace.Charts.Add() cht.HasLegend = True cht.Type = c.chChartTypeScatterMarkers ' Now call SetData to bind the various dimensions ' Second parameter is zero, meaning the first data source should be ' used if there are multiple data sources ' In this example of a Scatter chart, we will bind ' two value dimensions: X and Y cht.SetData c.chDimSeriesNames, 0, sfldSeries cht.SetData c.chDimXValues, 0, sfldXValues cht.SetData c.chDimYValues, 0, sfldYValues ' Finally, let's add some axis labels using ' the column names as the axis captions Set ax = cht.Axes(c.chAxisPositionBottom) ax.HasTitle = True ax.Title.Caption = sfldXValues ax.Title.Font.Name = "Tahoma" ax.Title.Font.Size = 8 ax.Title.Font.Bold = True ax.NumberFormat = "#,##0" Set ax = cht.Axes(c.chAxisPositionLeft) ax.HasTitle = True ax.Title.Caption = sfldYValues ax.Title.Font.Name = "Tahoma" ax.Title.Font.Size = 8 ax.Title.Font.Bold = True ax.NumberFormat = "$#,##0" ' Let's also set the marker size a bit smaller than normal For Each ser In cht.SeriesCollection ser.Marker.Size = 5 Next 'ser End Sub 'BindChartToRecordset() |
You will no doubt quickly notice that this example is similar to the previous example involving the DSC, except that this example uses a Scatter chart. This is because the ADO Recordset object is itself a valid data source in Visual Basic and Internet Explorer, and it implements the same data source interface as the DSC. This makes it possible to set the Chart control's DataSource property to point to the Recordset object just as you would set it when using the DSC. However, the Recordset object by definition has only one data set to expose, so the DataMember property does not need to be set when binding to it.
Because the chart type used in this example is a Scatter chart, the code sets two value dimensions: X Values and Y Values. As described earlier in the section on supported chart types, a Scatter chart uses two values for each data point, so we need to bind the X Values dimension to one column in the Recordset and the Y Values dimension to another column. (You could bind them to the same column, but that would make for a highly correlated Scatter chart!)
An Accident of Good ArchitectureWhen I sent this chapter to the Chart component developers for review, one of them commented that the component does not actually "support" loading from a Recordset and that the OWC team had not officially tested this scenario. However, almost all the demos and real pages we wrote internally use this method, so in actuality, this scenario was quite well tested.
The reason that loading the Chart component from a Recordset works is an accident of good architecture. When the Visual Basic and Internet Explorer development teams selected the standard data source interface (IDataSource), the ADO team decided that it made sense for the Recordset object to implement this interface since it could easily return the underlying IRowset interface from the GetDataMember method. Because the Chart control uses IDataSource when loading data from a DSC, it all just worked. To the Chart control, the Recordset object looks like any other data source control.
When binding to a Recordset object, you must ensure that the Recordset is using the Microsoft Windows Cursor Engine (WCE) or is capable of being sorted and scrolled. The WCE is an ADO component that provides scrolling, sorting, filtering, and more on any OLE DB Rowset regardless of the Rowset's source or native capabilities. To use this engine, set the CursorLocation property of your ADO Connection or Recordset object to adUseClient, which has a value of 3 if you are in an environment that does not recognize constants. To ensure that the Chart control can scroll around the Recordset, you can use the adOpenStatic cursor type, which also has a value of 3. Use adOpenStatic or 3 for the CursorType parameter of the Recordset's Open method or for the CursorType property of the Recordset object. (Chapter 4 will discuss the WCE in more detail.)
For an example of setting the necessary Recordset properties, see the full source listing in the LoadFromRecordset.htm file on the companion CD. When viewing this file, note the use of the column names for the axis captions. Scatter charts have two value axes (X and Y), so you should give those axes titles and explain what values you are showing on them. Using the column names in the Recordset can be an easy way to label these axes; of course, you can set your own captions if the column names in the Recordset are not intelligible.
Binding to the PivotTable Component
The last possible source of data for the Chart component is the PivotTable component. (We will talk more about this component in the next chapter.) As you might expect, this component also implements the same data source interface that all valid data sources expose in Visual Basic and Internet Explorer, so the following example, taken from the LoadFromPivot.htm file in the Chap03 folder on the companion CD, looks similar to the DSC example we discussed earlier but has a few important differences:
'------------------------------------------------------------------------ ' BindChartToPivot() ' ' Purpose: Binds a chart to a PivotTable component ' In: cspace reference to the ChartSpace object ' ptable reference to the PivotTable object ' fSeriesInCols Boolean flag indicating whether the series ' of the chart should come from the column ' axis or the row axis of the PivotTable control ' Sub BindChartToPivot(cspace, ptable, fSeriesInCols) ' Local variables Dim cht ' Chart object that we'll create in the chart space Dim ax ' Temporary axis reference Dim fnt ' Temporary font reference ' Grab the Constants object so that we can use constant names in ' the script. Note: This is needed only in VBScript -- do not include ' this in VBA code. Set c = cspace.Constants ' Clear out anything that is in the chart space cspace.Clear ' First tell the chart that its data is coming from the ' PivotTable component Set cspace.DataSource = ptable ' Create a chart in the chart space Set cht = cspace.Charts.Add() cht.HasLegend = True cht.Type = c.chChartTypeBarClustered ' Now call SetData to bind the various dimensions ' Second parameter is zero, meaning the first data source should be ' used if there are multiple data sources If fSeriesInCols Then cht.SetData c.chDimSeriesNames, 0, c.chPivotColumns cht.SetData c.chDimCategories, 0, c.chPivotRows Else cht.SetData c.chDimSeriesNames, 0, c.chPivotRows cht.SetData c.chDimCategories, 0, c.chPivotColumns End If 'fSeriesInCols ' Set the values dimension. The value ' you pass for the data reference (the last parameter) ' is the index of the total you want to use. ' Since there is only one total in this example, ' we pass zero, indicating the first one. cht.SetData c.chDimValues, 0, 0 ' Finally, let's add an axis title to the value ' axis, using the label on the pivot total ' as the caption, and set the number format Set ax = cht.Axes(c.chAxisPositionBottom) ax.HasTitle = True ax.Title.Caption = ptable.ActiveView.DataAxis.Totals(0).Caption Set fnt = ax.Title.Font fnt.Name = "Tahoma" fnt.Size = 8 fnt.Bold = True ax.NumberFormat = ptable.ActiveView.DataAxis.Totals(0).NumberFormat End Sub 'BindChartToPivot() |
As in the earlier Recordset and DSC examples, to bind the Chart control to the PivotTable control, you start by setting the Chart control's DataSource property to point to an instance of the PivotTable control. As in the Recordset example, the PivotTable control has only one data set to expose, so you do not need to change the DataMember property from its default setting.
The critical difference between this example and the DSC example is the use of special constant values for the last parameter to the SetData method. As you will remember, this last parameter specifies which part of the data you want to bind to the specified chart dimension. In a PivotTable control, the logical parts available are the row and column pivot axes and all the totals in the view. In a PivotTable control, the row axis refers to all the labels displayed down the left side of the table, and the column axis refers to all the labels displayed across the top of the table (more on these axes in the next chapter). All the Chart control needs to know is which axis you want to bind to the Series Names dimension and which axis you want to bind to the Categories dimension.
Binding the various values dimensions of the Chart control is slightly different. Since a PivotTable report can display many totals at once, the Chart control needs to know which total you want to use for the specified values dimension. You indicate this by passing the ordinal index of the total as the last parameter to the SetData method. In the BindChartToPivot example, we use a Clustered Bar chart so that each data point has only one value. We tell the Chart control to use the total at index zero (the first one, since this is 0-based) for the data point values. Note that this is the ordinal index of the available totals shown in the PivotTable control's current view—not all the possible totals in the data source.
While the PivotTable control does implement the standard COM interface for a data source control, it does not know how to return ADO Recordsets or OLE DB Rowsets. The Chart control has special code for knowing how to read the PivotTable control's crosstab data display and properly ignore subtotals and grand totals, which if used, would skew the chart's scale. Therefore, even though the Chart control can consume data from the PivotTable control, not all data-bound controls will be able to use the PivotTable control as their data source.
Chart and Axis Titles
Charts can be effective mechanisms for displaying large quantities of information in a quick-to-assimilate visual manner. However, the data plotted in charts is rarely self-explanatory; often you will want to add a descriptive title to your chart or to the axes displayed within it.
Using Multiple Totals for Multivalued ChartsThe Scatter, Bubble, Polar, and OHLC chart types all have something in common: they need more than one value to determine a data point on the chart. A Scatter chart requires both an X value and a Y value, a Bubble chart needs both those values as well as a bubble size value, a Polar chart needs Theta and R values, and an OHLC chart needs the four values its name suggests. Since PivotTable reports can show more than one total at a time, it's often desirable to map those totals to the different chart values to make a single data point.
You can do this by specifying the index of the total in the PivotTable view as the last parameter to the SetData method. For example, to bind the first total to X and the second total to Y in a Scatter chart, you would write this code:
Sometimes you want to use those multiple totals with a chart type that uses only one value per data point, such as a Stacked Column chart. In this case, you might want the caption of the total to appear as a nested category or a nested series label. To do this, you use a special constant:
This setting would use any total captions appearing on the row axis as nested category names on a hierarchical category axis. To get the results you want, you also should set the TotalsOrientation property of the PivotView object to the plTotalOrientationRow constant so that the total captions are displayed on each row.
Perhaps you want to display multiple totals as different series in the chart. To do this, leave the column axis on the PivotTable report empty, add the totals you want to appear as different series, and then write the following code:
This code will create a series in the chart for each total in the PivotTable report. Refer to the sample file PivotTotalsAsSeries.htm in the Chap03 folder on your companion CD to see what this looks like.
If you add more than one chart to the Chart control (detailed in the section "Multiple Charts in 'Chart Space'"), you can give each chart its own title. In fact, you can give the Chart control itself a global title that will be displayed above all the individual charts. Chart titles can be formatted with all the basic font attributes (name, size, bold, italic, underline, and color). You can also set their backgrounds to a specific color or leave them transparent. The same is true for axis titles.
By default, newly created charts will have neither a chart title nor axis titles. You can add chart and axis titles either at design time using the Property Toolbox or at runtime using code. The following method, taken from the AddTitles.htm file in the Chap03 folder on the companion CD, shows how to add a chart title:
'------------------------------------------------------------------------ ' SetChartTitle() ' ' Purpose: Sets the chart's title ' In: cht reference to a chart ' sTitle new title caption ' Sub SetChartTitle(cht, sTitle) Dim fnt ' Temporary font reference ' If the title is nonblank If Len(sTitle) > 0 Then ' Add a title if necessary cht.HasTitle = True ' Set the caption and its font formatting cht.Title.Caption = sTitle Set fnt = cht.Title.Font fnt.Name = "Tahoma" fnt.Size = 10 fnt.Bold = True Else ' Title is blank. Remove it. cht.HasTitle = False End if End Sub 'SetChartTitle() |
You add an axis title the same way you add a chart title, except that the first parameter of the method accepts an Axis object reference instead of a Chart object reference.
Also note that you can set the Color property for the title's font or background to either an RGB color value or to one of the Internet Explorer color names—for instance, "FireBrick" or "PapayaWhip". This also applies to any use of color in the Chart control, such as the chart's background color, plot area color, series color, and so on.
Axis Labels
By default, the Chart control will include labels on all your axes to show where a data point lies on the scale (for value axes) or which category a data point belongs to (for category axes). You might want to adjust a few aspects of these labels using either code at runtime or the Property Toolbox at design time.
For value axes, the labels show numeric points on the overall value scale of the axis. These numbers initially have no formatting unless the source is a spreadsheet with cells containing explicit numeric formatting. To change the default number formatting, set the axis's NumberFormat property to a new number format name or string. The list of named formats you can use appears in the Spreadsheet control's help file (opened by clicking the Help button on the spreadsheet) under the topic, "Number formats in a spreadsheet." You can also build a custom format string just like you can in Excel, and the symbols used in custom number formats are documented in the Excel 2000 help files starting with the overview topic titled, "Create a custom number format."
For both value and category axes, you can choose to drop some of the labels—for example, showing only every fifth label. This is useful only for axes that have too many labels to show without overlapping and for which the dropped labels can be inferred from visible labels such as a series of dates. Category axes will not drop labels by default. But suppose you have a large set of dates on a category axis. You can choose to drop some of them by setting the TickLabelSpacing property of the appropriate WCAxis object, either by selecting the axis and using the Property Toolbox at design time or in code at runtime. The setting for this property determines how many labels to skip between the labels that appear. Note that this property affects only labels, meaning the axis will still show tick marks where each label would have appeared. However, you can drop some of these tick marks as well by adjusting the TickMarkSpacing property to the same value as TickLabelSpacing.
The Chart Legend
By default, newly created charts do not have a legend. If you plan on showing more than one series of data points, you might want to add a legend to your chart to explain which color maps to which series. To add a legend, either select the chart and use the Property Toolbox at design time, or set the HasLegend property of the WCChart object to True in code at runtime.
Initially, the legend will contain one entry per series by default, but you can adjust that. To hide a legend entry, use the LegendEntries collection of the WCLegend object to retrieve the legend entry you want to hide, and then set the returned object's Visible property to False. At design time, you can simply select the legend entry itself and press the Delete key.
If you have more than one chart in the Chart control, you can create a legend for each individual chart or you can create one for the control as a whole. If you are showing multiple Pie charts, for example, it probably makes the most sense to show only one legend for all the pies since the color/category mapping will be same for each chart. Other chart types that show the series in the legend can benefit from individual legends if the set of series displayed differs among charts.
One rather annoying aspect of using a single legend for multiple charts that show series in the legend (charts other than Pie, Stacked Pie, and Doughnut) is that the Chart control will initially add an entry to the legend for each occurrence of each series in each chart. If you are showing two series on five charts, the chart space legend will contain ten legend entries, the same two entries repeated five times. To eliminate the extra legend entries, select and delete them at design time or use the WCLegendEntry object's Visible property in code as described earlier.
EAN: 2147483647
Pages: 111