FusionCharts

Here, we'll see how to generate a combination chart dynamically from a database.

We'll plotting a 3D Column Line Dual Y axis combination chart showing the Top 5 customers for a particular year. On the primary axis, we'll have the Amount figure representing the amount (in dollars) which has been transacted with that customer. On the secondary axis, we'll have Quantity axis representing the quantity traded for that year for that particular customer. The end chart would look as under:

As you can see above, we've extracted the amount and quantity figure for the top 5 customers for a particular year. All this data is contained in a view called Invoices. The entire XML code required for the above chart is as under (say for the year 1997):

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 top five customers whose data we're comparing). We've shown only the first 5 characters of the customer name, followed by ellipses, instead of the full customer name to avoid cluttering on the chart. We're showing the full customer name as tool tip. Post that, we render two <dataset> blocks - one for primary axis representing Amount figure and the second for the secondary axis representing the Quantity axis.

Let's now quickly code this chart in ASP.NET using dataURL method. It's always recommended to use the dataURL method when working with combination charts too, 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.

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 > Combination.

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

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script runat="server">
   Sub Page_Load(obj as Object, e as EventArgs)
      'Database objects
      Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))
      Dim oRs As New DataSet

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

      '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 arr_FCColors(10) As String
      arr_FCColors(0) = "AFD8F8"
      arr_FCColors(1) = "F6BD0F" 'Bright Red
      arr_FCColors(2) = "8BBA00" 'Dark Green
      arr_FCColors(3) = "A66EDD" 'Dark Pink
      arr_FCColors(4) = "F984A1" 'Variant of brown
      arr_FCColors(5) = "CCCC00" 'Orange
      arr_FCColors(6) = "999999" 'Violet
      arr_FCColors(7) = "0099CC" 'Grey
      arr_FCColors(8) = "CCCC00" 'Chrome Yellow+Green
      arr_FCColors(9) = "0372AB" 'Dark Blue

      '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.
      'strAmountDS - String variable to store the amount <dataset>
      'strQuantityDS - String variable to store Quantity dataset
      Dim strXMLData, strCategories, strAmountDS, strQuantityDS, strSQL As String
      'Initialize the <graph> element
      strXMLData = "<graph PYAxisName='Amount' SYAxisName='Quantity' showvalues='0' rotateNames='0' showAnchors='1' decimalPrecision='2' limitsDecimalPrecision='0' divLineDecimalPrecision='0' hoverCapSepChar=' ' showShadow='0' lineThickness='3'>" & vbCrLf
      'Initiate <categories> element
      strCategories = "<categories>" & vbCrLf
      'Initialize the amount and quantityt dataset
      strAmountDS = "<dataset seriesname='' showValue='1' color='" & arr_FCColors(0) & "' numberPrefix='$' >" & vbCrLf
      strQuantityDS = "<dataset seriesname='' showValue='1' color='" & arr_FCColors(3) & "' parentYAxis='S' numberSuffix=' pcs.' anchorSides='12' anchorRadius='3' anchorBorderColor='" & arr_FCColors(3) & "'>" & vbCrLf
      'Get a list of all the years
      strSQL = "SELECT TOP 5 CustomerName, SUM(ExtendedPrice) As Total, SUM(Quantity) As Quantity, SUM(Discount*ExtendedPrice) As Discount FROM Invoices WHERE YEAR(OrderDate)=" & strYear & " GROUP BY CustomerName ORDER BY SUM(ExtendedPrice) DESC"

      Dim cmd = New SqlCommand(strSQL, sqlConn)
      Dim DA = New SqlDataAdapter(cmd)
      sqlConn.Open()
      DA.Fill(oRs)

      Dim intCounter As Integer
      For intCounter = 0 To oRs.Tables(0).Rows.Count - 1
         'Generate the <category> element
         strCategories = strCategories & "<category name='" & Left(oRs.Tables(0).Rows(intCounter).Item("CustomerName"), 6) & "...' hoverText='" & oRs.Tables(0).Rows(intCounter).Item("CustomerName") & "'/>" & vbCrLf
         'Generate the set element for both amount and quantity dataset
         strAmountDS = strAmountDS & "<set value='" & oRs.Tables(0).Rows(intCounter).Item("Total") & "' />" & vbCrLf
         strQuantityDS = strQuantityDS & "<set value='" & oRs.Tables(0).Rows(intCounter).Item("Quantity") & "' />" & vbCrLf
      Next
      'Close the category and dataset elements
      strCategories = strCategories & "</categories>" & vbCrLf
      strAmountDS = strAmountDS & "</dataset>" & vbCrLf
      strQuantityDS = strQuantityDS & "</dataset>" & vbCrLf
      'Generate the XML document by concatenating the strings we've generated. Also, close the <graph> element.
      strXMLData = strXMLData & strCategories & strAmountDS & strQuantityDS & "</graph>"
      'Destroy the recordset objects
      oRs = Nothing

      'Write the XML data to output stream
      Response.Write(strXMLData)
   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 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. strAmountDs will be used to store the amount <dataset> and <set> elements. strQuantityDs will be used to store the quantity <dataset> and <set> elements.
We now initialize the <graph> element in the variable strXMLData and opening <categories> tag in the variable strCategories.
We also initialize the opening <dataset> tags for both dataset blocks in the variables strAmountDS and strQuantityDS. The first dataset has been set with a parentYAxis attribute as P (primary) and the second one with S (secondary).
Next, we retrieve the recordset from the database for top 5 customers for the selected year and iterate through the records one by one. For each record, we add a <category> element to the variable strCategories, and <set> elements in the format <set value='xxx' /> to the variables strAmountDS and strQuantityDS.
Finally, we close all the open elements in the XML data document (like <categories>, <dataset> and <graph>) using simple string concatenation and form a full XML doc 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/ASP/Combination/Data.aspx?year=1997) 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.aspx and see the code it contains to embed the chart.

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 valign="top"><table width="98%" border="0" cellspacing="0" cellpadding="2" align='center'>
            <tr>
               <td><div align="center" class="text">
               <OBJECT id="FusionCharts" codeBase="http://download.macromedia.com/pub/shockwave/cabs
/flash/swflash.cab#version=6,0,0,0"
height="300" width="500" classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" VIEWASTEXT>
               <PARAM NAME="Movie" VALUE="../../../Charts/FC_2_3_MSColumnLine_DY_3D.swf?chartWidth=500&chartHeight=300">
               <PARAM NAME="FlashVars" VALUE="&dataurl=<%= strFCdataURL %>&noCache=<%= Server.URLEncode(Now())%>">
               <EMBED src="../../../Charts/FC_2_3_MSColumnLine_DY_3D.swf" FlashVars="&dataurl=<%= strFCdataURL %>&chartWidth=500&chartHeight=300&noCache=<%= Server.URLEncode(Now())%>" quality=high bgcolor=#FFFFFF WIDTH="500" 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>
</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 view Chart.aspx, you'll get the following chart: