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.

 

Chapter 4 Excerpt: Advanced Charting Techniques

Qimao Zhang
The Advisory Board Company

March 2003

Applies to:
     Microsoft® Office XP Web Components

Buy this book

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

Introduction

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.

Split Axis

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  
   %>

Add Data Labels

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
%>

Create Chart Methods

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.

Create a chart as an object

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

Create a Chart as Bitmap Image

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.

What are the differences between the two methods?

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

Cleanup Methods

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")
%>

Create Chart using XML Data

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.

Summary

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.

About the Author

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.