FusionCharts Free Documentation

Using FusionCharts with ASP > Plotting data from a database

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:

dataXML method first.
Thereafter, we'll convert this chart to use dataURL method.

For the sake of ease, we'll use an Access Database. The database is present in Download Package > Code > ASP > DB folder. You can, however, use any database with FusionCharts including MS SQL, Oracle, MySQL 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 > ASP > DBExample folder. The Access database is present in Download Package > Code > ASP > 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
Factory_Output: To store the number of units produced by each factory for a given date.

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 dataXML method example is named as BasicDBExample.asp (in DBExample folder). It contains the following code:

<%@ Language=VBScript %>
<HTML>
   <HEAD>
      <TITLE>FusionCharts Free - Database Example</TITLE>
      <SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
   </HEAD>
   
   'Adding library script that will connect to the Access Databse file automatically.
   
   <BODY>
   <CENTER>
   <%
   'In this example, we show how to connect FusionCharts to a database.
   'For the sake of ease, we've used an Access database which is present in
   '../DB/FactoryDB.mdb. It just contains two tables, which are linked to each
   'other.

   'Database Objects - Initialization
   Dim oRs, oRs2, strQuery
   'strXML will be used to store the entire XML document generated
   Dim strXML

   'Create the recordset to retrieve data
   Set oRs = Server.CreateObject("ADODB.Recordset")

   'Generate the chart element
   strXML = "<graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='30' formatNumberScale='0'>"

   'Iterate through each factory
   strQuery = "select * from Factory_Master"
   Set oRs = oConn.Execute(strQuery)

   While Not oRs.Eof
      'Now create second recordset to get details for this factory
      Set oRs2 = Server.CreateObject("ADODB.Recordset")
      strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" & ors("FactoryId")
      Set oRs2 = oConn.Execute(strQuery)
      'Generate <set name='..' value='..'/>
      strXML = strXML & "<set name='" & ors("FactoryName") & "' value='" & ors2("TotOutput") & "' />"
      'Close recordset
      Set oRs2 = Nothing
      oRs.MoveNext
   Wend
   'Finally, close <chart> element
   strXML = strXML & "</chart>"
   Set oRs = nothing

   'Create the chart - Pie 3D Chart with data from strXML
   Call renderChart("../../FusionCharts/FCF_Pie3D.swf", "", strXML, "FactorySum", 650, 450)
%>
</BODY>
</HTML>

The following actions are taking place in this code:

We first include FusionCharts.js JavaScript class and FusionCharts.asp , to enable easy embedding of FusionCharts.
We then include DBConn.asp, which contains connection parameters to connect to Access database.
Thereafter, we generate the XML data document by iterating through the recordset and store it in strXML variable.
Finally, we render the chart using renderChart() method and pass strXML as dataXML.

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 > ASP > DB_dataURL folder.

Default.asp contains the following code to render the chart:

<%@ Language=VBScript %>

      <HTML>

      <HEAD>

   <TITLE>	FusionCharts Free - dataURL and Database  Example</TITLE>

   <SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>

</HEAD>

<!-- #INCLUDE FILE="../Includes/FusionCharts.asp" --> 

<BODY>

   <%

   'In this example, we show how to connect FusionCharts to a database 

   'using dataURL method. In our previous example, we've used dataXML method

   'where the XML is generated in the same page as chart. Here, the XML data

   'for the chart would be generated in PieData.asp.

   'For the sake of ease, we've used an Access database which is present in

   '../DB/FactoryDB.mdb. It just contains two tables, which are linked to each

   'other.

   'Variable to contain dataURL

   Dim strDataURL	

   'the asp file piedata.asp interacts with the database, 

   'converts the data into proper XML form and finally 

   'relays XML data document to the chart

   strDataURL = "PieData.asp"

   'Create the chart - Pie 3D Chart with dataURL as strDataURL

   Call renderChart("../../FusionCharts/FCF_Pie3D.swf", strDataURL, "", "FactorySum", 650, 450)

%>

</BODY>

</HTML>

In the above code, we:

Include FusionCharts.js JavaScript class and FusionCharts.asp,
Create the dataURL string and store it in strDataURL variable.
Finally, we render the chart using renderChart() method and set dataURL as strDataURL.

PieData.asp contains the following code to output XML Data:

<%@ Language=VBScript %>

<%
   'This page generates the XML data for the Pie Chart contained in
   'Default.asp.

   'For the sake of ease, we've used an Access database which is present in
   '../DB/FactoryDB.mdb. It just contains two tables, which are linked to each
   'other.

   'Database Objects - Initialization
   Dim oRs, oRs2, strQuery
   'strXML will be used to store the entire XML document generated
   Dim strXML

   'Create the recordset to retrieve data
   Set oRs = Server.CreateObject("ADODB.Recordset")

   'Generate the graph element
   strXML = "<graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='30' formatNumberScale='0'>"

   'Iterate through each factory
   strQuery = "select * from Factory_Master"
   Set oRs = oConn.Execute(strQuery)

   While Not oRs.Eof
      'Now create second recordset to get details for this factory
      Set oRs2 = Server.CreateObject("ADODB.Recordset")
      strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" &       ors("FactoryId")
      Set oRs2 = oConn.Execute(strQuery)
      'Generate <set name='..' value='..' />
      strXML = strXML & "<set name='" & ors("FactoryName") & "' value='" & ors2("TotOutput") & "' />"
      'Close recordset
      Set oRs2 = Nothing
      oRs.MoveNext
   Wend
   'Finally, close <graph> element
   strXML = strXML & "</graph>"
   Set oRs = nothing

   'Set Proper output content-type
   Response.ContentType = "text/xml"

   'Just write out the XML data
   'NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
   Response.Write(strXML)
%>

In the above page:

We generate the data and store it in strXML variable
Finally, we write this data to output stream without any HTML tags.

When you view this page, you'll get the same output as before.