Monday, March 14, 2011

Building a Dashboard Using The Microsoft Chart Controls

Most experienced developers will tell you that end users tend to "judge an application by its cover". In other words, they don't care how long you spent building an application or what techniques you employed to build it. They are only concerned with how it looks. I can recall a few times in my life where I spent many endless days and nights building an application just to meet a deadline and the first remark from the end-users was "can you change the color of that label" or "can you put our logo on the main page". They don't care that the application meets the specifications or that the project was on time and under budget, that was expected. They are more concerned with the way things look.
In my current job, I built an ASP.NET MVC application to track server inventory. Overall the application is a huge improvement over the previous tracking tools. Which by the way, consisted of a few excel spreadsheets stored on a remote file share. Strangely enough, the first comment I got from my management after rolling out my application was "Can you make a dashboard?". I guess, they really craved something visual and interesting on the main page to give them a warm and fuzzy feeling about the application. So after some research I stumbled upon the Microsoft Chart Controls. A couple of hours later I had a dashboard created and here is the result:
Integrating the chart controls into your ASP.NET MVC application is very easy. To get started, you will need to modify a few keys in the appSettings and httpHandlers section of your web.config file. Most likely you will just cut and paste my settings without giving it a second thought. However, it is important that you configure the value of the ChartImageHandler key according to your environment. The are three different storage modes which are: session, file and memory. Ultimately, your choice will need to be based on your architecture, available server resources and other weighing factors. For a complete explanation of the settings and possible values please view this blog post by Delian Tchoparinov.







<appSettings>
    <add key="ChartImageHandler" value="storage=file;timeout=20;URL=/App_Data/MicrosoftChartControls/" />
</appSettings>
...
<httpHandlers>
  <!-- Microsoft Chart Controls -->
  <add verb="*" path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler,     System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>   
  <add verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler,     Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"   />
Now that the configuration is out of the way we can start building some charts. In my case, I created a new class named DashboardModel which will be returned to the View by the controller. When a new instance of the DashboardModel class is created I immediately establish a connection to the database and load all the data I need to build my charts. My dashboard has five pie charts but the data is collected with a single LINQ query. Since I iterate over the data a few times to summarize the data into different buckets I cache it in a List (named pieChartdata). If you are experienced with LINQ then you probably know that LINQ will typically execute a query each time you iterate over the results. By converting the results of my query to a List, I can avoid the need to repetitively pound on my database server for the data. Why? Because when I converted it to a list I forced it into memory. Now I can query that data all day long without making a round-trip to my database server. Admittedly, I am a bit of a performance freak. I blame this on my DBA background and from getting code reviews from people who learned how to program on mainframes. Anyways, if you are still unclear about why I converted my query to a list then please read this post.












public class DashboardModel
{
    DBAInventoryDataContext db = new DBAInventoryDataContext();
    List<DBASummary> pieData;
    List<DBASupportMetric> metrics;

