Create advanced ASP.NET DrillDown Chart using LINQ



In one of my posts, I explained basics of creating chart in .NET applications and how to add 3D effects to it using Microsoft Chart Control. Now I am going to explain how to implement one of the advanced and most widely used reporting concept known as the "Drill-down Charting" that helps build charts that are sensitive to mouse clicks. As the name itself suggests, using drill-down it is possible to view additional data associated with each plot displayed on the chart. This topic is also explained in the Microsoft Chart Samples provided by Microsoft, but it is not very easy for a beginner to catch up at once. So, I am aiming at writing a simple yet effective post on how to drill-down a chart in ASP.NET Web Application.




For this illustration, I have used the same use case scenario that is provided in the Microsoft Sample but with data coming from MySql Database. Consider you have table of data consisting of sales rep names, their sales value and the region in which they made the sales for the company. In the first chart we will display chart for aggregated sales value for each region made by all sales representatives and when a user clicks on each region, we will display chart based on sales made by each sales representative in that particular region.  First step is to create two tables in Mysql Server or any database of your choice. Make sure the structure and content of the tables  is exactly same as shown below.

RegionTable

+----------+------------+
| regionid | regionname |
+----------+------------+
|        1 | East       |
|        2 | West       |
|        3 | North      |
|        4 | South      |
+----------+------------+

SalesRepTable

+------+---------+----------+-------+
| id   | name    | regionid | sales |
+------+---------+----------+-------+
|    1 | David   |        3 | 10000 |
|    2 | Tom     |        2 | 12000 |
|    3 | Chris   |        1 |  9000 |
|    4 | Peter   |        4 | 15000 |
|    5 | Sarah   |        3 | 17000 |
|    6 | Mike    |        1 | 10500 |
|    7 | Terry   |        2 | 13000 |
|    8 | Jim     |        4 | 12300 |
|    9 | Ferer   |        1 | 14550 |
|   10 | Francis |        3 | 12530 |
|   11 | Steve   |        1 | 11530 |
|   12 | Brian   |        4 |  9250 |
|   13 | Priya   |        3 | 10000 |
|   14 | Linda   |        2 | 10090 |
|   15 | Jenni   |        3 | 12090 |
+------+---------+----------+-------+

Next step is to create an ASP.NET Web application, name it as 'DrillDownChartDemo' and create a web page, name it as "Default.aspx". Open the page you just created, drag and drop chart control from toolbox on to the page. The source code should look like the code shown below, yet control names and some style properties may differ. If you are a newbie to ASP.NET web application I suggest you to use the same names I have used in this application to avoid simple mistakes.

<%@ Page Language="C#" AutoEventWireup="true"     
         CodeBehind="Default.aspx.cs"
         Inherits="DrillDownChartDemo.Default" %>
<%@ Register assembly="System.Web.DataVisualization,   
         Version= 4.0.0.0, Culture=neutral,    
         PublicKeyToken=31bf3856ad364e35"   
         namespace="System.Web.UI.DataVisualization.Charting"    
         tagprefix="asp" %>

<!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 runat="server">
    <title>Drill-Down Chart Demo</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
       <asp:Chart ID="Chart2" runat="server" Height="296px" 
            Width="438px"  imagetype="Png" 
            BackGradientStyle="LeftRight" BackColor="AliceBlue"               
            Palette="Pastel" > 
            <borderskin skinstyle="Emboss"></borderskin>
            <series>
            </series>
            <chartareas>
                <asp:ChartArea Name="ChartArea1">
                </asp:ChartArea>
            </chartareas>
        </asp:Chart>
    </div>
    </form>
</body>
</html>

Now go to Default.aspx.cs page and paste the below code under Page_Load event.

