Drill-Down JavaScript Charts in ASP.NET & MS SQL Server

In the third part of FusionCharts XT with ASP.NET series, we will create drill-down JavaScript charts using data from MS SQL Server. Drill-down charts are best suited for situations which require the user to go from a summarized view to a granular view. Drill-down charts can present annual revenue >> quarterly revenue >> monthly revenue; or global sales >> sales by country >> sales by state >> sales by city. FusionCharts XT supports unlimited levels of drill-down, and the chart types and chart attributes can be configured for each level. To create drill-down charts in ASP.NET, we will use the .NET library that is bundled with the FusionCharts Suite. This library automatically generates the HTML, JavaScript and XML required to plot your charts, so that you don’t need to.

Table of Contents

FusionCharts XT with ASP.NET

• Part 1 – Create JavaScript charts in ASP.NET (C#)
• Part 2 – Plot JavaScript charts using SQL Server data
• Part 3 – Create Drill-Down charts in ASP.NET(C#)
• Part 4 – Create LinkedCharts in ASP.NET(C#)

What we are going to visualize

We will use the same database that powers our showcase Management Dashboard. First we will create a drill-down chart which goes from annual revenue >> monthly revenue.

.NET library bundled with FusionCharts XT

FusionCharts XT is essentially a JavaScript charting library. In order to render a chart, a certain amount of HTML and JavaScript is required, along with the chart data in XML or JSON. To automatically generate this HTML and JavaScript code, we provide a .NET library with FusionCharts XT, which generates the HTML and JavaScript required. You can find this library in

FusionCharts XT Download Package > Code > CS > Bin > FusionCharts.dll

What we will need

• Visual Studio or Visual Web Developer
• SQL Server
• The latest version of FusionCharts XT
• Sample SQL Server Database

We will be using Visual Studio 2010 for this series. Visual Studio 2008 or Visual Web Developer will suit fine too. We recommend that you continue with the same Visual Studio Solution that we created in the last article. Using_MS_SQL_Server.aspx would contain the base chart, and later on we'll create another page which will contain the drill-down chart.

Attributes of a Drill-down chart

To create a simple drill-down chart, you will require the following 3 things:

• A parent chart (which we already have from the previous article of this series)
• The link attribute added to the data plots of the parent chart (see below how to add this attribute)
• The child chart (we need to create this)

To create a dataplot (be it column, pie or line/area anchor) in XML, you would have to specify the label and the value attributes for the set element. In Using_MS_SQL_Server.aspx.cs, change the following line:

xmlStr.AppendFormat("", rst["yr"].ToString(), rst["AMOUNT"].ToString());
[/cceWl]
to include the [cciel lang='xml']link[/cciel] attribute:
[cceWl lang='csharp']
xmlStr.AppendFormat("", rst["yr"].ToString(), rst["AMOUNT"].ToString(), Server.UrlEncode("DrillDown.aspx?year=" + rst["yr"].ToString()));

Now when a user would click on a data plot, he would be taken to DrillDown.aspx, which would show the monthly revenues for the specified year. Note that we’ve used the Server.UrlEncode() method for the attribute, so that any special characters would be safely encoded to HTML entities. Let us now concern ourselves with the code for DrillDown.aspx.

Coding the drill-down chart’s page

• Add a new Web Form to the Solution, and save it as [cciel lang=’csharp’]DrillDown.aspx[/cciel].
• Include [cciel lang=’javascript’]FusionCharts.js[/cciel] in this page, with the correct path.
• Create a [cciel lang=’javascript’]Literal[/cciel] tag with a unique ID. [cceWl lang=’html’][/cceWl] This is where the chart will be rendered.
• In DrillDown.aspx.cs reference the following:

  using System.Data;
  using System.Data.Sql;
  using System.Data.SqlClient;
  using System.Text;
  using InfoSoftGlobal;
  [/cceWl]

This prepares the newer page to create charts. Thereafter, write the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text;
using InfoSoftGlobal;

public partial class DataProvider : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// Construct the connection string to interface with the SQL Server Database
string connStr = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersFusion ChartsDocumentsVisual Studio 2010WebSitesFusionChartsXT_with_ASPNETApp_DataFusionChartsDB.mdf;Integrated Security=True;User Instance=True";

// Initialize the string which would contain the chart data in XML format
StringBuilder xmlStr = new StringBuilder();

// Capture the year from the querystring
String year = Request.QueryString["year"];

// Provide the relevant customization attributes to the chart
xmlStr.Append("");

// Create a SQLConnection object
using (SqlConnection conn = new SqlConnection(connStr))
{
// Establish the connection with the database
conn.Open();

// Construct and execute SQL query which would return the total amount of the sales for each month of the year specified
SqlCommand query = new SqlCommand("SELECT SUM(FC_OrderDetails.UnitPrice * FC_OrderDetails.Quantity) AS AMOUNT, DATENAME(MONTH, FC_Orders.OrderDate) AS Month FROM FC_Orders INNER JOIN FC_OrderDetails ON FC_OrderDetails.OrderID = FC_Orders.OrderID WHERE YEAR(FC_Orders.OrderDate) = " + year.ToString() + " GROUP BY MONTH(FC_Orders.OrderDate), DATENAME(MONTH, FC_Orders.OrderDate) ORDER BY MONTH(FC_Orders.OrderDate), DATENAME(MONTH, FC_Orders.OrderDate)", conn);

// Begin iterating through the result set
SqlDataReader rst = query.ExecuteReader();

while (rst.Read())
{
// Construct the chart data in XML format
xmlStr.AppendFormat("", rst["Month"].ToString(), rst["AMOUNT"].ToString());
}

// End the XML string
xmlStr.Append("");

// Close the result set Reader object and the Connection object
rst.Close();
conn.Close();

// Set the rendering mode to JavaScript, from the default Flash.
FusionCharts.SetRenderer("javascript");

// Call RenderChart(), pass the correct parameters, and write the return value to the Literal tag
drilldown.Text = FusionCharts.RenderChart(
"FusionChartsXT/Column2D.swf", // Path to chart's SWF
"", // Page which returns chart data. Leave blank when using Data String.
xmlStr.ToString(), // String containing the chart data. Leave blank when using Data URL.
"monthly_revenue", // Unique chart ID
"640", "340", // Width & Height of chart
false, // Disable Debug Mode
true); // Register with JavaScript object
}
}
}

Save this page. Switch to Using_MS_SQL_Server.aspx, and run the project. This is what you should see:

Customizing the Drill-down chart

In FusionCharts XT, you can define the following types of links:

• Links that open in the same tab
• Links that open in a new tab
• Links that open in a specified frame
• Links that open in a new pop-up window
• Links that invoke JavaScript functions
• Use the whole chart as a hotspot to use the simple link types named above

You can read more about customizing drill-down charts in our documentation. You can read an older blog post where we talk about making your drill-down charts more intuitive.

Download source files for these samples

Next in the series: LinkedCharts in ASP.NET

We initially planned to write about Drill-down charts and LinkedCharts in a single post. However, we will cover LinkedCharts in ASP.NET(C#) exclusively in the next article. Till then you can read more about LinkedCharts from our documentation. Stay tuned..