Professional ASP Programming Guide for Office Web Component: with Office 2000 and Office XP
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Qimao Zhang
The Advisory Board Company
March 2003
Applies to:
Microsoft® Office XP Web Components
Summary: This article presents an excerpt from the book Professional ASP Programming Guide for Office Web Component: with Office 2000 and Office XP by Qimao Zhang. (This chapter is 16 printed pages)
Contents
Introduction
Split Axis
Add Data Labels
Create Chart Methods
Cleanup Methods
Create Chart using XML Data
Summary
In this chapter we will take a look at some advanced charting techniques that will be useful in addition to the basic charting techniques covered by the previous chapter. We will introduce how to code split axis, add data labels, and other charting functions. We will also compare the differences of two chart creation methods: creating a chart as an object or as a bitmap image. In the very last section, we will explore charting with XML data.
In order to display small and large numbers on the same axis and still show the local variation, you need to split a value axis at a certain point. You can have one split per value axis. To split the axis, set the HasSplit property of the axis’ Scaling object to True and then set the SplitMinimum and SplitMaximum properties to the values you want to split. The SplitMinimum property sets the upper limit of lower range values and SplitMaximum property sets the lower limit of the upper range values
For example, if we have three major air pollutants we are concerned with: nitrogen dioxide, sulfur dioxide, and carbon monoxide.
Figure 4.1. Split Y-axis
The following code sample shows how to split axis:
<%
oChart.Charts(0).SeriesCollection(0).Caption = "Nitrogen Dioxide"
oChart.Charts(0).SeriesCollection(1).Caption = "Sulfur Dioxide"
oChart.Charts(0).SeriesCollection(2).Caption = "Carbon Monoxide"
'Format title
oChart.Charts(0).HasTitle = True
oChart.Charts(0).Title.Caption = "The Level of Air Pollutants"
set fnt = oChart.Charts(0).Title.Font
fnt.Name = "arial"
fnt.Size = 14
fnt.Bold = True
'Format legend font and position
oChart.Charts(0).PlotArea.Interior.Color = "#CCCC99"
oChart.Charts(0).HasLegend = True
oChart.Charts(0).Legend.Position = c.chLegendPositionBottom
oChart.Charts(0).Legend.Font = "Tahoma"
oChart.Charts(0).Legend.Font.size = 7
oChart.Charts(0).Legend.LegendEntries(0).Visible = True
Dim ax
Set ax = oChart.Charts(0).Axes(0)
ax.Scaling.HasSplit = True
ax.Scaling.SplitMaximum = 300
ax.Scaling.SplitMinimum = 200
%>
The purpose of any chart is to provide useful data to the user. Data labels are great tools to ensure user understanding of the values on the chart. In simple chart types such as the pie chart, data labels are almost always necessary to ensure the chart is informative. The following chart is unintelligible —the user has no idea of how much air pollutant existed per hour nor does it have a correct and descriptive legend. This type of chart is simply not useful to users. Worst of all, the chart is the wrong chart type for the type of data being presented.
Figure 4.2. Chart without data labels
To improve the air pollutant chart, we need to add data labels for each series, change the chart type, and add a legend. An example of the improved chart looks like the following:
Figure 4.3. Chart with data labels
To add data labels to each series, simple follow this syntax:
<%
'Add data label for the first series
oChart.Charts(0).SeriesCollection(0).DataLabelsCollection.Add
'Add data label for the second series
oChart.Charts(0).SeriesCollection(1).DataLabelsCollection.Add
'Add data label for the third series
oChart.Charts(0).SeriesCollection(2).DataLabelsCollection.Add
%>
There are two ways to create a chart on a Web page by using the OWC Chart Component: create the chart as an object or as a bitmap image.
To create chart as an object, we must include the object id, class id, and style parameters. You can specify the width and height of the chart object in the style section and then put the chart object into the OnLoad
function. This will automatically load the object when the page is retrieved by Web page users. For example:
<html>
<body>
<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:350"></object>
<script language=vbscript>
Sub Window_OnLoad()
Dim oChart
Dim oSeries1, oSeries2
dim oConst
'Ensure ChartSpace1 is empty:
ChartSpace1.Clear
Set oConst = ChartSpace1.Constants
'Create a new chart in the ChartSpace
Set oChart = ChartSpace1.Charts.Add
'Add a series of type Column
Set oSeries1 = oChart.SeriesCollection.Add
With oSeries1
.Caption = "Sales"
.SetData oConst.chDimCategories, oConst.chDataLiteral, _
Array("1994", "1995", "1996", "1997")
.SetData oConst.chDimValues, oConst.chDataLiteral, _
Array(50, 60, 55, 59)
.Type = oConst.chChartTypeColumnClustered
End With
'Add a second series of type Line
Set oSeries2 = oChart.SeriesCollection.Add
With oSeries2
.Caption = "Profit"
.SetData oConst.chDimCategories, oConst.chDataLiteral, _
Array("1994", "1995", "1996", "1997")
.SetData oConst.chDimValues, oConst.chDataLiteral, _
Array(39, 47, 52, 46)
.Type = oConst.chChartTypeLine
End With
'Add a second value axis to the Chart
oChart.Axes.Add oChart.Scalings(oConst.chDimValues), _
oConst.chAxisPositionRight, oConst.chValueAxis
'Display the legend
oChart.HasLegend = True
oChart.Legend.Position = oConst.chLegendPositionBottom
'Display the title for the chart
oChart.HasTitle = True
oChart.Title.Caption = "Four Year Overview"
End Sub
</script>
</body>
</html>
This code will produce the following results on the Web page:
Figure 4.4. Line Chart on a Web page
The charts we created for Figures 4.1 and 4.3 were bitmap images. Both of these charts were created without the use of the object id, class id, and style parameters.
When you see the chart on Web page created as an object or created as a bitmap image, the chart looks identical. However, when you click on the image of a chart created as an object, using the right mouse button (right-click) the popup menu displays only one item, “About…” as shown on the figure above. You cannot save this chart to your local drive directly.
If you right-click on a chart created as a bitmap image, the pop up menu displays several items as shown in the following figure. One of the items is “Save As …” – to save the image directly to your local drive directly.
Depending on the purpose of your Web site and the chart you created, this may be a significant difference for the user. For example, the chart that created as an object cannot be saved, thus preventing the user from saving the chart to embed into a Word document.
Figure 4.5 Image context menu
In the previous chapter, we always created the chart image by using a temporary name. Therefore, each time you create and reload the same Web page, the newly generated image replaces the previous one. This is fine in many cases but not all cases. Sometimes you may need to create images and keep them on the server with a unique name for each image. In order to do this, you can use the GetTempName method of the FileSystemObject:
<%
Dim temFileName, objFSO
Set objFSO = Server.CreateObject(“Scripting.FileSystemObject”)
tmpFileName = strTmpDir & objFSO.GetBaseName(objFSO.GetTempName) & “.gif”
%>
If your Web pages have created too many images on the server, you may need to implement some sort of periodic cleanup schema to clean up the files. The following example code section deletes any image, with a DateLastModified
value greater than 10 minutes, from the server. Of course, you can set the number of minutes value to any value you determine is valid for periodic cleaning.
<%
Sub CleanUpGIF(GIFpath)
Dim objFS
Dim objFolder
Dim gif
set objFS = Server.CreateObject("Scripting.FileSystemObject")
set objFolder = objFS.GetFolder(GIFpath)
'Loop through each file in the GIFpath folder
for each gif in objFolder.Files
'Delete GIF files older than 10 minutes
if instr(gif.Name, ".gif") > 0 and _
DateDiff("n", gif.DateLastModified, now) > 10 then
objFS.DeleteFile GIFpath & "\" & gif.Name, True
end if
next
set objFolder = nothing
set objFS = nothing
End Sub
Call CleanUpGIF("e:\Inetpub\wwwroot\hworkspc")
%>
This section describes how you can use the Microsoft Office Chart Component to plot data from an XML data stream. The chart used in this sample is bound to a RecordsetDef in a Data Source Component. The RecordsetDef itself is created from Active Server Pages (ASP) script that uses the XML Rowset definition to return an XML data stream.
First, create a file called GenerateData.asp in your IIS home directory and add the following code to it:
<%@ Language=VBScript %>
<%
' GenerateData.ASP
' Purpose: Generates data for the chart
Option Explicit
Response.Buffer = True
' Write out the XML-Data header information
%>
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<%
' Write out the schema info
%>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:attribute type='XValues'/>
<s:attribute type='YValues'/>
<s:extends type='rs:rowbase'/>
</s:ElementType>
<s:AttributeType name='XValues' rs:number='1' rs:nullable="true">
<s:datatype dt:type='string' dt:maxLength='10'/>
</s:AttributeType>
<s:AttributeType name='YValues' rs:number='2'>
<s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
</s:Schema>
<%
' Now write out some random data
Dim nXVal 'Temp X value
Dim anYValues 'Array of values for Y Axis
Dim nct, nUpperbound, nLowerbound
nUpperbound = 100
nLowerbound = 25
Randomize
Response.Write "<rs:data>"
For nct = 0 To 100
' Start the row
Response.Write "<z:row XValues=' CPU"
' Generate and write the X value
Response.Write CInt(nct) & "'"
' Generate and wwrite the Y value
Response.Write " YValues='" & CInt((nUpperbound - nLowerbound + 1) * Rnd + nLowerbound) & "'"
' Close the row tag
Response.Write "/>"
Next 'ct
' Close the data section
Response.Write "</rs:data>" & vbcrlf
' Close the xml tag
Response.Write "</xml>"
%>
Then we can create another file named Chart.htm in the same directory as GenerateData.asp to display the chart from XML data. Add the following code to Chart.htm:
<HTML>
<HEAD>
<TITLE>Sample Chart</TITLE>
</HEAD>
<BODY>
<H1>Chart based on XML Data</H1>
<!-- OWC DSC Control -->
<object classid="clsid:0002E530-0000-0000-C000-000000000046" id="dscSample">
</object>
<!-- OWC Chart Control -->
<OBJECT classid=clsid:0002E500-0000-0000-C000-000000000046 height=384
id=csSample style="HEIGHT: 75%; WIDTH: 100%" width=576>
</OBJECT>
<SCRIPT language=vbscript>
Sub Window_onLoad()
' Initialize the DSC
DSCInit dscSample
' Draw the chart
DrawChart csSample, dscSample
End Sub
' Initializes the DSC by setting Connection String and RecordSetDef
Sub DSCInit(dsc)
' Add a RecordsetDef with name ChartData to the dsc
if len(dsc.ConnectionString) = 0 then
dsc.ConnectionString = "provider=mspersist"
dsc.RecordsetDefs.AddNew "GenerateData.ASP", _
dsc.Constants.dscCommandFile, "ChartData"
else
Window.status = "DSC ConnectionString is already set!"
end if
End Sub
' Draws the chart using the RecordSetDef data
Sub DrawChart(cspace, dsc)
Dim c 'Constants object
Dim cht 'Temp WCChart object
Dim ser 'Temp WCSeries object
Dim ax 'Temp WCAxis object
Set c = cspace.Constants
' Clear the Chartspace
cspace.Clear
' Load the chart data sources
Dim cds 'Temp WCChartDataSource object
' Add a DataSource to the Chart and set it to be the dsc
Set cds = cspace.ChartDataSources.Add()
Set cds.DataSource = dsc
' Set the Data Member to be the RecordsetDef
cds.DataMember = "ChartData"
cds.CacheSize = 400
' Draw the Chart
set cht = cspace.Charts.Add()
cht.Type = c.chChartTypeLineMarkers
cht.HasLegend = True
cht.Legend.Position = c.chLegendPositionTop
cht.HasTitle = True
cht.Title.Caption = "CPU Utilizations"
' Add a series
set ser = cht.SeriesCollection.Add()
ser.Name = "Utilization(%)"
ser.Caption = ser.Name
ser.Marker.Size = 4
' Set the Categories to the first field (YValues)in the
' RecordSetDef of the DataSource - dsc
ser.SetData c.chDimCategories, 0, 0
' Set the Values to the second field (XValues)in the
' RecordSetDef of the DataSource - dsc
ser.SetData c.chDimValues, 0, 1
' Set the tick label spacing depending on the number of points plotted
Set ax = cht.Axes(c.chAxisPositionBottom)
ax.TickLabelSpacing = cht.SeriesCollection(0).Points.Count / 10
End Sub
</SCRIPT>
</BODY>
</HTML>
This will create a chart on the Web page that displays the CPU Utilization Rate from XML data.
We have covered some advanced techniques for charting building on the basic techniques covered in the previous chapter. Some chart types can benefit from these techniques. For example, data labels make pie charts much more informative to users. Split axis can be a good enhancement when the data is concentrated in certain value areas. You should also consider whether to create a chart as an object depending on your requirements.
In addition we covered how to create a unique chart each time it is displayed, file clean up techniques and an example of using XML data to create charts.
Qimao Zhang is a software developer at The Advisory Board Company. Zhang has a MBA and M.S. in Computer Information Systems. His certifications include Microsoft Certified Solution Developer (MCSD), Microsoft Certified Database Administrator (MCDBA), and Network+ Certified Professional. He resides in Alexandria, VA with his wife Michelle.