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.aspx: 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_NET> dataXML.

The page Chart.aspx 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:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script runat="server">
   'Initialize a variable that will store FusionCharts XML Data document
   Dim strFCdataXML as String
   'Year for which the data is to be shown
   Dim strSelectedYear as String

   Sub Page_Load(obj as Object, e as EventArgs)
      '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
      strSelectedYear = ddlSelYear.selectedItem.Value
   End Sub

   Public Function getFCXMLData() as String
      'This function returns the XML data for FusionCharts chart
      'Container for the XML Data
      Dim strFCXMLData as String

      '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)=" & strSelectedYear & " 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 " & strSelectedYear & "' 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>"

      'Return the XML data
      Return strFCXMLData
   End Function
</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">
            <PARAM NAME="FlashVars" VALUE="&chartWidth=450&chartHeight=300&dataXML=<%= getFCXMLData() %>">
            <EMBED src="../../../Charts/FC_2_3_Column3D.swf" FlashVars="&chartWidth=450&chartHeight=300&dataXML=<%= getFCXMLData() %>" 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>

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 strSelectedYear. 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 strSelectedYear.
Next, we've defined a subroutine getFCXMLData, which returns the XML data for this year. In this subroutine, we've initialized 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 array of colors that we've defined. Thereafter, we add the ending </graph> element to the XML document (stored as string in strFCXMLData).
Lastly, we embed the FusionCharts Chart in the ASP page and convey the XML data using dataXML method.

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