FusionCharts and ASP > Combination Chart Example |
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): |
<graph PYAxisName='Amount' SYAxisName='Quantity'
showvalues='0' rotateNames='0' showAnchors='1' decimalPrecision='2' limitsDecimalPrecision='0'
divLineDecimalPrecision='0' hoverCapSepChar=' ' showShadow='0' lineThickness='3'> <categories> <category name='QUICK-...' hoverText='QUICK-Stop'/> <category name='Save-a...' hoverText='Save-a-lot Markets'/> <category name='Ernst ...' hoverText='Ernst Handel'/> <category name='Mère P...' hoverText='Mère Paillarde'/> <category name='Hungry...' hoverText='Hungry Owl All-Night Grocers'/> </categories> <dataset seriesname='' showValue='1' color='AFD8F8' numberPrefix='$' > <set value='61109.92' /> <set value='57713.58' /> <set value='48096.27' /> <set value='23332.31' /> <set value='20454.4' /> </dataset> <dataset seriesname='' showValue='1' color='A66EDD' parentYAxis='S' numberSuffix=' pcs.' anchorSides='12' anchorRadius='3' anchorBorderColor='A66EDD'> <set value='2172' /> <set value='2574' /> <set value='1950' /> <set value='750' /> <set value='799' /> </dataset> </graph> |
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 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:
The above two are present in the downloads under the folder FusionCharts2_3 > Sample Code > DBExamples > ASP > Combination. We also have a sample of another combination chart, with multiple series on a single axis under the folder FusionCharts2_3 > Sample Code > DBExamples > ASP > Combination2. We'll also need two other pages:
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: |
<% |
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 ***** 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 'strXMLData - String variable
to contain the entire XML data document for the chart |
In the above code, we're doing the following:
When you try viewing this page individually in the browser (in the format http://localhost/...Path.../DBExamples/ASP/Combination/Data.asp?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.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> <!-- #INCLUDE FILE="../Includes/Connection_inc.asp" --> <% 'Request the current year from querystring Dim intYear, strDataURL intYear = Request("Year") 'Database objects 'oRsYears - Recordset Object 'strSQL - String variable to hold SQL Query (to get all years list) Dim oRsYears, strSQL 'Initialize the recordset object and retrieve the years Set oRsYears = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1" oRsYears.Open strSQL, DB_CONNECTION 'If no years has been specified select the last one If intYear="" then While not oRsYears.EOF intYear = oRsYears("Year") oRsYears.MoveNext() Wend end if 'Move to first oRsYears.MoveFirst() 'Define dataURL strDataURL = "Data.asp?Year=" & intYear 'URL Encode the dataURL - Important Step strDataURL = Server.URLEncode(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> <form action="Chart.asp" method="post" id="form1" name="form1"> <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();"> <% While not oRsYears.EOF %> <option value="<%=oRsYears("Year")%>" <% If int(intYear)=int(oRsYears("Year")) then %> selected <% end if %>><%=oRsYears("Year")%> </option> <% oRsYears.MoveNext() Wend %> </select> </td> </tr> </form> <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="500" HEIGHT="300" id="FusionCharts" ALIGN VIEWASTEXT> <param NAME="FlashVars" value="&dataURL=<%=strDataURL%>"> <param NAME="movie" VALUE="../../../Charts/FC_2_3_MSColumnLine_DY_3D.swf?chartWidth=500&chartHeight=300"> <param NAME="quality" VALUE="high"> <param NAME="bgcolor" VALUE="#FFFFFF"> <embed src="../../../Charts/FC_2_3_MSColumnLine_DY_3D.swf?chartWidth=500&chartHeight=300" FlashVars="&dataURL=<%=strDataURL%>" quality="high" bgcolor="#FFFFFF" WIDTH="500" HEIGHT="300" NAME="FusionCharts" ALIGN TYPE="application/x-shockwave-flash" PLUGINSPAGE="http://www.macromedia.com/go/getflashplayer"> </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> <% 'Destroy objects Set oRsYears = nothing %> |
Here, in the above code, we're doing the following:
When you now view Chart.asp, you'll get the following chart: |