    public DashboardModel()
    {
        pieData = db.usp_DBADashboardMetrics().ToList<DBASummary>();
        metrics = ( from x in db.DBASupportMetrics
                    orderby x.SampleDate ascending
                    select x ).ToList<DBASupportMetric>();
    }
Since I am making a total of five pie charts I decided to abstract the code that creates them. To start, I needed a common data structure that I could use to populate each chart. Therefore, I created an internal class named PieChartData. It has a Title, xValues, yValues and then an array of objects called Data that I used to populate tooltips and other miscellaneous properties. I marked it as an internal class because I have no intent of using it anywhere other than from within the DashboardModel class. I am a firm believer of only exposing classes and methods unless it is absolutely necessary.





internal class PieChartData{
    public string Title { get; set; }
    public DBASummary[] Data { get; set; }
    public string[] xValues { get; set; }
    public decimal[] yValues { get; set; }
}
Now its a simple matter of instantiating a PieChartData object and passing it to another function called BindChartData which assembles the chart:




































































private Chart BuildDatabasePieChart()
{
    var data = new PieChartData
    {
        Title = "Databases: " + (from y in pieData select y.PrimaryDatabases).Sum().ToString(),
        Data = (from x in pieData orderby x.PrimaryDatabases descending select x).ToArray(),
        xValues = (from x in pieData orderby x.PrimaryDatabases descending select x.LastName).ToArray(),
        yValues = (from y in pieData orderby y.PrimaryDatabases descending select y.PrimaryDatabases).ToArray()
    };

    return BindChartData(data);
}

private Chart BuildServerPieChart()
{
    var data = new PieChartData
    {
        Title = "Servers: " + (from y in pieData select y.PrimaryServers).Sum().ToString(),
        Data = (from x in pieData orderby x.PrimaryServers descending select x).ToArray(),
        xValues = (from x in pieData orderby x.PrimaryServers descending select x.LastName).ToArray(),
        yValues = (from y in pieData orderby y.PrimaryServers descending select y.PrimaryServers).ToArray()
    };

    return BindChartData(data);
}


private Chart BindChartData(PieChartData data)
{
    Chart chart = new Chart();
    chart.Width = 150;
    chart.Height = 300;
    chart.Attributes.Add("align", "left");

    chart.Titles.Add(data.Title); // Display a Title 
    chart.ChartAreas.Add(new ChartArea());

    chart.Series.Add(new Series());

    chart.Legends.Add(new Legend("DBAs"));
    chart.Legends[0].TableStyle = LegendTableStyle.Auto;
    chart.Legends[0].Docking = Docking.Bottom;

    chart.Series[0].ChartType = SeriesChartType.Pie;
    chart.Series[0]["PieLabelStyle"] = "Inside";
    chart.Series[0]["PieLabelStyle"] = "Disabled";
    chart.Series[0].BackGradientStyle = GradientStyle.DiagonalLeft;
    chart.Series[0].BackSecondaryColor = System.Drawing.Color.LightGray;
    chart.Series[0]["PieLineColor"] = "Black";
    chart.Series[0]["PieDrawingStyle"] = "Concave";

    for (int i = 0; i < data.xValues.Length; i++)
    {
        string x = data.xValues[i];
        decimal y = data.yValues[i];
        int dbaId = data.Data[i].ContactID;
        int ptIdx = chart.Series[0].Points.AddXY(x, y);
        var c = data.Data[i];
        DataPoint pt = chart.Series[0].Points[ptIdx];
        pt.Url = "/Instance/Index/" + dbaId.ToString();
        pt.ToolTip = c.FirstName + " " + c.LastName + ": #VALY";
        pt.LegendText = "#VALX: #VALY";
        pt.LegendUrl = "/Contact/Details/" + dbaId.ToString();
        pt.LegendToolTip = "Click to view " + c.FirstName + "'s contact     information...";
    }

    chart.Series[0].Legend = "DBAs";
    return chart;
}
Finally, I created a public property called PieCharts which creates all five pie charts and populates a list.












public List<Chart> PieCharts
{
    get
    {
        List<Chart> charts = new List<Chart>();
        charts.Add(BuildSQLPieChart());
        charts.Add(BuildOraclePieChart());
        charts.Add(BuildDatabasePieChart());
        charts.Add(BuildServerPieChart());
        charts.Add(BuildClusterPieChart());
        return charts;
    }
}
Now its time to build the controller method. I instantiate a copy of the DashboardModel object and return it to the View:



public ActionResult Index() {
    DashboardModel model = new DashboardModel();
    return View(model);
}
In the view we iterate over the pie charts and add them to a Panel.

















<%@ Page Title="Support Dashboard" Language="C#" MasterPageFile="~/Views/Shared/Site.Master"
    Inherits="System.Web.Mvc.ViewPage<DashboardModel>" %>
<%@ Register Assembly="System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <h2>Support Dashboard</h2>
    <%
        foreach ( System.Web.UI.DataVisualization.Charting.Chart pie in Model.PieCharts)
        {
           pieChartPanel.Controls.Add(pie);
        }
        supportChart.Controls.Add(Model.SupportMetricChart);       
    %>
    <asp:Panel ID="pieChartPanel" runat="server"></asp:Panel>
    <br />
    <asp:Panel ID="supportChart" runat="server"></asp:Panel>
</asp:Content>
And Voila! We have a dashboard!
Additional Resources:

No comments:

Popular Posts