FusionCharts

Here, we'll be using ASP.NET as our choice of scripting language and create the dynamic "Top 5 Countries" 3D column chart, with MS SQL Server as our database, using the dataURL method. We'll be using VB.NET as our choice of .NET language. If you want to use C# as your choice of language, you can download our FusionCharts Blueprint application in C# from our website (http://www.InfoSoftGlobal.com/FusionCharts) to learn more.

Please do read the section "Using with scripts and database", if you've not already read, before reading this section.

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.aspx - 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.aspx. We'll name this page as Data.aspx.

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

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

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script runat="server">
Sub Page_Load(obj as Object, e as EventArgs)
   'Container for the XML Data
   Dim strFCXMLData as String

   'Request the year passed to this page
   Dim strYear as String
   strYear = Request.QueryString("Year")

   'Establish a connection
   Dim DS As DataSet
   Dim MyConnection As SqlConnection
   Dim MyCommand As SqlDataAdapter

   'Get Top 5 Countries the database.
   MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))
   MyCommand = New SqlDataAdapter("SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber    FROM Invoices WHERE YEAR(OrderDate)=" & strYear & " GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC", MyConnection)

   'Fill the dataset
   DS = new DataSet()
   MyCommand.Fill(ds, "Countries")

   'Define a list of Colors
   'NOTE - You can move the array below to some global place so as to use it for all the charts and have a cleaner code
   'We've put it here to simplify the code
   Dim strColor(10) As String
   strColor(0) = "AFD8F8"
   strColor(1) = "F6BD0F" 'Bright Red
   strColor(2) = "8BBA00" 'Dark Green
   strColor(3) = "A66EDD" 'Dark Pink
   strColor(4) = "F984A1" 'Variant of brown
   strColor(5) = "CCCC00" 'Orange
   strColor(6) = "999999" 'Violet
   strColor(7) = "0099CC" 'Grey
   strColor(8) = "CCCC00" 'Chrome Yellow+Green
   strColor(9) = "0372AB" 'Dark Blue


   'Initialize the XML String
   strFCXMLData = "<graph caption='Top 5 Countries for the Year " & strYear & "' shownames='1' showvalues='0' decimalPrecision='0' numberPrefix='$'>" & vbCrLf

   'Now iterate through each data row
   Dim i As Integer
   For i = 0 To ds.Tables("Countries").Rows.Count - 1
      'Append the value in format <set name='...' value='...' color='...' />
      strFCXMLData = strFCXMLData & "<set name='" & ds.Tables("Countries").Rows(i).Item("Country") & "' value='" & ds.Tables("Countries").Rows(i).Item("Total") & "' color='" & strColor(i Mod 9) & "'/>" & vbCrLf
   Next

   'End the XML data by adding the closing </graph> element
   strFCXMLData = strFCXMLData & "</graph>"

   'Write the XML data to the output stream
   'NOTE - NO HTML CONTENTS ARE PRESENT IN THIS PAGE - not even <head></head> tags. This page should just output the XML.
   Response.Write(strFCXMLData)
End Sub
</script>

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.aspx?year=1996 (or 1997 or 1998) by the FusionCharts charts contained in the page Chart.aspx
Next, we initialize a few string variables and a recordset object to enable us to retrieve data from database.
We also initialize a variable strFCXMLData 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 have defined in the array strColor.
Thereafter, we add the ending </graph> element to the XML document (stored as string in strFCXMLData),
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/ASP/dataURL/Data.aspx?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.aspx 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 ASP page and convey dataURL in the format Data.aspx?year=199x

Chart.aspx contains the following code:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script runat="server">
   'Initialize a variable that will store FusionCharts dataURL string
   Dim strFCdataURL as String

   Sub Page_Load(obj as Object, e as EventArgs)
      'Set data page as Data.aspx - we'll add the year to it later
      strFCdataURL = "Data.aspx?Year="

      'If coming for the first time, fill the select (drop down list)
      If Not IsPostBack Then
         'Establish a connection
         Dim DS As DataSet
         Dim MyConnection As SqlConnection
         Dim MyCommand As SqlDataAdapter

         'Get the list of years from the database.
         MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))
         MyCommand = New SqlDataAdapter("SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1", MyConnection)

         'Fill the dataset
         DS = new DataSet()
         MyCommand.Fill(ds, "Orders")

         'Bind it to the drop down list
         ddlSelYear.DataSource = ds.Tables("Orders").DefaultView
         ddlSelYear.DataTextField = "Year"
         ddlSelYear.DataValueField = "Year"
         ddlSelYear.DataBind()
      End If

      'Get the selected year's index
      strFCdataURL = strFCdataURL & ddlSelYear.selectedItem.Value
      'URL Encode it
      strFCdataURL = Server.URLEncode(strFCdataURL)
   End Sub

</script>
<HTML>
<HEAD>
<TITLE>FusionCharts</TITLE>
<LINK REL='Stylesheet' HREF='../Style.css'>
</HEAD>
<body>
   <form id="YearSelect" method="post" runat="server">
   <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>
      <tr>
         <td colspan="3" class="text" align="center">Please select the year for which you want to see the chart:
         <asp:dropdownlist AutoPostBack="True" id="ddlSelYear" runat="server" class="Select"></asp:dropdownlist>
         </td>
      </tr>
      <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 id="FC2Column" codeBase="http://download.macromedia.com/pub/shockwave/cabs/
flash/swflash.cab#version=6,0,0,0"
height="300" width="450" classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" VIEWASTEXT>
                  <PARAM NAME="Movie" VALUE="../../../Charts/FC_2_3_Column3D.swf?chartWidth=450&chartHeight=300">
                  <PARAM NAME="FlashVars" VALUE="&dataurl=<%= strFCdataURL %>&noCache=<%= Server.URLEncode(Now())%>">
                  <EMBED src="../../../Charts/FC_2_3_Column3D.swf" FlashVars="&dataurl=<%= strFCdataURL %>&chartWidth=450&chartHeight=300&noCache=<%= Server.URLEncode(Now())%>" quality=high bgcolor=#FFFFFF WIDTH="450" HEIGHT="300" NAME="FC2Column" 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>
</form>
</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.aspx?year=(intYear) (i.e., Data.aspx?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.aspx in your browser, you'll get the following output: