FusionCharts

Till now, we've all been dealing with single series charts and dynamic creation of those charts. Here, we'll see the dynamic implementation of a multi-series chart.

Considerably, creating the XML data document for a multi-series chart requires more effort than that of a single series chart. Also, it's always recommended to use the dataURL method when working with multi-series charts, as the XML data document tends to get larger in size and as such, older versions of browsers are not very able to handle long data chunks in the same page.

In our multi-series example chart (3D Multi-series column chart), we'll creating a 3 year comparison chart for the following five countries: Austria, Brazil, France, Germany, USA. We'll sum the invoice prices for each year for each of these countries and then show in the chart. The end chart would look as under:

As you can see above, we've extracted the sum of Invoice prices for each of the 5 countries for all the 3 years. All this data is contained in a view called Invoices. The entire XML code required for the above chart is as under:

In the above XML, we first define the basic attributes (both functional and visual) of the <graph> element. Thereafter, we render the <categories> elements. Each <category> element represents a category of data (in our example it's the five countries whose data we're comparing). Post that, we render a <dataset> element each for the three years (1996, 1997, 1998). Let's now quickly code this chart in ASP using dataURL method.

As we had earlier seen, the dataURL method requires two pages:

Chart Container Page - This page contains the chart object alongwith any other HTML objects. We'll name this page as Chart.asp.
Data Provider Page - This page provides the XML data required by the chart. This page is invoked by FusionCharts contained in Chart.asp. We'll name this page as Data.asp.

The above two are present in the downloads under the folder FusionCharts2_3 > Sample Code > DBExamples > ASP > MultiSeries.

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 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 Data.asp is responsible for relaying virtual XML documents to the FusionCharts embedded in Chart.asp. You should note that Data.asp does NOT write XML data documents to the server disk physically. It relays the XML data document virtually (i.e., no XML files are created and saved on the server). The code for this page can be reproduced as under:

<%@ Language=VBScript %>


<%
   'Define database objects
   'oRs - Recordset object
   'oRsYears - To store the list of years
   'strSQL - String variable to contain the SQL query
   'intCounter - Numeric value to keep a track of number of records
   'intYear - Numeric value to keep index of year
   Dim oRs, oRsYears, strSQL, intCounter
   intCounter = 0

   'Initialize the database objects
   Set oRs = Server.CreateObject("ADODB.Recordset")
   Set oRsYears = Server.CreateObject("ADODB.Recordset")

   'strXMLData - String variable to contain the entire XML data document for the chart
   'strCategories - String variable to store the <categories> element and it's child <category> elements.
   'strDataSet - String variable to store the <dataset> and <data>
   Dim strXMLData, strCategories, strDataSet
   'Initialize the <graph> element
   strXMLData = "<graph caption='Country Comparison' shownames='1' showvalues='0' decimalPrecision='0' numberPrefix='$'>" & vbCrlf
   'Generate the category elements - category elements are the x-axis labels (5 countries in our example)
   strCategories = "<categories><category name='Austria' /><category name='Brazil' /><category name='France' /><category name='Germany' /><category name='USA' /></categories>"
   strDataSet = ""
   'Get a list of all the years
   strSQL = "SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1"
   oRsYears.Open strSQL, DB_CONNECTION
   While not oRsYears.EOF
      'Increment the counter
      intCounter = intCounter + 1
      'Get the year
      intYear = oRsYears("Year")
      'Generate the <dataset> string for this year.
      strDataSet = strDataSet & "<dataset seriesName='" & intYear & "' color='" & arr_FCColors(intCounter) & "' showValues='0'>" & vbCrlf
      'Generate the SQL Query to retrieve data for this year.
      strSQL = "SELECT Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE Country In ('USA','Germany','Austria','Brazil','France') and Year(OrderDate)=" & intYear & " GROUP BY Country ORDER BY Country"
      'Retrieve the recordset
      oRs.Open strSQL, DB_CONNECTION
      'Now generate the <set> elements for this dataset in the format <set value='xxxx' />
      While not oRs.EOF
         strDataSet = strDataSet & "<set value='" & oRs("Total") & "' />" & vbCrLf
         oRs.MoveNext()
      Wend
      'Close this oRs
      oRs.Close()
      'Close the dataset
      strDataSet = strDataSet & "</dataset>" & vbCrLf
      oRsYears.MoveNext()
   Wend

   'Generate the XML document by concatenating the strings we've generated. Also, close the <graph> element.
   strXMLData = strXMLData & strCategories & strDataSet & "</graph>"
   'Destroy the recordset objects
   Set oRsYears = nothing
   Set oRs = nothing
   'Write the XML data to output stream
   Response.Write(strXMLData)
%>

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

We first initialize two recordset objects namely oRs and oRsYears. oRs would be to retrieve the invoice prices data from the database and oRsYears would be used to retrieve the list of unique years present in the database.
Next, we initialize a few string variables which would be used to contain the XML data. strXMLData will be used to contain the entire XML data document for the chart. strCategories will be used to store the <categories> element and it's child <category> elements. strDataSet will be used to store the <dataset> and <set> elements.
We initialize the <graph> and the <categories> elements. Since we already know that we're comparing data for 5 years, we've hard-coded our category names (and consecutive XML elements) in this example. In dynamic examples, you can use this variable to concatenate and store x-axis names retrieved from the database (we've done this in our next example - Combination Chart Example).
We now retrieve the list of unique years from the database and interate through each of them
For each year, we first generate the <dataset> element and store it in strDataSet variable. The seriesname attribute of each <dataset> is set to the year for which we'll be retrieving data.
We now retrieve the data for each of the countries, for this year, and generate the <set> element (in the format <set value='xxxx' />) for each one of them.
Finally, we close all the open elements in the XML data document (like <dataset> and <graph>) using simple string concatenation.
In the end, we just write this XML data document to the output stream.

When you try viewing this page individually in the browser you'll get the XML output which we had earlier seen. You should always make sure, that while using dataURL method, your dataURL script should output just XML data and no HTML tags.

Let's now shift our attention to Chart.asp and see the code it contains to embed the chart.

Chart.asp contains the following code:

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

<table width="500" border="0" cellpadding="2" cellspacing="0" class="tableWithBorder" align='center'>
   <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="&dataURL=Data.asp">
                  <PARAM NAME=movie VALUE="../../../Charts/FC_2_3_MSColumn3D.swf?chartWidth=450&chartHeight=300">
                  <PARAM NAME=quality VALUE=high>
                  <PARAM NAME=bgcolor VALUE=#FFFFFF>
                  <EMBED src="../../../Charts/FC_2_3_MSColumn3D.swf?chartWidth=450&chartHeight=300" FlashVars="&dataURL=Data.asp" 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>

Here, in the above code, we're simply embedding the 3D Multi-series Column Chart and specifying its dataURL as Data.asp. When you now view Chart.asp, you'll get the following chart: