Creating drill down Visifire charts in ASP.NET using JQuery Ajax

Introduction

Drill down chart is an interactive chart that allows users to view more detailed information for selected data. In this article, we will learn to create drill down chart using Visifire. With the help of events it is easy to create drill down charts in Visifire. Click here to learn more about events.

Background

Let’s consider northwind database as data source to create a drill down of one level. Here, a pie chart displays available categories from ‘Category’ table. On click of a particular pie segment, a 2D column chart is displayed with details of all the products under that category from ‘Products’ table.

How does it work?

In a asp.net project we will be creating two pages i.e., Default.aspx and WebService.aspx. Default.aspx displays the chart and WebService.aspx acts as data supplier. Default.aspx sends Ajax request to WebService.aspx page and receives chart data XML as response.

The first step is to add reference to visifire.js and JQuery plug-in in Default.aspx. Create a chart container in HTML (DIV element) to display chart. Send an Ajax request to WebService.aspx using JQuery with specific QueryString parameters and collect the chart data XML from server to display available categories from the ‘Category’ table. Then create an instance of Visifire control, set the chart data using setDataXml (XML) API and render the chart by calling render (container Id) API, this creates a Visifire chart.

Here is a sample JavaScript code to create a chart in Visifire.

//Chart data XML
var chartXmlString = ''<vc:Chart> …...</vc:Chart>”;
 
// Create instance of Visifire
var vChart = new Visifire("SL.Visifire.Charts.xap");
 
// Set chart data XML
vChart.setDataXml(chartXmlString);

// Render chart 
vChart.render("VisifireChartContainerDivId");

Given below is a complete code of Default.aspx and screenshot displaying categories from the ‘Category’ table in form of Pie chart.

[Default.aspx]

  <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DrillDownChart._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title></title>
    
    <script src="../JQuery/jquery-1.7.min.js" type="text/javascript"></script>
    <script src="../Visifire_v4.5.0-9/Visifire.js" type="text/javascript"></script>
    
    <script type="text/javascript">
        
        var visiChart1; // Visifire javaScript object reference
        
        function CreateVisifireChart() {
            
            // Create new instance of Visifire JavaScript object
            visiChart1 = new Visifire("../Visifire_v4.5.0-9/SL.Visifire.Charts.xap");
            
            visiChart1.setWindowlessState(true);

            RenderProductCategoriesChart();
        }
        
        /* Display all product Categories in Chart */
        function RenderProductCategoriesChart() {
            
            var url = 'http://' + document.location.host + "/WebService.aspx?Action=SelectCategories";
            
            var onAjaxReqSuccess = function(chartXml) {
                visiChart1.loaded = function(charts) {
                    /* Take first chart as Silverlight chart references because 
                    in this project xml will caontain only one chart */
                    var silverlightChartRef = charts[0];

                    // Reference of first DataSeries
                    var dataSeries = silverlightChartRef.Series[0];

                    /* Attach click event with DataSeries (If you attach event with one
                    DataSeries then event will be autometically attached with all DataPoints)*/
                    dataSeries.MouseLeftButtonDown = function(sender, args) {
                        var dataPoint = sender;
                        var catagoryId = dataPoint.GetTagFromJs(); // catagoryId was set as Tag in XML for each DataPoint (Look into the code present in WebService.aspx.cs)
                        var catagoryName = dataPoint.LabelText;

                        // Exploded the Pie segment with animation
                        dataPoint.SetPropertyFromJs("Exploded", "true");
                        setTimeout(function() { DisplayProductsByCatagoryIdAndName(catagoryId, catagoryName) }, 500); // Sender is nothing but DataPoint
                    }
                };

                // Set chart data xml
                visiChart1.setDataXml(chartXml);

                // Render chart in div (VisifireChart1)
                visiChart1.render('VisifireChart1');
            };

            // Send ajax request and get the chart-data-xml as response
            SendAjaxRequest(url, onAjaxReqSuccess);
        }

        /* Display all product and Unit Price in Chart by CatagoryId and CatagoryName */
        function DisplayProductsByCatagoryIdAndName(catagoryId, catagoryName) {

            var url = 'http://' + document.location.host + "/WebService.aspx?Action=SelectProducts"
            + "&CategoryID=" + catagoryId
            + "&CategoryName=" + catagoryName;

            var onAjaxReqSuccess = function(chartXml) {

                visiChart1.loaded = function(charts) {

                    /* Take first chart as Silverlight chart references because 
                    in this project xml will caontain only one chart */
                    var silverlightChartRef = charts[0];

                    /* Attach click event with Second Title "<< Go Back" */
                    silverlightChartRef.Titles[1].MouseLeftButtonDown = function(sender, args) {
                        var chart = sender;
                        RenderProductCategoriesChart(); // Sender is nothing but DataPoint
                    }
                };

                // Set chart data xml
                visiChart1.setDataXml(chartXml);

                // Render chart in div (VisifireChart1)
                visiChart1.render('VisifireChart1');
            };

            // Send ajax request and get the chart-data-xml as response
            SendAjaxRequest(url, onAjaxReqSuccess);
        }

        /* Send ajax request and get the chart-data-xml as response */
        function SendAjaxRequest(url, onSuccess) {
            $.ajax({
                type: 'POST',
                url: url,
                context: document.body,
                success: function(response) {
                    var chartDataXml = response;
                    onSuccess(chartDataXml);
                }
            });
        }

        $(document).ready(function() {
            CreateVisifireChart();
        });
    
    </script>
    
</head>
<body>
    <form id="form1" runat="server">
    
    <br />
    <div id="VisifireChart1" style="height:300px;width:500px; text-align: left;">
        
    </div>
    </form>
