Generate DrillDown Chart using HighChart and ASP.NET

• Download source (RAR) - 224.7 KB
• Download source (ZIP) - 238.7 KB

Introduction

This tip will help developers to generate drilldown charts using HighChart (Jquery) and ASP.NET and SQL.

Background

Due to difficulty in showing large data in one chart, I have decided to divide this chart into sub-groups. (Here, I have divided chart into Age and Person groups.)

Using the Code

I have divided code into three parts:

1. SQL queries to pull data
2. WebService
3. WebPage

SQL DATA

Age    AgeCount (Table1)
0          12
1--2       25

Name        Age    AgeCount  (Table2)
A           1--2       2
B           1--2       1
C           1--2       7 

create table table1
(
Age varchar(10),
AgeCount INT
)

GO

create table table2
(
Name varchar(10),
Age varchar(10),
AgeCount INT
)

GO

Create Procedure usp_ManagerMetrix_AgeDashboardTotal
@intSystemId INT,
@intLoggedinUserId INT
as
Begin
  -- depend on some conditions I am going to calculate Age and AgeCount values 
  -- for this purpose Paramaters are defined
  select Age,AgeCount from Table1

End



Create Procedure usp_ManagerMetrix_AgeDashboardDetail
@intSystemId INT,
@intLoggedinUserId INT,
@AgeType varchar(10)
as
Begin
  -- depend on selected age conditions I am going to dispaly drilldown chart 
  select Age,Name,AgeCount from Table2
  where Age=@AgeType

End

JavaScript Code

<script src="Script/jquery.min.js" type="text/javascript"></script>
<script src="Script/highcharts.js" type="text/javascript"></script>
<script src="Script/drilldown.js" type="text/javascript"></script>
<script type="text/javascript">

       $(document).ready(function () {
         $.ajax({
               type: "POST",
               contentType: "application/json; charset=utf-8",
               url: "Services/WebServiceChart.asmx/GetTotal",
               data: JSON.stringify({ intSystemId: 1, intLoggedinUserId: 1 }),
               dataType: "json",
               success: function (Result) {
               Result = Result.d;
                   var data = [];
                   for (var i in Result) {
                       var serie = { name: Result[i].Age,
                       y: Result[i].AgeCount, drilldown: Result[i].drilldown };
                       data.push(serie);
                   }
                   BindChart(data);
               },

               error: function (Result) {
                   alert(Result.toString());
               }
           });
       });

       function BindChart(seriesArr) {
            $('#container').highcharts({
               chart: {
                   type: 'column',
                   backgroundColor: '#CCE6FF',
                   borderColor: '#6495ED',
                   borderWidth: 2,
                   className: 'dark-container',
                   plotBackgroundColor: '#F0FFF0',
                   plotBorderColor: '#6495ED',
                   plotBorderWidth: 1,

                   events: {
                       drilldown: function (e) {
                          if (!e.seriesOptions) {
                               var chart = this;
                               chart.showLoading('Loading Data ...');
                               var dataArr = CallChild(e.point.name);
                               chart.setTitle({
                                   text: 'DrillDown Report'
                               });

                               data = {
                                   name: e.point.name,
                                   data: dataArr
                               }

                               setTimeout(function () {
                                   chart.hideLoading();
                                   chart.addSeriesAsDrilldown(e.point, data);
                               }, 1000);
                           }
                       }
                  }
               },

               title: {
                   text: 'Age wise Report'
               },

               xAxis: {
                   type: 'category',
                   labels: {
                       rotation: -45,
                       style: {
                           fontSize: '13px',
                           fontFamily: 'Verdana, sans-serif'
                       }
                  }
               },

               yAxis: {
                   title: {
                       text: 'Total No. of Request'
                   }
               },

               tooltip: {
                   headerFormat: '<span style="font-size:11px"
                   >{series.name}</span><br>',
                   pointFormat: '<span style="color:{point.color}"
                   >{point.name}</span>: <b>{point.y}</b> of total<br/>'
               },
               legend: {
                   enabled: false
               },

               plotOptions: {
                   series: {
                       borderWidth: 0,
                       dataLabels: {
                           enabled: true
                       }
                   }
               },

               series: [{
                   name: 'Age',
                   colorByPoint: true,
                   pointWidth: 50,
                   data: seriesArr
               }],

               drilldown: {
                   series: [{
                       pointWidth: 50
                   }]
               }
           });
       }

       function CallChild(name) {
            var Drilldowndata = [];
           $.ajax({
               type: "POST",
               contentType: "application/json; charset=utf-8",
               url: "Services/WebServiceChart.asmx/GetTotalDetail",
               data: JSON.stringify({ intSystemId: 1,
               intLoggedinUserId: 1, AgeType: name }), //JSON.stringify({ Age: Age }),
               dataType: "json",
               success: function (Result) {
                   Result = Result.d;
                   for (var i in Result) {
                       var serie = { name: Result[i].Name, y: Result[i].AgeCount };
                       Drilldowndata.push(serie);
                   }
               },

               error: function (Result) {
                   alert("Error");
               }
           })
           return Drilldowndata;
       }
   </script>

On WebPage, add this div:

<div id="container" style="width 100px; height: 400px;">
   </div>

WebService Code

   using System.Web.Services;
   using Microsoft.Practices.EnterpriseLibrary.Data;

  [WebService(Namespace = "http://tempuri.org/")]
  [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
  [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
  [System.Web.Script.Services.ScriptService]

    public class WebServiceChart : System.Web.Services.WebService
    {
        public class AgeEntity
        {
            public string Age { get; set; }
            public int AgeCount { get; set; }
            public string drilldown { get; set; }
        }

        public class AgeNameEntity
        {
            public string Age { get; set; }
            public string Name { get; set; }
            public int AgeCount { get; set; }
        }

        [WebMethod]
        public List<AgeEntity> GetTotal(int intSystemId, int intLoggedinUserId)
        {
            List<AgeEntity> Total = new List<AgeEntity>();
            DataSet ds=new DataSet() ;
            try
            {
                string constr = "Data Source=SERVER;Initial Catalog=DB;
                	User ID=ID;password=PWD;Connect Timeout=1000;";
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        SqlParameter SystemId =
                        	new SqlParameter("@intSystemId",SqlDbType.Int,int.MaxValue);
                        SystemId.Value = 1;
                        SqlParameter LoggedinUserId =
                        	new SqlParameter("@intLoggedinUserId", SqlDbType.Int, int.MaxValue);
                        LoggedinUserId.Value = 1;
                        cmd.Parameters.Add(SystemId);
                        cmd.Parameters.Add(LoggedinUserId);
                        cmd.Connection = con;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "usp_ManagerMetrix_AgeDashboardTotal";
                        con.Open();
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(ds, "dsTotal");
                        }
                    }
                }

                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables["dsTotal"].Rows.Count > 0)
                       {
                            foreach (DataRow dr in ds.Tables["dsTotal"].Rows)
                            {
                                Total.Add(new AgeEntity
                                {
                                    Age = dr["Age"].ToString(),
                                    AgeCount = Convert.ToInt32(dr["AgeCount"]),
                                    drilldown = "Age" + dr["Age"].ToString()  //true
                                });
                            }
                        }
                    }
                }
            }
           catch (Exception ex)
            {

            }
            return Total;
        }

        [WebMethod]
        public List<AgeNameEntity> GetTotalDetail(int intSystemId, int intLoggedinUserId, string AgeType)
        {
            List<AgeNameEntity> TotalDetail = new List<AgeNameEntity>();
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection
            	(" Data Source=SERVER;Initial Catalog=DB;User ID=ID;password=PWD;")
{
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "usp_ManagerMetrix_AgeDashboardDetail";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@intSystemId", 1);
                    cmd.Parameters.AddWithValue("@intLoggedinUserId", 1);
                    cmd.Parameters.AddWithValue("@AgeType", AgeType);
                    cmd.Connection = con;
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds, "dsDetail");
                    }
                }
            }

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables["dsDetail"].Rows.Count > 0)
                    {
                        foreach (DataRow dr in ds.Tables["dsDetail"].Rows)
                        {
                            TotalDetail.Add(new AgeNameEntity
                            {
                                Age ="Age"+ dr["Age"].ToString(),
                                Name = dr["Name"].ToString(),
                               AgeCount = Convert.ToInt32(dr["AgeCount"])
                            });
                        }
                    }
                }
            }
            return TotalDetail;
        }
    }

Points of Interest

• HighChart implementatation using ASP.NET