Creating a drill down chart with ASP.NET and MSChart
In my first post, A basic reporting chart in ASP.NET, I went over the basics of creating a report using MSChart and ASP.NET and this was quit easy. However, in today’s world where the importance of business intelligence is ever increasing, the ability to drill down on a report has become the de facto standard. MSChart, ASP.NET, and HTML make it easy to create a drill-down report.
Note: Microsoft has a drill-down report in their ChartSamples example, but it was bundled as part of the same project with two-hundred other reports and was not a minimal example. It requires the use of an Access database (and I had nothing to read Access with), it has a bunch of javascript code that is for a tooltip preview of the drill down report, and the charts are in two objects. All of this made it more difficult for me to break this down. In this example, the report will be its own ASP.NET project and will be a minimal example, however the use case and the sample data is taken directly from Microsoft’s example.
Report Example Use Case
Imagine you have a list of sales reps, their regions, and their sales results. You want a report to look at total sales per region. Then you want to click on a region to the see the sales by sales rep.
Download the project here: SampleChart.zip
Step 1 – Create the Visual Studio project
- In Visual Studio, click on File | New | Project.
- Select Visual C# | Web from the Installed Templates.
- Locate and select ASP.NET Empty Web Application.
Note: I like to demonstrate using an Empty project you nothing is done for you, and you have to learn everything you actually need to do. - Give the project a name.
I named mine DrillDownChart because that is my example’s purpose. - Click OK.
- Right-click on the newly created project and click Add | Reference.
- Select the .NET tab.
- Locate System.Web.DataVisualization and highlight it.
- Click OK.
Step 2 – Add a web form for your chart
- Right-click on the Project and choose Add | New Item.
- Select Web Form.
- Give the file a name.
I named my file Report.aspx. - Click OK.
Step 3 – Create a data object for the report
Because data is often coming from a database, this example is going to use a DataSet. I am not going to connect to a database, but just use a statically build DataSet.
- Right-click on the Project and choose Add | Class.
- Give the file a name.
I named my file SalesDataSet.cs. - Make the class inherit from DataSet.
- Click OK.
Step 4 – Add example data to the data object for the report
While in a real world scenario, you would get the data from a database or somewhere, lets first just create some sample data. We are going to create two simple tables. One is a Region table, that has the region name and ID. One is a RepSales table that has sales per rep and the rep’s region id.
- Create a property with only a getter that creates a region DataTable called RegionTable.
- Add the columns needed: RegionID and RegionName.
- Add the appropriate rows.
- Create a property with only a getter that creates a reps sales DataTable called RepsSalesTable.
- Add the columns needed: ID, Name, RegionID, and Sales.
- Add the appropriate rows.
- Now in your constructor, add those to the list of Tables in your object.
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; namespace DrillDownChart { public class SalesDataSet : DataSet { public SalesDataSet() { Tables.Add(RegionTable); Tables.Add(RepsSalesTable); } public DataTable RegionTable { get { if (_RegionTable == null) { List<String> Regions = new List<string>() { "East", "West", "Central", "International", "South" }; _RegionTable = new DataTable("Region"); _RegionTable.Columns.Add("RegionID", typeof(int)); _RegionTable.Columns.Add("RegionName", typeof(string)); int i = 0; foreach (var region in Regions) { DataRow row = _RegionTable.NewRow(); row["RegionID"] = ++i; row["RegionName"] = region; _RegionTable.Rows.Add(row); } } return _RegionTable; } } private DataTable _RegionTable; public DataTable RepsSalesTable { get { if (_RepsSalesTable == null) { List<String> reps = new List<string>() { "Aaron", "Larry", "Andrew", "Mary", "Sally", "Nguyen", "Francis", "Jerry", "Danny", "Jim", "Sarah", "Hannah", "Kim", "Gerry", "Bob" }; int[] regions = { 1, 2, 3, 1, 4, 2, 4, 3, 1, 2, 2, 3, 5, 5, 5 }; int[] sales = { 10440, 17772, 23880, 7663, 21773, 32294, 11983, 14991, 17946, 8551, 19443, 27887, 30332, 16668, 21225 }; _RepsSalesTable = new DataTable("RepsSales"); _RepsSalesTable.Columns.Add("ID", typeof(int)); _RepsSalesTable.Columns.Add("Name", typeof(string)); _RepsSalesTable.Columns.Add("RegionID", typeof(int)); _RepsSalesTable.Columns.Add("Sales", typeof(int)); for (int i = 0; i < reps.Count; i++) { DataRow row = _RepsSalesTable.NewRow(); row["ID"] = i + 1; row["Name"] = reps[i]; row["RegionID"] = regions[i]; row["Sales"] = sales[i]; _RepsSalesTable.Rows.Add(row); } } return _RepsSalesTable; } } private DataTable _RepsSalesTable; } }
That is it, your fake example data is prepared.
Step 5 – Add a Chart to the Report.aspx file
- Open the Report.aspx file.
- Add a Register to the System.Web.DataVisualization assembly.
- Locate the div inside the body.
- Inside the div, add a Chart that includes a ChartArea.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Report.aspx.cs" Inherits="CompareYearsByQuarter.Report" %> <%@ 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></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Chart ID="SalesReport" runat="server"> <chartareas> <asp:ChartArea Name="ChartArea1"> </asp:ChartArea> </chartareas> </asp:Chart> </div> </form> </body> </html>
Step 6 – Add code the Report.aspx.cs file
We are going to use the same object for both the original report and the drill down report. We will just a little code that switches which data the chart is populated with.
- Open the Report.aspx.cs file.
- Create an instance of the SalesDataSet object that has our sample data.
- Add code in the Page_Load method to configure the Chart.
Note 1: The steps for this code is in the code and comments itself. I created a method for each step and then populated the methods as needed.
Note 2: Notice that the AddDataToSeries() method uses and if statement to determine whether to add the original data or the drill down data.
Note 3: Because we used a DataTable we query the example data using LINQ. It is likely that in your production reports you are using a real database and you will probably use queries directly to your database.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.Web.UI.DataVisualization.Charting; namespace DrillDownChart { public partial class RegionReport : System.Web.UI.Page { // Step 1 - Create Example Data SalesDataSet ExampleData = new SalesDataSet(); protected void Page_Load(object sender, EventArgs e) { // Step 2 - Populate chart drop down PopulateChartTypeDropDown(); // Step 3 - Create Series Series series = CreateSeries(); // Step 4 - Set the chart type SetChartType(series); // Step 5 - Add data (and if needed drilldown links to series) AddDataToSeries(series); // Step 6 - Add series into the chart's series collection SalesReport.Series.Add(series); } private void PopulateChartTypeDropDown() { List<String> chartTypes = new List<String>(Enum.GetNames(typeof(SeriesChartType))); chartTypes.Insert(0, ""); foreach (var item in chartTypes) { DropDownListChartType.Items.Add(item); } } private Series CreateSeries() { Series series = new Series("Sales"); series.BorderWidth = 3; series.ShadowOffset = 2; return series; } private void SetChartType(Series inSeries) { if (Page.Request["ChartType"] != null) DropDownListChartType.SelectedValue = Page.Request["ChartType"]; if (DropDownListChartType.SelectedValue.ToString() == "") DropDownListChartType.SelectedValue = SeriesChartType.Column.ToString(); inSeries.ChartType = (SeriesChartType)System.Enum.Parse(typeof(SeriesChartType), DropDownListChartType.SelectedValue.ToString()); } private void AddDataToSeries(Series series) { if (Page.Request["ChartType"] == null) AddAllRegionData(series); else AddSpecificRegionData(series); } private void AddAllRegionData(Series series) { DataTable sales = ExampleData.Tables["RepsSales"]; DataTable regions = ExampleData.Tables["Region"]; var query = from reps in sales.AsEnumerable() join region in regions.AsEnumerable() on reps.Field<int>("RegionID") equals region.Field<int>("RegionID") group reps by region.Field<string>("RegionName") into regionGroup select new { Region = regionGroup.Key, Sales = regionGroup.Sum(total => total.Field<int>("Sales")) }; // Populate new series with data foreach (var value in query) { series.Points.AddXY(value.Region, value.Sales); } // Step 7 - Make this series drillable for (int i = 0; i < series.Points.Count; i++) { series.Points[i].Url = string.Format("RegionReport.aspx?region={0}&ChartType={1}", series.Points[i].AxisLabel, DropDownListChartType.SelectedValue); } } private void AddSpecificRegionData(Series series) { var query = from reps in ExampleData.RepsSalesTable.AsEnumerable() join region in ExampleData.RegionTable.AsEnumerable() on reps.Field<int>("RegionID") equals region.Field<int>("RegionID") where region.Field<string>("RegionName") == (Page.Request["Region"] ?? "East") select new { RepName = reps.Field<string>("Name"), Sales = reps.Field<int>("Sales") }; // Populate new series with data foreach (var value in query) { series.Points.AddXY(value.RepName, value.Sales); } // Step 7 - Make this series drillable for (int i = 0; i < series.Points.Count; i++) //{ // // Add drill down code to drill to a third chart //} } private void AddDrillDown(Series series) { for (int i = 0; i < series.Points.Count; i++) { series.Points[i].Url = string.Format("RegionReport.aspx?region={0}&ChartType={1}", series.Points[i].AxisLabel, DropDownListChartType.SelectedValue); } } } }
Step 7 – Add an http handler to the Web.Config for the Chart
- Open the Web.Config file.
- Add an http handler for the chart.
<?xml version="1.0"?>
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0" />
<httpHandlers>
<add path="ChartImg.axd" verb="GET,HEAD,POST" validate="false"
type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
</httpHandlers>
</system.web>
</configuration>
Your project is now complete.
You now have a report that shows you the total sales per region.
Click on the report and you have the sales per region broken out by sales rep.
And your done.
Download the project here: SampleChart.zip
