FusionCharts Free Documentation

Using FusionCharts with VB.NET (ASP.NET) > Creating Drill-down charts

In our previous example, we had used FusionCharts to plot a chart using data stored in database. We'll now extend that example itself to create a drill-down chart which can show more information.

Before you go further with this page, we recommend you to please see the previous sections like "Plotting from Database Example", as we start off from concepts explained in those pages.

If you recall from previous example, we were showing the sum of factory output in a pie chart as under:

In this example, we'll extend this example, so that when a user clicks on a pie slice for a factory, he can drill down to see date wise production for that factory.

To set up the pie chart to enable links for drill-down involves just minor tweaking of our previous BasicDBExample.aspx. We basically need to add the link attribute for each <set> element. We create a new page Default.aspx (in DB_DrillDown folder) from the previous page with the following code changes:

The code examples contained in this page are contained in Download Package > Code > VBNET > DB_DrillDown folder.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb"
Inherits="DB_DrillDown_Default" %>

<HTML>
  <HEAD>
    <TITLE> FusionCharts Free - Database and Drill-Down Example </TITLE>
    <%
      'You need to include the following JS file, if you intend to embed the chart using JavaScript.
    %>
    <SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
  </HEAD>

  <BODY>
     <asp:Literal ID="FCLiteral" runat="server"></asp:Literal>
  </BODY>

</HTML>

Here, we have included FusionCharts.js file to enable us embed the chart using JavaScript. We are also adding an ASP control literal which acts as the container for the charts. The CreateCharts() function does the generation, and is the code behind the file Default.aspx.vb. Here is the content of the Default.aspx.vb:

Imports DataConnection
Imports InfoSoftGlobal

Partial Class DB_DrillDown_Default
Inherits System.Web.UI.Page

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      ' Generate chart in Literal Control
      FCLiteral.Text = CreateCharts()
    End Sub

   Public Function CreateChart() As String
      '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
      '../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each
      'other.

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

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

' SQL Query
strQuery = "select a.FactoryId,a.FactoryName, sum(b.Quantity) as TotOutput from factory_master a,factory_output b where a.FactoryId=b.FactoryId group by a.FactoryId,a.FactoryName"

' Open data reader
oRs = New DbConn(strQuery)

      'Iterate through each factory
      While oRs.ReadData.Read()

         'Generate <set name='..' value='..' link='..' />
         'Note that we're setting link as Detailed.asp?FactoryId=<<FactoryId>>
         '&FactoryName=<<FactoryName>>
         strXML = strXML & "<set name='" & oRs.ReadData("FactoryName").ToString() & "' value='" & oRs.ReadData("TotOutput").ToString() & "' link='" &          Server.UrlEncode("Detailed.aspx?FactoryId=" & oRs.ReadData("FactoryId").ToString()) & "&FactoryName=" & oRs.ReadData("FactoryName").ToString() & "'/>"

      End While
      'Finally, close <graph> element
      strXML = strXML & "</graph>"
      ' Close Data Reader
      oRs.ReadData.Close()

      'Create the chart - Pie 3D Chart with data from strXML
      Return FusionCharts.RenderChart("../FusionCharts/FCF_Pie3D.swf", "", strXML, "FactorySum", "650", "450", False, False)
   End Function

End Class

As you can see in the code above, we're doing the following:

Include DataConnection and InfoSoftGlobal namespace to enable databse connection and easy embedding of FusionCharts.
(Know more about DataConnection namespace)
Then we declare variables strQuery to store SQL Query that fetches data from our MS Access database and strXML to store XML data.
Thereafter, we generate the XML data document by iterating through the recordset. We store the XML data in strXML variable. To each <set> element, we add the link attribute, which points to Detailed.aspx - the page that contains the chart to show details. We pass the factory id of the respective factory by appending it to the link. We finally URL Encode the link, which is a very important step.
Finally, we render the chart using renderChart() method and pass strXML as dataXML. RenderChart()is called form the Page_Load event listener.

Let's now shift our attention to Detailed.aspx page.

The page Detailed.aspx contains the following code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Detailed.aspx.vb"
Inherits="DB_DrillDown_Detailed" %>

<HTML>
  <HEAD>
    <TITLE>
      FusionCharts Free - Database and Drill-Down Example
    </TITLE>
    <%
      'You need to include the following JS file, if you intend to embed the chart using JavaScript.
    %>
    <SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
  </HEAD>

  <BODY>
     <asp:Literal ID="FCLiteral" runat="server"></asp:Literal>
  /BODY>

</HTML>

Here we have included FusionCharts.js file to enable us embed the chart using JavaScript. We are also adding an ASP control literal which acts as the container for the charts. The CreateCharts() function does the generation, and is the code behind the file Detailed.aspx.vb. Here is the code:

Imports DataConnection
Imports Utilities
Imports InfoSoftGlobal

Partial Class DB_DrillDown_Detailed
   Inherits System.Web.UI.Page

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      ' Generate chart in Literal Control
      FCLiteral.Text = CreateChart()
   End Sub

   Public Function CreateChart() As String
      'This page is invoked from Default.asp. When the user clicks on a pie
      'slice in Default.asp, the factory Id is passed to this page. We need
      'to get that factory id, get information from database and then show
      'a detailed chart.

      'First, get the factory Id
      Dim FactoryId As String, FactoryName As String
      Dim util As New Util
      'Request the factory Id from Querystring
      FactoryId = Request.QueryString("FactoryId")
      FactoryName = Request.QueryString("FactoryName")

      Dim oRs As DbConn, strQuery As String
      'strXML will be used to store the entire XML document generated
      Dim strXML As String

'Generate the graph element string
strXML = "<graph caption='Factory " & FactoryName & " Output ' subcaption='(In Units)' xAxisName='Date' showValues='1' decimalPrecision='0' rotateNames='1' >"

' SQL Query
strQuery = "select * from Factory_Output where FactoryId=" & FactoryId

      ' Open Data Reader
      oRs = New DbConn(strQuery)
      'Now, we get the data for that factory
      While oRs.ReadData.Read()
         'Here, we convert date into a more readable form for set name.
         strXML = strXML & "<set name='" &          Convert.ToDateTime(oRs.ReadData("DatePro")).ToString("dd/MM/yyyy") & "' value='" &          oRs.ReadData("Quantity").ToString() & "' color='" & util.getFCColor() & "'/>"

      End While
         'Close <graph> element
      strXML = strXML & "</graph>"
         ' Close Data Reader
      oRs.ReadData.Close()

         'Create the chart - Column 2D Chart with data from strXML
      Return FusionCharts.RenderChart("../FusionCharts/FCF_Column2D.swf", "", strXML, "FactoryDetailed", "600", "300", False, False)
   End Function

End Class

In this page, we're:

Including DataConnection, Utility and InfoSoftGlobal namespaces from DbConn and Util class of App_Code folder and FusionCharts class of bin folder respectively.
(Know more about DataConnection namespace)
Requesting the factory id for which we've to show detailed data. This data was sent to us as query string, as a part of pie chart link.
We get the requisite data for this factory from database and then convert it into XML using string concatenation in the variable strXML.
Finally, we render a Column 2D chart using RenderChart() method to show detailed data. RenderChart()is called form the Page_Load event listener.

When you now run the app, you'll see the detailed page as under: