FusionCharts

As with all other languages, FusionCharts can be used with ColdFusion too using both dataURL and dataXML method. In this part, we stick to CF (ColdFusion ) as our choice of scripting language and revisit the process with CF code samples. We've an entire blueprint application using FusionCharts and ColdFusion - you can download the source from our website (http://www.InfoSoftGlobal.com/FusionCharts) to learn more.

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

Chart Container Page - This page contains the chart object alongwith any other HTML objects. We'll name this page as Chart.cfm - in this page, we let the user select the year for which he wants to view the chart and would show the chart.
Data Provider Page - This page provides the XML data required by the chart. This page is invoked by FusionCharts contained in Chart.cfm. We'll name this page as Data.cfm.

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

The application will use a ColdFusion array called Colors, defined in Application.cfm, that looks as follows:

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.

The application uses also a database connection defined by the ColdFusion server, called “q01nw”. This datasource refers to the NorthWind MS SQL database. This will be used in the Datasource attribute of the CFQUERY object.

The page Data.cfm is responsible for relaying virtual XML documents to the FusionCharts embedded in Chart.cfm. You should note that Data.cfm 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:

<cfset curYear = url.Year>

<cfset strXMLData = "">

<cfquery name="oRs" datasource="q01nw">
   SELECT TOP 5 Country, SUM(ExtendedPrice) As Total,
   COUNT(DISTINCT OrderID) As orderNumber
   FROM Invoices
   WHERE YEAR(OrderDate)=#curYear#
   GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC
</cfquery>

<cfset strXMLData = "<graph caption='Top 5 Countries for the Year " & curYear & "' shownames='1' showvalues='0' decimalPrecision='0' numberPrefix='$'>" & chr(10) >
<cfset intCounter = 0>
<cfloop query="oRs">
   
   <cfset strXMLData = strXMLData & "<set name='" & Country &"' value='" & Total & "' color='" &Colors[(intCounter mod ArrayLen(Colors))+1] & "'/>" &chr(10)>
   <cfset intCounter=intCounter+1>
</cfloop>

<cfset strXMLData = strXMLData & "</graph>">
<cfoutput>#strXMLData#</cfoutput>

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

We first request the year, for which we have to show the data. The year was passed in the format Data.cfm?year=1996 (or 1997 or 1998) by the FusionCharts charts contained in the page Chart.cfm
Next, we initialize a few string variables and a recordset object to enable us to 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 Application.cfm).
Thereafter, we add the ending </graph> element to the XML document (stored as string in strXMLData),
In the end, we just write this XML data document to the output stream.

When you try viewing this page individually in the browser (in the format http://localhost/...Path.../DBExamples/CF/dataURL/Data.cfm?year=1996), you'll get the following XML output. You should always make sure, that while using dataURL method, your dataURL script should output just XML data and no HTML tags.

This is exactly the XML that we need for our 3D Column Chart. So, let's now shift our attention to Chart.cfm and see the code it contains to attain the following tasks:

Present the list of unique years in a drop down list to the user - so that the end user can select the year for which the chart should appear.
Embed the FusionCharts Chart in the CF page and convey dataURL in the format Data.cfm?year=199x

Chart.cfm contains the following code:

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

<cfset curYear = Year>

<cfquery name="oRsYears" datasource="q01nw">
   SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1
</cfquery>
<cfif curYear eq "">
<cfset curYear = oRsYears.Year>
</cfif>

<cfset strDataURL = "Data.cfm?Year=" & curYear>

<cfset strDataURL = URLEncodedFormat(strDataURL)>
<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>
<cfoutput>
   <form action="Chart.cfm" method="post">
      <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();">
         <cfloop query="oRsYears">
            <option value="#oRsYears.Year#" <cfif int(curYear) eq int(oRsYears.Year)>selected</cfif>>
#oRsYears.Year#
            </option>
         </cfloop>
         </SELECT>
         </td>
      </tr>
   </form>
</cfoutput>
<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="&dataURL=<cfoutput>#strDataURL#</cfoutput>"

<PARAM NAME=movie VALUE="../Charts/FC_2_3_Column3D.swf?chartWidth=450&chartHeight=300">
<PARAM NAME=quality VALUE=high>
<EMBED src="../Charts/FC_2_3_Column3D.swf?chartWidth=450&chartHeight=300" FlashVars="&dataURL=<cfoutput>#strDataURL#</cfoutput>" 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 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 generate the dataURL string in the format Data.cfm?year=(intYear) (i.e., Data.cfm?year=1996)
Thereafter, we embed the FusionCharts chart in this page and provide the above dataURL to it (see the code in bold above).

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