FusionCharts and ASP > Using dataURL method |
In the previous part, we had gone through an example of conceptually creating a chart using Northwind database. In this part, we stick to ASP (Microsoft Active Server Pages) as our choice of scripting language and revisit the process with ASP code samples. We'll be creating the "Top 5 Countries" 3D column chart using ASP and MS SQL Server, using the dataURL method. 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:
The above two are present in the downloads under the folder FusionCharts2_3 > Sample Code > DBExamples > ASP > dataURL. 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 %> <!-- #INCLUDE FILE="../Includes/Connection_inc.asp" --> <!-- #INCLUDE FILE="../Includes/FC_Colors.asp" --> <% 'Request the current year from querystring 'The year was passsed to this page in the form Data.asp?Year=199x (where x=6,7,8) Dim intYear, strDataURL intYear = Request("Year") 'Define database objects 'oRs - Recordset object 'strSQL - String variable to contain the SQL query 'intCounter - Numeric value to keep a track of number of records Dim oRs, strSQL, intCounter 'strXMLData - String variable to contain the entire XML data document for the chart Dim strXMLData strXMLData = "" 'Initialize the recordset object Set oRs = Server.CreateObject("ADODB.Recordset") '--------------------XML Data for TOP 5 COUNTRIES------------------------- 'Query the database strSQL = "SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=" & intYear & " GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC" oRs.Open strSQL, DB_CONNECTION 'Create the <graph> element strXMLData = "<graph caption='Top 5 Countries for the Year " & intYear & "' shownames='1' showvalues='0' decimalPrecision='0' numberPrefix='$'>" & vbCrlf 'Now iterate through each data row intCounter=0 While not oRs.EOF 'Increase the count intCounter=intCounter+1 'Append the value in format <set name='...' value='...' color='...' /> strXMLData = strXMLData & "<set name='" & ors("Country") & "' value='" & ors("Total") & "' color='" & arr_FCColors(intCounter mod intFCColors_count) & "'/>" & vbCrlf oRs.MoveNext() Wend 'Entire XML - concatenation strXMLData = strXMLData & "</graph>" oRs.Close() '-------------------------------------------------------------------- 'Destroy objects Set oRs= nothing 'Write the XML data to output stream Response.Write(strXMLData) %> |
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/dataURL/Data.asp?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. |
<graph caption='Top 5 Countries for the Year 1996' shownames='1'
showvalues='0' decimalPrecision='0' numberPrefix='$'> <set name='USA' value='38105.68' color='AFD8F8'/> <set name='Germany' value='35407.15' color='F6BD0F'/> <set name='Austria' value='25601.34' color='8BBA00'/> <set name='Brazil' value='20148.82' color='A66EDD'/> <set name='France' value='17372.76' color='F984A1'/> </graph> |
This is exactly the XML that we need for our 3D Column Chart. So, let's now shift our attention to Chart.asp and see the code it contains to attain the following tasks:
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"> <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="450" HEIGHT="300" id="FusionCharts" ALIGN=""> <PARAM NAME="FlashVars" value="&dataURL=<%=strDataURL%>"> <!-- We specify the dataURL here --> <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=<%=strDataURL%>" 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> <% 'Destroy objects Set oRsYears = nothing %> |
Here, in the above code, we're doing the following:
When you now see Chart.asp in your browser, you'll get the following output: |