protected void Page_Load(object sender, EventArgs e)
        {
            // Connect to MySql database and retrieve data from  the tables
            string cs = "Server=localhost;Database=test;Uid=root;Pwd=xxxxxx;";
            MySqlConnection cnx = new MySqlConnection(cs);
            string reg = "SELECT * FROM regiontable";
            string sales = "SELECT * FROM salesreptable";
            MySqlDataAdapter adapter = new MySqlDataAdapter(reg, cnx);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "regiontable");
            DataTable regiontable = ds.Tables[0];
            adapter = new MySqlDataAdapter(sales, cnx);
            ds = new DataSet();
            adapter.Fill(ds, "salesreptable");
            DataTable salesrep = ds.Tables[0];


            //Create new series to populate the chart with data
            Series series = new Series("Column");
            series.ChartType = SeriesChartType.Column;
          
  
            string region1 = "";
            //if page request value for "region" is not null,
            //then populate chart with data for sales by each representative
            //else populate chart with region specific data
           
            if (this.Page.Request["region"] != null)
            {
                region1 = (string)this.Page.Request["region"];
                var query = from reps in salesrep.AsEnumerable()
                            join regions in
                            regiontable.AsEnumerable()
                            on reps.Field<int>("regionid") equals 
                            regions.Field<int>("regionid")where
                            regions.Field<string>("regionname")==                                          (Page.Request["Region"] ?? "East")
                            select new { RepName =   
                            reps.Field<string>("name"), 
                            Sales = reps.Field<int>("sales") };
                foreach (var value in query)
                {
                    series.Points.AddXY(value.RepName, value.Sales);
                }
                Chart2.Series.Add(series);
                Chart2.Palette= ChartColorPalette.Fire;
                Chart2.Titles.Add("Sales by each Representative");
            }
            else
            {
                var query = from reps in salesrep.AsEnumerable()
                            join regions in regiontable.AsEnumerable()
                            on reps.Field<int>("regionid") equals 
                            regions.Field<int>("regionid")
                            group reps by regions.Field<string>("regionname") 
                            into regionGroup
                            select new { Region = regionGroup.Key, Sales =  
                    regionGroup.Sum(total >= total.Field<int>("Sales")); 
            }
                foreach (var value in query)
                {
                    series.Points.AddXY(value.Region, value.Sales);
                }

                //Code to drill down data
                for (int i = 0; i < series.Points.Count; i++)
                {
                    series.Points[i].Url = 
                    string.Format("Default.aspx?region={0}",
                    series.Points[i].AxisLabel);                    
                }
                Chart2.Series.Add(series);
                Chart2.Titles.Add("Sales for each Region");
        }
        }


In the above code, connection to MySql Database is made and data is retrieved from both the tables and stored in DataTable objects. If you are using any database other than MySql, replace that piece of code with code to establish connection to your database, otherwise just modify the connection string to connect to your MySql server

Code Explanation

A new Series object is created and named as "Column". This series is populated with data based on the page request attribute called region 's value. Initially when the page loads, there will be no request attributes sent along with the url to the browser. Whenever click event is encountered on any of the data points in the chart, in this case regions, the page is requested again with "region" attribute set to the value of axis label of the data point being clicked. Based on this value, series object is populated using query to get data for sales by each representative for that particular region mentioned in the request attribute. Finally, the populated series object is added to the Chart control to be rendered on the web page.

I have used LINQ to query data in the DataTable object for better performance. For easy understanding, these LINQ queries are equivalent to the following SQL queries,

Sales based on Region


select a.regionname,b.sales from RegionTable a JOIN (select regionid,sum(
sales) as sales from SalesRepTable group by regionid) as b on a.regionid=b.regio
nid;

Sales by sales representative for each region

select name,sales from SalesRepTable where regionid in(select regionid from RegionsTable where regionname="East");


You can download the source code for this demo project(use download button at the beginning of this article) and execute it yourself to see the output and implementation. An high level overview of the output is shown above in the introduction image.

Please leave your comments and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading!!

Subscribe to GET LATEST ARTICLES!


Related

MySql 4190648635131536727

Post a Comment

emo-but-icon

SUBSCRIBE


item