</body>
</html>

On Click of a pie segment (Product category), an Ajax request is sent to WebService.aspx using JQuery with Product Id as QueryString parameter to collect the chart data XML for Column chart.

Now to make the chart drill down, attach MouseLeftButtonDown event to all the DataPoints (pie segments). To attach an event with all DataPoints (pie segments) provide a reference of the DataSeries object by attaching loaded event with chart control. Loaded event returns the reference of Silverlight chart object. Reference of DataSeries can be easily collected from the reference of chart as var dataSeries = chart.Series[0];

Please note: When an event is attached to a DataSeries, it is automatically attached to all DataPoints in that DataSeries.

Given below is the complete code of WebService.aspx and screenshot of 2D column chart displaying products of a particular category.

[WebService.aspx.cs]

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.SqlClient;
using System.Text;

namespace DrillDownChart
{
    public partial class WebService : System.Web.UI.Page
    {
        String connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;User Instance=True";

        protected void Page_Load(object sender, EventArgs e)
        {   
            Response.ClearContent();

            String query;
            DataSet ds;
            String chartXml;

            String action = Request.QueryString["Action"];

            switch (action)
            {
                case "SelectCategories":
                    query = "SELECT CategoryID, CategoryName, Description FROM Categories";
                    ds = ExecuteQuery(query);
                    chartXml = CreatePieChartXmlForCatagories(ds);

                    Response.Write(chartXml);

                    break;
                case "SelectProducts":

                    String categoryID = Request.QueryString["CategoryID"];
                    query = String.Format(@"SELECT ProductName, UnitPrice FROM  Products WHERE (CategoryID = {0})", categoryID);
                    ds = ExecuteQuery(query);
                    chartXml = CreateColumnChartXmlForProducts(ds);

                    Response.Write(chartXml);
                    break;
            }
        }

        private String CreateColumnChartXmlForProducts(DataSet ds)
        {
            StringBuilder stb = new StringBuilder();
            stb.Append("<vc:Chart >");

            stb.Append("<vc:Chart.Titles>");
            stb.Append(String.Format(@"<vc:Title FontSize=""16"" Text=""Products under {0} Category"" />", Request.QueryString["CategoryName"]));
            stb.Append(@"<vc:Title Cursor=""Hand"" FontColor=""#ff0000"" FontSize=""12"" Text="" &lt;&lt; Go Back"" />");
            stb.Append("</vc:Chart.Titles>");

            stb.Append("<vc:Chart.AxesY>");
            stb.Append(@"<vc:Axis Title=""UnitPrice"" />");
            stb.Append("</vc:Chart.AxesY>");

            stb.Append("<vc:Chart.Series>");
            stb.Append(@"<vc:DataSeries RenderAs=""Column"">");
            stb.Append(@"<vc:DataSeries.DataPoints>");

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                stb.Append(String.Format(@"<vc:DataPoint AxisXLabel=""{0}"" YValue=""{1}"" />", dr["ProductName"], dr["UnitPrice"]));
            }

            stb.Append(@"</vc:DataSeries.DataPoints>");
            stb.Append(@"</vc:DataSeries>");
            stb.Append("</vc:Chart.Series>");

            stb.Append("</vc:Chart>");
            return stb.ToString();
        }

        private String CreatePieChartXmlForCatagories(DataSet ds)
        {
            StringBuilder stb = new StringBuilder();
            stb.Append(@"<vc:Chart View3D=""True"">");

            stb.Append("<vc:Chart.Titles>");
            stb.Append(@"<vc:Title FontSize=""16"" Text=""Products Categories"" />");
            stb.Append(@"<vc:Title FontSize=""10"" Text=""(Click on a Category to see products)"" />");
            stb.Append("</vc:Chart.Titles>");

            stb.Append("<vc:Chart.Series>");
            stb.Append(@"<vc:DataSeries RenderAs=""Pie"" Cursor=""Hand"">");
            stb.Append("<vc:DataSeries.DataPoints>");

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                stb.Append(String.Format(@"<vc:DataPoint LabelText=""{0}"" YValue=""100"" ToolTipText=""{1}"" Tag=""{2}""/>", dr["CategoryName"], dr["Description"], dr["CategoryID"]));
            }

            stb.Append("</vc:DataSeries.DataPoints>");
            stb.Append(@"</vc:DataSeries>");
            stb.Append("</vc:Chart.Series>");

            stb.Append("</vc:Chart>");
            return stb.ToString();
        }

        private DataSet ExecuteQuery(String query)
        {
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();

            SqlDataAdapter da = new SqlDataAdapter(query, con);

            DataSet dataSet = new DataSet();
            da.Fill(dataSet);
            con.Close();

            return dataSet;
        }

    }
}

On Click of “Diary Products” category, a 2D column chart appears which displays all the products under “Diary”. An event is attached to drill up or go back to the previous chart (Pie Chart).

Download Source Code

Download Drill_Down_Chart_Asp_Net_CS.zip

Download Drill_Down_Chart_Asp_Net_VB.zip

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

SomnathPanja

Technical Lead

India

Member

Follow on Twitter

I'm an IT Professional, Microsoft Technologies (.Net, SQLServer, IIS, SharePoint). Currently working on - C, C#, VB, ASP.net, JavaScript, JQuery, Presentation Framework, Silverlight, WPF, XBAP and SharePoint.

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

In this article, we will learn to create drill down chart in Asp.net using Visifire.

Type	Article
Licence	CPOL
First Posted	9 Mar 2012
Views	10,464
Bookmarked	8 times

C#VBASP.NET
Javascript.NET, +