This Daily Drill Down on the basics of creating charts programmatically with the Chartspace control has two main objectives. First, it provides IT pros with code samples for creating charts on Web pages for their users. Second, it demonstrates the creation of charts based on data behind the chart, a spreadsheet control on the Web page, or an Access database.
The Office XP Web Component series
This is the third Daily Drill Down in a series on Office XP Web Components. This installment illustrates how to automatically generate charts of data on Web pages. The first Daily Drill Down presented an overview of the four Web Components that ship with Office XP, and it introduced how to manually use each of these components. The second Daily Drill Down focused on the Spreadsheet component and its computational and data access capabilities.
Creating clustered column charts
|A 3D clustered column chart programmatically created with the Chartspace control|
Figure A shows a clustered column chart with 3D formatting programmatically generated with the Chartspace control. The chart displays the sum of extended prices, or sales, by year for 1997 and 1998. The display clusters the sales results by product category. The chart's formatting includes labels for the bottom and left axes, a list of the category names on the bottom axis, dollar formatting for the extended price values, and a legend.
You can hand-code the creation of a chart like the one in Figure A with FrontPage 2002. For example, you can enter your code on the HTML tab of a blank page; see the code for Figure A in Listing A. While you can add charts manually, programming them is more robust, especially when you have to edit a chart or use a copy of the chart for another application. This is because you can copy your code for another chart instead of having to manually go through all the steps to generate a chart. Your code never forgets a step, and your formatting will always be consistent with a previously created chart.
It's a straightforward process to program a chart. First, you add the Chartspace control to the page. You can do this from the FrontPage menu or you can just paste in the code from a prior sample. Second, you program the control with a scripting language, such as VBScript or JScript. This Daily Drill Down will demonstrate the use of VBScript because most Office developers and power users will already have some degree of familiarity with one or more VB dialects, such as VBA.
When you initially add a Chartspace control to a Web page, use an OBJECT tag. Set the tag's classid attribute to the unique identifier, a GUID, for the Chartspace control. You can customize the appearance and operation of the Chartspace control with attribute settings and PARAM tags. For example, Listing A uses height and width attribute settings to designate a Chartspace control that is 80 percent of the browser window's width and height. These settings leave room for other content on the page along with the chart. An id attribute provides a handle, ChartSpace1, which your code can use to reference the Chartspace control.
After specifying the Object tag for the Chartspace control, Listing A begins a SCRIPT block. This block contains a window_onload event procedure for the browser window. Whenever the browser window initially opens or a user forces a refresh of the window's contents, the window_onload event procedure fires. The procedure starts by creating a shortcut key (C) for the constants to set parameters for the Chartspace control and its hierarchically dependent objects. Without this shortcut, your code would have to reference constant values instead of constant names when setting parameters. (It’s a lot easier to remember that c.chDataBound denotes a bound object than to remember that the number 0 specifies it.)
Next, the code deletes any current charts in the Chartspace control and adds a new empty one. Failing to delete prior charts can bring various problems to your program for creating charts. In general, this is a good initial step, but it’s not required for all chart types. After deleting any prior charts, the program invokes the Add method for the Charts collection of the Chartspace object. The ch variable is a shortcut for the chart object just as C is a shortcut for the Chartspace constant names. The Microsoft documentation refers to the object created by the Add method as a ChChart object (this is not a typo). The first chart that you add to a page has an index value of 0; the next is 1, and so on. The Charts collection for the Chartspace object permits the generation of multiple charts (up through 16) in a single chart workspace.
Once you have a blank chart on a page, you can start to format it. To begin, specify a chart's Type property because this setting can have a profound impact on the availability and operation of other hierarchically related objects, as well as properties and methods. Listing A shows the constant name for designating a 3D clustered column chart. Notice the reference to the constant’s shortcut (C) before the specification of the constant for the chart (chChartTypeColumnClustered3D). A period delimits the constant's shortcut from the constant name for the chart type. If you don’t specify a chart's Type property, the Chartspace control reverts to creating a clustered column chart based on the chChartTypeColumnClustered constant.
A column chart plots one or more series of data vs. a set of categories. You designate values for a column chart to plot by making assignments for the categories and series. Listing A begins formatting its charts by naming its series 1997 and 1998 with the chart's SetData method. An especially important argument for the method is an Array function to specify the two series names. The chDimSeriesNames argument indicates what the SetData method assigns. The chDataLiteral argument indicates that the statement uses literal values in the statement as opposed to referencing values in another source. Next, the listing designates eight product category names. The code designates two series with extended prices (or sales). One series is for 1997, and another is for 1998. Both series contain a value for each product category. The listing designates each series of values by applying the SetData method to a SeriesCollection object that belongs to the chart object. The SetData statement for the series specifies series values in the order that they appear in the SetData statement for category names.
After specifying the data for the chart to plot, the event procedure moves on to formatting the chart's layout. For example, Listing A adds a legend by setting the chart's HasLegend property to True. Notice that the listing spells True in lowercase. VBScript is not case-sensitive for names or value assignments—even values such as True and False. Next, the code sets a shortcut for the chart's left axis. Then, it assigns a caption for the axis and formats the caption with a font name, size, and bold type. In addition, the code designates a currency format for the numbers on the axis. The listing also shows an alternate format for specifying a currency format in a commented line. The next block of lines assigns similar formatting to the category names along the chart's bottom axis. Finally, an end sub statement closes the event procedure.
There are more than five dozen Type property specifications for a chart. While some of these designate charts with dramatically different looks, others lead to the creation of charts that are only slightly different from one another. The preceding sample used chChartTypeColumnClustered3D, but there are six other chart Type constants with the word Column in their name. However, you can’t freely exchange all constants with the word Column in their name. Many column, line, and area chart types take similar content and format designations across chart types. This lets you experiment with different ways of displaying data by changing just one value in a program: the chart's Type specification. The chart Type values that you can swap for the one used in Listing A include the following:
- · chChartTypeArea
- · chChartTypeArea3D
- · chChartTypeAreaStacked
- · chChartTypeAreaStacked3D
- · chChartTypeColumn3D
- · chChartTypeColumnClustered
- · chChartTypeColumnStacked
- · chChartTypeColumnStacked3D
- · chChartTypeLine
- · chChartTypeLine3D
- · chChartTypeLineMarkers
- · chChartTypeLineOverlapped3D
- · chChartTypeLineStacked
- · chChartTypeLineStacked3D
- · chChartTypeLineStackedMarkers
Creating clustered bar charts
A bar chart is a column chart turned on its side. Figure B demonstrates the outcome of flipping the chart in Figure A on its side. I enlarged the percent of a browser's width that the Chartspace control takes to show the category names without any truncation.
|A 3D clustered column chart flipped on its side to appear as a 3D clustered bar chart|
To perform the flip of the axes from Figure A to Figure B, swap the chChartTypeColumnClustered3D Type property value with a chChartTypeBarClustered3D value. When you do this, you change the values along the left and bottom axes of the chart. This means that you must also update the formatting for these axes. For example, you have to drop the currency setting for the NumberFormat property of the chart's left axis and add a currency format to the bottom axis. Failing to make this formatting modification generates a run-time error when you attempt to display the bar chart in a browser.
Aside from the new Type property value and the altered axis receiving a currency format, the window_onload event procedure is identical for the column and bar charts in Figures A and B. The following excerpt (see Listing B) from the event procedure for the bar chart shows the new formatting for the chart's axes. Notice that you can specify a currency format with a custom format as in Listing A, or you can use the predefined numeric keyword, Currency. Using the Currency keyword automatically applies the currency setting based on the system's local setting instead of requiring an explicit format that may not be appropriate for all the different countries in which an application runs.
Listing B: An excerpt that shows new code for formatting the chart that appears in Figure B. Compare this excerpt to the comparable section in Listing A.
'Format left axis
set ax = ch.Axes(c.chAxisPositionLeft)
ax.HasTitle = True
ax.Title.Caption = "Category Names"
ax.Title.Font.Name = "Tahoma"
ax.Title.Font.Size = 8
ax.Title.Font.Bold = true
'Format bottom axis
set ax = ch.Axes(c.chAxisPositionBottom)
ax.HasTitle = True
ax.Title.Caption = "ExtPrice"
ax.Title.Font.Name = "Tahoma"
ax.Title.Font.Size = 8
ax.Title.Font.Bold = true
'Format axis as currency with second of two options
'ax.NumberFormat = "$#,##0"
ax.NumberFormat = "Currency"
Again, you can freely swap the chChartTypeBarClustered3D Type property specification for the chart in Figure B with one of several other Type property settings. The list with which you can exchange chChartTypeBarClustered3D includes:
- · chChartTypeBarClustered
- · chChartTypeBarStacked
- · chChartTypeBarStacked3D
Creating pie charts
A standard pie chart differs fundamentally from both column and bar charts in that a pie chart contrasts the values within a single series. If you need to contrast multiple series and you can’t use either a column or a bar chart, consider one of two options for using pie charts. First, you can use a stacked pie chart. This approach stacks pie charts within one another; the effect is like stacking smaller pots in larger ones. Second, you can add multiple pie charts to the same Web page using one pie chart for each series. This section presents three samples, including one for a stacked pie chart.
|A 3D pie chart programmatically created with the Chartspace control|
Figure C shows a standard pie chart that contrasts total extended price by product category in 1997. Each pie slice denotes a different product category. The legend to the right of the pie indicates the product category that each slice represents by its color.
You can construct the VBScript for the pie chart in Figure C so that it’s very similar in design to that for the bar and column charts. However, a standard pie chart restricts you to displaying results for just one series. That is why the chart in Figure C shows data just for 1997.
Listing C presents the window_onload event procedure, which fires whenever the Web page in Figure C opens initially or a user refreshes the page. As you can see, the instructions and syntax are the same as in Listing A up until the assignment of the chart's Type property. Since the pie chart in Figure C has 3D formatting, its Type property setting is chChartTypePie3D. The code for assigning the values for the category names is again identical to that for either a column or bar chart. However, the code sample invokes the SetData method just a single time for a series—this is for the extended price totals in 1997. By setting the chart's HasLegend property to True, the code creates a road map for deciphering which pie slice color corresponds to which product category.
Using a chart's Type property, you can format pie chart features. Figure C illustrates one of these: 3D formatting. By using chChartTypePie as the Type property setting, your code can generate a pie chart without the 3D formatting. You can also elect to explode all the pie slice segments by specifying either chChartTypePieExploded or chChartTypePieExploded3D as the Type property value. The difference between these two property values is that the latter one explodes all the slices and gives them 3D formatting, as well.
Using the Explosion property, you can selectively specify which pie slices explode, as well as how much they explode, from the other pie slices. See the Explosion Property topic in the owcvba10.chm Help file for sample code illustrating the use of this property.
One deficiency of the pie chart in Figure C is that it doesn’t label the pie slices. Your only guide to the slices is the legend, but all it indicates is which product category a slice represents. Figure D shows a modified version of the chart that represents the aggregated extended price value associated with each pie slice. The numerical information more precisely represents the value of each chart. For example, from this version of the pie chart, you can tell that the Dairy Products category had total sales of $115, 387.63.
|A pie chart with numeric values for pie slices|
The event procedure for the chart in Figure D is identical to the one in Listing C up to the point where the setting of the chart object's HasLegend property is equal to True. Therefore, the excerpt from the window_onload event procedure (see Listing D) picks up from that point through the concluding end sub statement. This portion of the event procedure begins by creating a DataLabelsCollection object with the Add method. A DataLabelsCollection object is for a series in a chart. You can use this kind of object with pie charts and other kinds of charts. With a DataLabelsCollection object, you can return the series values for chart elements, such as the pie slices in Figure D. You can also present other aspects of the data, such as the category name. The code sample in Figure D illustrates the return of both series values and category values. In addition, the sample coding techniques for formatting the data labels appear in the chart.
The sample gives two options for displaying data labels depending on the value for usecategoryname. If usecategoryname is False, then the chart appears as in Figure D. This includes a legend, and it shows the series values in a relatively large font to make them easy to read. If usecategoryname is True, then the code removes the legend from the chart to make more room for the printing of the category name and series value on each pie slice. In addition, the code reduces the font size to allow more content to appear on a slice with a minimal amount of overlap to other slices. To help improve the visibility of the smaller font, the code sets the color for the category names and series values to magenta.
Before entering an If…Else…End If statement, the excerpt in Listing D applies a currency format to the series values, and it sets the whole data label to appear in a bold font. These settings apply no matter which path through the If…Else…End If statement the procedure takes. Notice also that you can use the With…End With construct to perform multiple property value assignments to an object, which in this case is the dl shortcut pointing at the DataLabelsCollection object.
The excerpt sets the usecategoryname variable to False by default. However, you can prompt the user for this value to make the variable's assignment interactive. VBScript supports the familiar InputBox function to help you execute the prompt. When the usecategoryname value is False, the procedure takes the Else path through the If…Else…End If statement. In this path, the code makes two property assignments for the DataLabelsCollection's Font object. First, it sets the color to black and the font size to 12 points. If usecategoryname is True, the code turns off the chart's legend by setting its HasLegend property to False. Then, it makes three property assignments to the Font object for the data labels. The first of these sets the HasCategoryName property to True. This assignment formats the chart to display category names along with series values for pie slices. A default symbol (a comma) delimits the category name and series value for each slice. You can use other symbols, such as ":", by setting the Separator property for the DataLabelsCollection object. See the Separator Property topic in the owcvba10.chm Help file for details on the syntax.
Figure E presents the final pie chart. This example demonstrates the appearance of a stacked pie chart. Recall that you can use this design to represent the values for multiple series with a single pie chart. The inner pie is for extended price values by product category in 1997, and the outer pie is for values in 1998. The cursor rests on an outer pie slice for Confections. The cursor tip displays the series name (1998), the category name (Confections), and the series value with its percent of the total of all 1998 values in parentheses.
|A stacked pie chart shows the two series appearing in Figure A.|
Listing E shows the event procedure to generate the stacked pie chart in Figure E. This procedure differs in three ways from the code in Listing C. First, it assigns a Type property value of chChartTypePieStacked. Second, the code in Listing E invokes the SetData method to assign names to its two series. This step was not necessary for Listing C since there was only one series. Third, Listing E invokes the SetData method twice to assign values to each of its two series. Your code needs to invoke this method once for each series in a chart. Aside from these three slight modifications, the code for a stacked pie chart is the same as for a standard pie chart.
Basing a chart on a spreadsheet
The samples I’ve presented so far illustrate how to easily publish data from a source behind the chart. Notice that the code in Listing A included four statements that invoked the SetData method. These statements referenced data literally typed into the code that generated the Web page. When you need to easily publish small amounts of data, this is a viable solution technique.
What if you needed to let users dynamically set the values that a chart plots? Presenting the data in a spreadsheet and binding a chart to a spreadsheet offers a viable solution for this requirement. Many users feel comfortable working with a spreadsheet. Therefore, it makes a good interface for users entering data. In addition, you can readily bind a chart control to a spreadsheet control. When you bind the chart control to the spreadsheet control, the chart can update automatically whenever a user modifies the contents of the spreadsheet.
|A clustered column chart bound to a spreadsheet|
Figure F shows a Web page with a chart that binds to a spreadsheet for its contents. This sample is easy to follow because it works with the same extended prices for product categories from the prior charts in this Daily Drill Down. What makes the Web page in Figure F special is that a user with Office XP on his or her computer can change the spreadsheet values and watch the chart dynamically update. Refreshing the page from the server restores the original data. This means a user can run what-if scenarios in the spreadsheet and see the results in a chart.
To create a Web page like the one in Figure F, start by adding a chart component to a Web page in FrontPage. Don’t specify a data source for a chart. Next, add a spreadsheet component to the same Web page right below the chart. Then, add data and format the spreadsheet as indicated in Figure F.
Next, you can add a window_onload event procedure to create a chart and populate it with data based on the values in selected spreadsheet ranges. Listing F shows the event procedure. After creating a reference to the Chartspace constants and clearing any previous charts from the control, the procedure binds the chart control to the spreadsheet. Then, it adds a chart to the Web page based on the control. Since the chart control is bound to the spreadsheet, the chart added to the page is automatically bound to the spreadsheet as well. However, we still have to specify which spreadsheet ranges provide values for the series names, categories, and series of our column chart. That's the job of the rest of the procedure.
The event procedure starts to populate the chart with values by assigning the B3 through C3 range for the series names. Notice from Figure F that these cells contain 1997 and 1998. Next, the code sample assigns the range of cells from A4 through A11 to the chart's category values. The statements that make these assignments invoke the SetData method with the chDataBound constant. All prior code samples used chDataLiteral instead of chDataBound. The chDataBound argument, the specifically designated cell range in the SetData statement, and the DataSource property assignment tell the chart from where to retrieve the data for series names and category values. The sample uses a For Each…Next loop to iterate through the two series. The SetData statement that designates two values for the series names provides the information that there are just two series to populate. The SetData statement inside the loop extracts values from rows 4 through 11 in the spreadsheet. For the first series, it uses column B as a source. Then, it moves to column C as a source for the second series. The Columns property for a spreadsheet range object returns the values that the SetData statement assigns to the chart's SeriesCollection objects. Since SeriesCollection objects are zero-based but spreadsheet Columns are one-based, the code sample adds one to the SeriesCollection object index.
Basing a chart on an Access database
|A datasheet based on a query for the Access Northwind database that totals extended price by product category|
It’s common to have requirements to plot data derived from a database. For example, Figure G shows results derived from a custom query of the Access Northwind database that aggregates extended price across order detail items to compute total sales by product category. The query computes extended price as the product of quantity, unit price, and 1-Discount for each order detail line item. The datasheet in Figure G served as the original sample data for all samples throughout this article.
This query in Figure G is the result of joining two other queries. The first computes extended price by product category for 1997. The second query performs the same computation for 1998. For your convenience in duplicating the results, I present the three Jet SQL statements for each query. The query to generate results for 1997 has the name 1997CategoryForExtendedPrice. This is its SQL syntax.
The second query to aggregate extended prices by product category for 1998 has the name 1998CategoryForExtendedPrice. Here is its SQL syntax.
The third query joins the two preceding queries. Its result set appears in Figure G. This is its SQL syntax.
To enrich your understanding of the chart control, this final sample creates a scatter chart that plots 1998 extended price aggregates by their corresponding results for 1997. Figure H shows the scatter chart. Notice from the chart's legend that different symbols, such as a diamond, square, or triangle, represent different product categories. The 1997 aggregates plot against the horizontal, or x, axis, while 1998 aggregates plot against the vertical, or y, axis.
|A scatter chart showing 1998 extended prices vs. 1997 extended prices by product category|
When the Chartspace control makes a scatter chart based on a recordset, the control automatically adds drop areas for a PivotChart, which are like drop areas for a PivotTable List control. Since users are not to modify our chart, the code suppresses the drop areas with some XML code in a PARAM tag for the ChartSpace control (see the top of Listing G). You may consider this syntax easy enough to code by hand, but you don’t have to do it that way. Simply right-click the Chartspace control on the Normal tab in FrontPage and choose ActiveX Control Properties. Then, select the Show/Hide tab in the dialog box that opens. Clear the Field buttons/drop zones check box and click OK. This adds more code that you need just to eliminate the drop zones from the chart generated by the Chartspace control. I removed the extraneous code to make it easier for you to follow what the XML code does. As you can see, the XML specifies the hiding of the pivot fields, which is another way to specify drop zones.
The window_onload event procedure for the chart in Figure H starts by creating a connection to the Northwind database file for Access 2002. If you moved your sample database file or didn’t install it, you’ll want to update the sample code so that it points at a suitable data source. In addition, you need to add the three queries based on the preceding Jet SQL statements. You can add a new query based on a SQL statement by pasting the SQL statement in to the SQL view of a blank query. After making a connection to the database, the event procedure opens a recordset based on the 19971998CategoryForExtendedPrice query, whose result set appears in Figure G. The next line of code sets the Chartspace control's DataSource property to the recordset. This binds the control to the query in the Northwind database. The statement to perform the binding also adds a chart to the Chartspace control's workspace. The procedure completes the initial specification of the chart by assigning chChartTypeScatterMarkers to its Type property.
After creating a chart that binds to the recordset, the procedure turns to populating the chart with values from the recordset. Three statements invoke the SetData method to accomplish this. The first two instances of the method assign the 1997 and 1998 datasheet column values from Figure G to the x-axis and y-axis values. As the query values change, so will charts that open on them. The third instance of the SetData method assigns the CategoryName column from the query to the series names for the chart.
The balance of the event procedure focuses on formatting values along the x and y axes. This task involves designating an appropriate caption for each axis and specifying a number format for the axis values. Notice that the numbers in Figure G appear without a currency format, but the axis values have a currency format. This is because the NumberFormat property assignments account for the currency format of the axes in the chart.
The Chartspace control is a rich one in terms of the variety of ways that it can display data. This Daily Drill Down introduced you to the basics of creating charts programmatically with the Chartspace control. Mastering these techniques will make it easy for you to create charts for your users without the drudgery of plowing through endless dialog tabs to fine-tune the data and format for a chart. In addition, once you have the code for a chart developed, you can easily modify the chart and know that you’ll retain any settings that you don’t explicitly change. Finally, another reason for learning this material is that it illustrates a general paradigm for manipulating Office XP Web Components programmatically because you can use VBScript to program the object model for each of the Web Components.