Graphing (Charting) data pulled from a database is a fairly routinely requested job. You open a connection and pass the data to some object, which saves the graph as a gif and you then send that to the client. There have been a number of examples on how to do this. Chandra Raghawendra (http://www.asptoday.com/articles/20001121.htm?WROXEMPTOKEN=1118675ZMX6zmKiNy7ApE3lPAU) and Keith Stanislaw (http://www.asptoday.com/articles/19991117.htm?WROXEMPTOKEN=1118675ZMX6zmKiNy7ApE3lPAU) demonstrated a way to create graphs by loading an instance of Excel on the server, passing it variables and then having the Excel application save the graph as a gif. Alexendre Pinho (http://www.asptoday.com/articles/20001103.htm?WROXEMPTOKEN=1118675ZMX6zmKiNy7ApE3lPAU) showed how to use the Charting Component to do a similar feat, and Frank Kwong (http://www.asptoday.com/articles/19991029.htm?WROXEMPTOKEN=1118675ZMX6zmKiNy7ApE3lPAU) showed us how to use the proprietary COM object AspDB.Y2k to create charts as well. It's a popular subject.

As you can see there are a number of ways to make graphs on your server and send them to the client. Although these are an effective way to prepare chart graphics, in a solution that needs to provide graphs for hundreds of concurrent users, loading bulky COM objects for each graph request is a tremendous strain on the server and doesn't scale well. In this article I'll show you how to create graphs on the client's PC without over taxing the server. This solution is easily expandable to large numbers of clients.

Instead of creating full gif images of the graph, we'll use a gif image, smaller than 1k and stretch it to the length of the value taken from the database. This minimizes even further the load on the server. I had to simulate hundreds of hits to the server just to register activity on the MMC's Performance Monitor of our IIS server.

With the advent of the reality of XML and the fact that XML Parsers are on many PCs now, we can use a combination of these tools to create fast graphs on the client, with little load on the IIS server. The web server only has to retrieve the data and pass it on as XML. It is the client that does the more CPU-intensive work of rendering HTML from XML. I'll step you though a number of alternative ways to accomplish this as well as showing a client-server hybrid version that will work on all browsers.

You'll have to choose which of the methods I outline here. If you're writing Internet based applications then transform the data server side as not everyone has an XML parser and IE on their PC. For Intranets, where you can better control the clients browser, use the methods where the client does the transformations.

What you'll need:

• IIS4 or higher with the latest service packs. (IE 5.5 with SP1 is recommended)
• Microsoft's XML parser 3 on the client and server (You can download it for free from the Links section at the end of this article). Install the XML parser in replace mode, or use the XMLINT to remove older XML parsers.
• IE 5.x or higher on the client. Version 5.5 is recommended.
• Recommended: Add a reference to the Active X Data Objects 2.1 COM object or higher to your Interdev project you import this into. This will allow you to use the Enumerated types adPersistXML. You can do this by selecting this COM object from the PROJECT | REFERENCES menu. It will add the following text to your Global.asa :

  <!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.1 Library" & _
   UUID="{00000201-0000-0010-8000-00AA006D2EA4}" VERSION="2.1"-->
  

What you'll know:

• How to conserve server resources by off loading CPU work onto the client.
• How to convert data queried from a database into XML.
• How to apply the XSL to XML on the client and/or server in order to generate HTML.
• How to use the XML Data Source Object to retrieve and manipulate XML on the client side and render it into a graph.
• How to save server bandwidth by sending only small graphics that are turned into big graphs by the client.

What you'll get:

The article comes with a zip file containing the following files. Simply unzip them into a folder on your IIS server and browse to the default.asp page. It links to all the other pages. Each page is simply written and can be easily copied and modified into your own application.

The basic structure of the article:

1. Get XML from a database on the server.
2. Pass the XML to the three different methods of converting XML into HTML graphs:
3. Client site Processing Instructions
4. Server Side Processing Instructions
5. XML Data islands

Error checking note:

In order to keep the article as simple as possible, there is little error checking. In your production scripts you should include a robust error handling facility.

Getting the XML from a database on the server.

For the purposes of this article we'll be using a simple Access database (which is provided in the example source). The data looks like this:

We'll be using an ASP page that writes our data information as XML to the server's RESPONSE stream with ADO.

Code Description

Response.ContentType = "text/xml"
Response.Write "<?xml version='1.0' ?>"

This tells the browser/server that the data coming is in XML format.

if Request.QueryString("XSL") <> "" then
   Response.Write "<?xml-stylesheet type='text/xsl' href='" & Request.QueryString("XSL") & "'?>"
end if

If the ASP page is called with the QueryString variable XSL set, the XML will have a Processing Instruction (PI) included. In this example, it's a XSL style sheet. The Browser will take the XML it receives, then load the XSL document and transform them into HTML without any scripting on the client side. We'll get into this more shortly. Think of XSL as a mail merge tool which merges the XML and the HTML into data rich HTML.

dim RS, CN
set CN = server.CreateObject("adodb.connection")
set RS = server.CreateObject("adodb.RecordSet")
       
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath("staff.mdb")
RS.Open "select first, last, total, phone, email from sales order by last",cn

We connect to the database and get all the data into a recordset. You could enhance this part by having different queries run based on QueryString parameters sent to the ASP page. You can further reduce the load on the server by hard coding the staff.mdb path. The MapPath method of the Server object is expensive. I have used it here for simplicity and so that the web application will work without any intervention when you unzip the source files into your IIS web server. If you're cautious about hard coding paths, as you should be, you can use the ServerVariable Request.ServerVariabes("APPL_PHYSICAL_PATH") to get the base path of the web application and then add the relative path of the MDB to the end of that. It's not as expensive as the MapPath. Here's an example : Dbpath = Request.ServerVariables("APPL_PHYSICAL_PATH") & "/myfolder/" & "sales.mdb"

RS.Save response,adPersistXML

This is the most important and interesting part of the script. The recordset object can save its data as XML into any Stream object. The Response object in ASP exposes the IStream interface and so can accept the recordsets output. With this all the data in the recordset it is sent to the browser in nicely formatted XML.

Now that we have the XML data source, all we have to do is render it into XML using a number of methods.

Method one: Using an XML data island

Pros : The source code is easy to read for a designer without any XML/XSL knowledge.

Cons : Not as powerful or flexible as using the various XSL methods.

The output will look like this when done:

Data Islands, denoted by the tag <XML></XML>, can either contain in their innerHTML the XML code or have it reside remotely in another file by specifying the SRC property. In our example we'll use the latter method and specify the ASP ( XMLSource.asp ) we just wrote as the XML provider.

The script for this is as follows:

Example filename: ClientDataIsland.asp

<%@ Language=VBScript %>
<% option explicit %>

<XML id=xmlDSO name=xmlDSO src="XMLSource.asp"></XML>

<SCRIPT ID=clientEventHandlersVBS LANGUAGE=vbscript>
<!--

Sub window_onload
   set RS = CreateObject("ADODB.RecordSet")
   set Stream = createobject("ADODB.stream")
   
   '-----------------------------------------------------
   ' Get the XML from the ASP page 
   ' which supplies the XML Data Source Object with XML
   '-----------------------------------------------------
   stream.Open
   stream.WriteText xmlDSO.xml, adWritechar
   stream.Position = 0
     
   rs.Open Stream,,,,adCmdFile
   
   '----------------------------
   ' Write out the graph 
   '----------------------------
    document.write "<LINK rel=stylesheet type=text/css href=standard.css>"
    document.write "<table class=tblresults border=0>"
    document.write "<caption class=title><nobr>Graph made with XML Data Island</nobr></caption>"
    
    do until rs.EOF
       document.write "<TR><TD>" & rs("first") & " " & rs("last") & "</TD>"
       '--------------------------------------------------------------
       ' Make the graph column as wide as the value of the data
       ' Note: If your data is very large, divide the rs("total") by
       '  a large enough number so that it will fit on the screen. This
       '  will ensure the application will scale well.
       '-------------------------------------------------------------- 
   
       document.write "<TD><img src=graph.gif height=5 width=" & rs("total") & "></TD>"
       document.write "<TD>" & rs("total") & "</td></tr>"
       rs.MoveNext
    loop
    document.write "</table>"
   
End Sub

-->
</SCRIPT>

Code description:

<XML id=xmlDSO name=xmlDSO src="XMLSource.asp"></XML>

When the page loads, and before the OnLoad event of the Window DHTML object fires, the XML data island fetches the XML from the ASP page XMLSource.asp.

stream.Open
stream.WriteText xmlDSO.xml, adWritechar
stream.Position = 0
     
rs.Open Stream,,,,adCmdFile

This code will execute this logical process:

• XMLSource.asp
• Sends XML to xmlDSO
• Sends the data to the Stream
• Which is read into a recordset.

The XML Data Island exposes the XML as plain text which we'll use client side to write the formatted page to the browser. The above code uses a Stream object to get all the XML into itself. Then a disconnected recordset is created and is filled with the Stream's XML. It's a two-step process, but the recordset can only read from a Stream.

document.write "<LINK rel=stylesheet type=text/css href=standard.css>"
document.write "<table class=tblresults border=0>"
document.write "<caption class=title><nobr>Graph made with XML Data Island</nobr></caption>"
    
do until rs.EOF
   document.write "<TR><TD>" & rs("first") & " " & rs("last") & "</TD>"
   '--------------------------------------------------------------
   ' Make the graph column as wide as the value of the data
   ' Note: If your data is very large, divide the rs("total") by
   '  a large enough number so that it will fit on the screen. This
   '  will ensure the application will scale well.
   '--------------------------------------------------------------

   document.write "<TD><img src=graph.gif height=5 width=" & rs("total") & "></TD>"
   document.write "<TD>" & rs("total") & "</td></tr>"
   rs.MoveNext
loop

document.write "</table>"

Now that we have the recordset, we can simply write it to the browser by looping through it and writing the page.

Evaluation of this method:

This is a simple method to write a quick graph to the browser, especially for someone who knows little about XSL or XML. In fact the author would need to know nothing in order to use this method. However it's not as robust or powerful as XSL.

Method Two: using XML and XSL client side to render XML into HTML.

Pros : Near zero HTML rendering load on the server, only one quick hit to a database needed.

Cons : Requires the client to have IE 5.x and the version 3 XML parser from Microsoft.

This second method of creating graphs client side is by far the more robust, scaleable, feature laden and comprehensive method. Essentially, the browser is sent the XML and a Processing Instruction tells the browser to convert the XML into HTML using a XSL Style sheet. Your server can handle many more chart requests because it doesn't have to do the transformation of data into HTML.

To do this we simply call the XML provider we created ( XMLSource.asp ) and pass it the value of the QueryString variable XSL, which specifies the XSL file to use. When the browser receives this information, it will render the XML into HTML.

Here's what the final HTML will look like. Note we've added links to the email items of each record to demonstrate a few added features in XSL.

To get this page, we simply point the browser to our XMLSource.asp page and pass it the QueryString variable XSL, which contains the path to the XSL style sheet. The URL would look like this: XMLSource.asp?XSL=standard.xsl

Here's the code that makes this work. Referring back to the XMLSource.asp page,

if Request.QueryString("XSL") <> "" then
    Response.Write "<?xml-stylesheet type='text/xsl' href='" & Request.QueryString("XSL") & "'?>"
end if

This code tells the browser to use the specified style sheet. Since we'll be using the standard XML format, which the ADO Recordset saves, let's take a few moments to look at its structure:

You'll notice that each row is saved in a z:row tag and the data is in elements of the tag. In order to convert this XML we use the following Style sheet.

Example filename: standard.xsl

<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<xsl:template match="/">
<HTML>
<HEAD>
<LINK rel="stylesheet" type="text/css" href="standard.css"/>
</HEAD>
<BODY>      
        
<TABLE class="tblResults">
  <CAPTION class="title">Customers</CAPTION>
  <xsl:for-each select="xml/rs:data/z:row">
    <TR>
      <TD class="Data">
           <xsl:value-of select="@first"/>
           <xsl:value-of select="@last"/>
      </TD>
      <TD class="Data">
           <img src="graph.gif" height="5">
              <xsl:attribute name="width"><xsl:value-of select="@total"/></xsl:attribute>
           </img>
      </TD>      
      <TD class="Data">
           <xsl:value-of select="@total"/>
      </TD>       
      
      <TD class="Data">
         <A>
            <xsl:attribute name="href">mailto:<xsl:value-of select="@email"/></xsl:attribute>
            <xsl:value-of select="@email"/>
         </A>   
      </TD>
      
      <TD class="Data"><xsl:value-of select="PHONE"/></TD>
    </TR>
   </xsl:for-each>  
</TABLE>   

</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>

Code description:

<xsl:for-each select="xml/rs:data/z:row">

We loop inside our table through all of the XML elements in the namespace rs:data.

<xsl:value-of select="@first"/>
<xsl:value-of select="@last"/>

Each row in the HTML table is filled with elements by selecting them from the current XML node as we loop through it. Notice the @ symbol in front of each. This denotes that the data is not a tag by itself, but an element of a tag. In this case, the tag is the z:row.

<A>
  <xsl:attribute name="href">mailto:<xsl:value-of select="@email"/></xsl:attribute>
  <xsl:value-of select="@email"/>
</A>   

In order to add an HREF element to the Anchor tag <A>, we use the xsl:attribute command. This adds the value of email to the HREF and to the value to be shown in the link.

That's all there really is to displaying XML data as properly formatted HTML in IE 5.x and above using XSL. Of course there are many books written about XSL and this is not meant to be a comprehensive overview of XSL. With what you've learned so far you can display graphed, or other data, quickly and easily. I recommend the Wrox book, "Professional ASP XML" for a more comprehensive overview. It can be ordered from the Wrox web site http://www.wrox.com/?WROXEMPTOKEN=1118675ZMX6zmKiNy7ApE3lPAU. Its ISBN is 1861004028. Don't be scared by the title, it's well written with great examples.

So far it's a fairly easy method, but what if you need to make graphs for the client, but are unsure about what browser they have? The solution is to convert the XML to HTML on the server, and then send that to the client. Although it's a little more work for the server, it's a way to guarantee that the browser that views your site can see the data. Method Two is good obviously for an environment where you know that everyone one is using IE 5 and have the XML parser on their workstation.

Suggestion : If you are trying to squeeze the most possible amount of life from the server, check to see what browser is requesting the page. If it's 5.5 you can be fairly certain that the user has the version 3 XML parser on their client PC and you can send them method two. Otherwise, send them to method three, as outlined below.

Method Three: Converting the XML into HTML server side.

Pros : Works on all clients because the HTML is rendered on the server.

Cons : The server has to do extra work to create the page.

In this final example of how to create lightweight graphs, we use the server to render the XML into HTML with a XSL style sheet.

Basically, the script opens the database up, gets the XML, saves it to a Stream object then uses the XML TransformNode method to render the XML into HTML with an XSL style sheet before sending that to the browser.

Here's the full script.

Example Filename: SERVERXML.asp

<%@ Language=VBScript %>

<%
        
    dim RS, CN, Stream
    dim XML, XSL

    set CN = server.CreateObject("adodb.connection")
    set RS = server.CreateObject("adodb.RecordSet")
    set Stream = server.CreateObject("adodb.stream")
    set XML = server.CreateObject("MSXML2.DOMDocument")
    set XSL = server.CreateObject("MSXML2.DOMDocument")
       
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath("staff.mdb")
    RS.Open "select first, last, total, phone, email from sales order by last",cn
    RS.Save stream,adPersistXML       
    Stream.Position = 0
    
    XML.loadXML Stream.ReadText    
    XSL.load  server.MapPath("standard.xsl")  
    response.write XML.transformNode(XSL.documentElement)

    CN.close
    set rs = nothing
    set cn = nothing
    set XML = nothing
    set XSL = nothing
    set Stream = nothing
%>

Code Explanation:

set XML = server.CreateObject("MSXML2.DOMDocument")
set XSL = server.CreateObject("MSXML2.DOMDocument")

We need to create 2 XML Document objects to hold the XSL and XML data. Many examples will use the ProgID Microsoft.XMLDOM, however to use the version 3 XML parser, use this ProgID.

RS.Save stream,adPersistXML       

As explained in the previous method, the recordset saves the data in XML format to the Stream object.

XML.loadXML Stream.ReadText    

We then load the contents of the stream into the XML document

XSL.load  server.MapPath("standard.xsl")  

And then load the XSL document from the server into the XSL document. Remember that XSL is in XML format so we can load that into an XML document object.

response.write XML.transformNode(XSL.documentElement)

Here's the magic, the XML TransformNode call takes the XML inside the XML document and using the ?XSL stylesheet converts it to HTML. It then sends the result to the browser.

Summary:

As you can see, you have a number of simple ways to convert XML into an HTML graph. Each has its own benefits and costs. With XML there is no reason why your server has to get bogged down in heavy graphing operations if your solution requires quick, and resource inexpensive, graphs.

Links:

IE 5.5

http://www.microsoft.com/windows/ie?WROXEMPTOKEN=1118675ZMX6zmKiNy7ApE3lPAU

Version 3.0 Microsoft Parser

http://msdn.microsoft.com/xml?WROXEMPTOKEN=1118675ZMX6zmKiNy7ApE3lPAU

Including the Version 3.0 Microsoft parser in your applications

http://msdn.microsoft.com/xml/general/msxmlmergemodule.asp?WROXEMPTOKEN=1118675ZMX6zmKiNy7ApE3lPAU