FusionCharts

Previously, we had seen how to use the dataURL method to create a dynamic column chart using FusionCharts. Here, we'll be creating the same chart but using dataXML method.

As we had earlier seen, the dataXML method just requires one page:

Chart.asp: This page contains everything - the year select drop down list, the chart and the XML data.

The above page is present in the downloads under the folder FusionCharts2_3 > Sample Code > DBExamples > ASP > dataXML.

We'll also need two other pages:

Includes/Connection_inc.asp - This page contains the data connection string
Includes/FC_Colors.asp - This page contains a list of hex colors (without #) in an array named as arr_FCColors

The above two include files are present in a common Includes folder at FusionCharts2_3 > Sample Code > DBExamples > ASP > Includes.

FC_Colors.asp looks the same as before, as under:

<%
'This page contains an array of colors to be used as default set of colors for FusionCharts
'arr_FCColors is the array that would contain the hex code of colors
'ALL COLORS HEX CODES TO BE USED WITHOUT #
Dim arr_FCColors(20), intFCColors_count
'Count of the number of colors
intFCColors_count = 20
arr_FCColors(1) = "AFD8F8"
arr_FCColors(2) = "F6BD0F"
arr_FCColors(3) = "8BBA00"
arr_FCColors(4) = "A66EDD"
arr_FCColors(5) = "F984A1"
arr_FCColors(6) = "CCCC00" 'Chrome Yellow+Green
arr_FCColors(7) = "999999" 'Grey
arr_FCColors(8) = "0099CC" 'Blue Shade
arr_FCColors(9) = "FF0000" 'Bright Red
arr_FCColors(10) = "006F00" 'Dark Green
arr_FCColors(11) = "0099FF" 'Blue (Light)
arr_FCColors(12) = "FF66CC" 'Dark Pink
arr_FCColors(13) = "669966" 'Dirty green
arr_FCColors(14) = "7C7CB4" 'Violet shade of blue
arr_FCColors(15) = "FF9933" 'Orange
arr_FCColors(16) = "9900FF" 'Violet
arr_FCColors(17) = "99FFCC" 'Blue+Green Light
arr_FCColors(18) = "CCCCFF" 'Light violet
arr_FCColors(19) = "669900" 'Shade of green
arr_FCColors(20) = "1941A5" 'Dark Blue
%>

Basically, this file is a list of hex colors that we'll be using for the chart. In the above list, we've stored only 20 colors. You can extend it to any number. You should note that all the above colors are without the #. We'll include this file in all our pages, where we generate the XML data for FusionCharts.

Connection_inc.asp is the page which contains the database connection string. When you run the code on your machine, after downloading the package, you should ensure that this page is updated so as to reflect the connection details to your SQL server. This page looks as under:

<%
'*****************************************************************************************
' Page Description: This page contains the database connections
'*****************************************************************************************

'***** SQL SERVER FORMAT *****
'Update the string, replacing the words YourServerName, YourDatabaseName, YourUsername, and YourPassword
'with the appropriate values.
Const DB_CONNECTION = "Driver=SQL Server;Server=(local);Initial Catalog=Northwind;UID=sa;PWD="

Response.Write vbcrlf
%>

The page Chart.asp is responsible for everything - rendering the year down box, getting the selected year, creating the XML data for that year and then rendering the chart for that year. The code for this page can be reproduced as under:

<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE>FusionCharts</TITLE>
<LINK REL='Stylesheet' HREF='../Style.css'>
</HEAD>


<%
   'Request the current year from querystring
   Dim intYear, strDataURL
   intYear = Request("Year")
   'Database objects
   'oRsYears - Recordset Object
   'oRs - Recordset object
   'intCounter - Numeric value to keep a track of number of records
   'strSQL - String variable to hold SQL Query (to get all years list)
   Dim oRsYears, oRs, intCounter, strSQL
   'strXMLData - String variable to contain the entire XML data document for the chart
   Dim strXMLData
   strXMLData = ""
   'Initialize the recordset object and retrieve the years
   Set oRsYears = Server.CreateObject("ADODB.Recordset")
   strSQL = "SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1"
   oRsYears.Open strSQL, DB_CONNECTION
   'If no years has been specified select the last one
   If intYear="" then
      While not oRsYears.EOF
         intYear = oRsYears("Year")
         oRsYears.MoveNext()
      Wend
   end if
   'Move to first
   oRsYears.MoveFirst()
   'Initialize the recordset object
   Set oRs = Server.CreateObject("ADODB.Recordset")
   '--------------------XML Data for TOP 5 COUNTRIES-------------------------
   'Query the database
   strSQL = "SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=" & intYear & " GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC"
   oRs.Open strSQL, DB_CONNECTION
   'Create the <graph> element
   strXMLData = "<graph caption='Top 5 Countries for the year " & intYear & "' shownames='1' showvalues='0' decimalPrecision='0' numberPrefix='$'>" & vbCrlf
   'Now iterate through each data row
   intCounter=0
   While not oRs.EOF
      'Increase the count
      intCounter=intCounter+1
      'Append the value in format <set name='...' value='...' color='...' />
      strXMLData = strXMLData & "<set name='" & ors("Country") & "' value='" & ors("Total") & "' color='" &       arr_FCColors(intCounter mod intFCColors_count) & "'/>" & vbCrlf
      oRs.MoveNext()
   Wend
   'Entire XML - concatenation
   strXMLData = strXMLData & "</graph>"
%>
<table width="500" border="0" cellpadding="2" cellspacing="0" class="tableWithBorder" align='center'>
   <tr>
      <td colspan="3" class="trdark"><div align="center"><span class="textboldlight">FusionCharts</span></div></td>
   </tr>
   <form action="Chart.asp" method="post" id=form1 name=form1>
      <tr>
         <td colspan="3" class="text" align="center">Please select the year for which you want to see the chart:
         <SELECT name='year' class='select' onChange="this.form.submit();">
         <%
            While not oRsYears.EOF
         %>
               <option value="<%=oRsYears("Year")%>"
               <% If int(intYear)=int(oRsYears("Year")) then %>
                  selected
               <% end if %>
               ><%=oRsYears("Year")%>
               </option>
         <%
               oRsYears.MoveNext()
            Wend
         %>
         </SELECT>
         </td>
      </tr>
   </form>
   <tr>
      <td colspan="3"> </td>
   </tr>
   <tr>
      <td valign="top">
         <table width="98%" border="0" cellspacing="0" cellpadding="2" align='center'>
            <tr>
               <td><div align="center" class="text">
                  <OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/
flash/swflash.cab#version=6,0,0,0" WIDTH="450" HEIGHT="300" id="FusionCharts" ALIGN="">
                  <PARAM NAME="FlashVars" value="&dataXML=<%=strXMLData%>">
                  <PARAM NAME=movie VALUE="../../../Charts/FC_2_3_Column3D.swf?chartWidth=450&chartHeight=300">
                  <PARAM NAME=quality VALUE=high>
                  <PARAM NAME=bgcolor VALUE=#FFFFFF>
                  <EMBED src="../../../Charts/FC_2_3_Column3D.swf?chartWidth=450&chartHeight=300" FlashVars="&dataXML=<%=strXMLData%>" quality=high bgcolor=#FFFFFF WIDTH="450" HEIGHT="300" NAME="FusionCharts" ALIGN="" TYPE="application/x-shockwave-flash" PLUGINSPAGE="http://www.macromedia.com/go/getflashplayer"></EMBED>
                  </OBJECT>
               </div></td>
            </tr>
         </table>
      </td>
   </tr>
</table>
<p align="center" class="text">©All Rights Reserved - InfoSoft Global Private Limited - 2005
- <a href="http://www.InfoSoftGlobal.com" target="_blank">www.InfoSoftGlobal.com</a></p>
</BODY></html>
<%
   'Destroy objects
   Set oRs = nothing
   Set oRsYears = nothing
%>

In the above code, we're doing the following:

First, we're requesting a list of unique years from the database and rendering them in a drop down box. By default, we choose the last year as the selected year (i.e., the year for which we'll show the chart) and store it in the variable intYear. When the user selects a different year from the drop down box, the forms submits to this page only and the new requested year is stored in the variable intYear.
Next, we initialize a few string variables and a recordset object to help us retrieve data from database.
We also initialize a variable strXMLData which would store the entire XML data document. We initialize the <graph> element in this string variable.
Next, we retrieve the recordset from the database and iterate through the records one by one. For each record, we add a <set> element in the format <set name='DataName' value='DataValue' color='HexCode' />. We get individual color codes for each data item from the list of colors that we had stored in the array arr_FCColors (stored in FC_Colors.asp under Includes folder).
Thereafter, we add the ending </graph> element to the XML document (stored as string in strXMLData).
Lastly, we embed the FusionCharts Chart in the ASP page and convey the XML data using dataXML method.

When you now see Chart.asp in your browser, you'll get the following output: