|
In this section, we'll show you how to use FusionCharts and ASP to plot charts from data contained in a database. We'll create a pie chart to show "Production by Factory" using:
- A simple method first.
- Thereafter, we'll convert this chart to use dataURL method.
We've used MySQL database here. The database dump is present in Download Package > Code > ASPClass > DB folder. You can, however, use any database with FusionCharts including MS SQL, Oracle, Access etc.
Before you go further with this page, we recommend you to please see the previous section "Basic Examples" as we start off from concepts explained in that page.
The code examples contained in this page are present in Download Package > Code > ASPClass > DBExample folder. The MySQL database dump is present in Download Package > Code > ASPClass > DB. |
|
Before we code the ASP pages to retrieve data, let's quickly have a look at the database structure. |
 |
The database contains just 2 tables:
- Factory_Master: To store the name and id of each factory (Columns : FactoryID & FactoryName ) .
- Factory_Output: To store the number of units produced by each factory for a given date.(Columns : FacrotyId, DatePro, Quantity) .
For demonstration, we've fed some dummy data in the database. Let's now shift our attention to the ASP page that will interact with the database, fetch data and then render a chart. |
|
The ASP page for this example is named as BasicDBExample.asp (in DBExample folder). It contains the following code: |
|
<%@LANGUAGE="VBSCRIPT"%>
<% option explicit %>
<%
%>
<!--#include file="../Includes/DBConn.asp"-->
<!--#include file="../Includes/FusionCharts_Gen.asp"-->
<HTML>
<HEAD>
<TITLE>
FusionCharts V3 - Database Example
</TITLE>
<%
%>
<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
</HEAD>
<BODY>
<h2><a href="http://www.fusioncharts.com" target="_blank">FusionCharts V3</a> -Database and Drill-Down Example</h2>
<%
dim FC
set FC = new FusionCharts
Call FC.setChartType("pie3d")
Call FC.setSize("650","450")
Call FC.setSWFPath("../../FusionCharts/")
dim strParam
strParam="caption=Factory Output report;subCaption=By Quantity;pieSliceDepth=30;numberSuffix= Units;decimals=0"
Call FC.setChartParams(strParam)
Dim oRs
Set oRs = Server.CreateObject("ADODB.Recordset")
dim strQuery
strQuery = "select a.FactoryID, b.FactoryName, sum(a.Quantity) as total from Factory_output a, Factory_Master b where a.FactoryId=b.FactoryId group by a.FactoryId,b.FactoryName"
Set oRs = oConn.Execute(strQuery)
if not oRs.bof then
Call FC.addDataFromDatabase(oRs, "total", "FactoryName", "" ,"")
end if
oRs.Close
set oRs=Nothing
Call FC.renderChart(false)
%>
</BODY>
</HTML> |
|
These are the steps that we performed in the above code: |
- Include FusionCharts_Gen.asp, DBConn.asp and FusionCharts.js files for easy chart rendering and database connection set up. DBConn.asp contains connection parameters to connect to MySQL database.
- Create an object of FusionCharts ASP class for Pie 3D chart.
- Set relative path of chart SWF file.
- Store chart attributes in a variable strParam.
- Set chart attributes through setChartParams() function.
- Fetch factory records and store in result. The query result creates a column/field total to store chart data and another column FactoryName to store category names.
- Add data using addDataFromDatabase() function passing the column names that store chart data values and category names.
- Close database connection.
- Render chart by renderChart() function.
|
Please go through FusionCharts ASP Class API Reference section to know more about the functions used in the above code. |
|
When you now run the code, you'll get an output as under: |
 |
|
Let's now convert this example to use dataURL method. As previously explained, in dataURL mode, you need two pages:
- Chart Container Page - The page which embeds the HTML code to render the chart. This page also tells the chart where to load the data from. We'll name this page as Default.asp.
- Data Provider Page - This page provides the XML data to the chart. We'll name this page as PieData.asp
The pages in this example are contained in Download Package > Code > ASPClass > DB_dataURL folder. |
|
Default.asp contains the following code to render the chart: |
|
<%@LANGUAGE="VBSCRIPT"%>
<% option explicit %>
<%
%>
<!--#include file="../Includes/FusionCharts.asp"-->
<HTML>
<HEAD>
<TITLE>FusionCharts V3 - dataURL and Database Example</TITLE>
<%
%>
<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
</HEAD>
<BODY>
<h2><a href="http://www.fusioncharts.com" target="_blank">FusionCharts V3</a> - dataURL and Database</h2>
<%
dim strDataURL
strDataURL = "PieData.asp"
Response.Write(renderChart("../../FusionCharts/Pie3D.swf", strDataURL, "", "FactorySum", 650, 450, false, false,false))
%>
</BODY>
</HTML>
|
|
In the above code, we have: |
- Included FusionCharts.js JavaScript class that uses codes to easily render FusionCharts.
- Included FusionCharts.asp, a ASP wrapper class to load charts easily.
- Stored the return value of PieData.asp in strDataURL.
- Finally, we renderd the chart using renderChart() method using dataURL method.
|
Note: The renderChart() function used in this code is not the same with the one we used in the previous example, though they bear same name. This is FusionCharts ASP chart embedding function; please go through Using with ASP > Basic Examples to know more about it. |
PieData.asp contains the following code to output XML Data. This code is similar like the Simple DB Example. The only difference is, here we do not render the chart but send the full XML as output stream. |
|
<%@LANGUAGE="VBSCRIPT"%>
<% option explicit %>
<%
%>
<!--#include file="../Includes/DBConn.asp"-->
<%
%>
<!--#include file="../Includes/FusionCharts_Gen.asp"-->
<%
dim FC
set FC = new FusionCharts
Call FC.setChartType("Pie3D")
Call FC.setSWFPath("../../FusionCharts/")
dim strParam
strParam="caption=Factory Output report;subCaption=By Quantity;pieSliceDepth=30;numberSuffix= Units;decimals=0"
Call FC.setChartParams(strParam)
dim strQuery
strQuery = "select a.FactoryID, b.FactoryName, sum(a.Quantity) as total from Factory_output a, Factory_Master b where a.FactoryId=b.FactoryId group by a.FactoryId,b.FactoryName"
Dim oRs
Set oRs = Server.CreateObject("ADODB.Recordset")
Set oRs = oConn.Execute(strQuery)
If Not oRs.bof Then
Call FC.addDataFromDatabase(oRs, "total", "FactoryName", "", "")
End If
oRs.Close
set oRs=Nothing
Response.ContentType= "text/xml"
Response.Write(FC.getXML())
%>
|
|
In the above code: |
- We include FusionCharts_Gen.asp and DBConn.asp files.
- Set connection to database through connectToDB() function.
- Create an object of FusionCharts ASP class for Pie 3D chart.
- Set relative path of chart SWF file.
- Store chart attributes in strParam variable.
- Set chart attributes using setChartParams() function.
- Fetch records from database and store the query output in result.
- Pass result to addDataFromDatabase() function to add chart data.
- Write the XML to output stream.
|
Please go through FusionCharts ASP Class API Reference section to know more about the functions used in the above code. |
|
When you view this page, you'll get the same output as before. |