Create advanced ASP.NET DrillDown Chart using LINQ
DOWNLOAD In one of my posts , I explained basics of creating chart in .NET applications and how to add 3D effects to it using Micr...
https://www.programming-free.com/2012/10/create-advanced-aspnet-drilldown-chart.html?m=0
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.
Faisalabad is one of the biggest cities in Pakistan and the hub of the textile industry. It is widely acknowledged as the Manchester of Pakistan due to its large industrial role. The quality of the fabrics produced in this city has no parallel. In fact, the fabric is something of a specialty of Faisalabad. Many people from all over the country flock to this city for a spot of cloth shopping. We aim to provide you all of the best of Faisalabad at our store. pakistani lawn brands , pakistani lawn brands
ReplyDelete