Using Microsoft's Chart Controls In An ASP.NET Application: Adding Statistical Formulas

By Scott Mitchell

---

A Multipart Series on Microsoft's Chart Controls

A picture is worth a 1,000 words... This adage rings especially true when it comes to reporting. Charts summarize and illuminate patterns in data in a way that long tables of numbers simply cannot. The Microsoft Chart Controls are a free and encompassing set of charts for WinForms and ASP.NET applications. This article series explores how to use these Chart Controls in an ASP.NET application. Getting Started - walks through getting started using the Chart Controls, from version requirements to downloading and installing the Chart Controls, to displaying a simple chart in an ASP.NET page. Plotting Chart Data - examines the multitude of ways by which data can be plotted on a chart, from databinding to manually adding the points one at a time. Rendering the Chart - the Chart Controls offer a variety of ways to render the chart data into an image. This article explores these options. Sorting and Filtering Chart Data - this article shows how to programmatically sort and filter the chart's data prior to display. Programmatically Generating Chart Images - learn how to programmatically create and alter the chart image file. Creating Drill Down Reports - see how to build drill down reports using the Chart control. Adding Statistical Formulas - learn how to add statistical formulas, such as mean, median, variance, and forecasts, to your charts. Enhancing Charts With Ajax - improve the user experience for dynamic and interactive charts using Ajax. Serializing Chart Data - see how to persist a chart's data and appearance to a persistent store. Using the Chart Controls with ASP.NET MVC - learn how to display charts in an ASP.NET MVC application. Exporting Charts - allow visitors to export charts as images and PDF files. (Subscribe to this Article Series! )

Introduction

The Microsoft Chart controls make it easy to take data from a database or some other data store and present it as a chart. As discussed in Plotting Chart Data, the Chart controls offer a myriad of ways to get data into a chart. You can add the data programmatically, point-by-point, or you can bind an ADO.NET DataTable directly to the Chart. You can even use declarative data source controls, like the SqlDataSource or ObjectDataSource controls.

In addition to converting your specified data points into a chart image, the Chart controls also include a wealth of statistical formulae that you can use to analyze the plotted data. For example, with a single line of code you determine the mean (average) value for data in a particular series. Likewise, with one line of code you can get the median, variance, or standard deviation. These values can be displayed as text on the page or as a stripe line on the chart itself. What's more, the Chart controls include functions to forecast future values, to compute moving averages, to identify trends, and to determine rates of change, among others.

This article looks at how to use two statistical formulae. Specifically, we'll look at how to compute and display the mean of a series, as well as how to display an exponential trend line on the chart to forecast future values. Read on to learn more!

Computing the Mean Value of a Series of Data Points

Given a list of numbers, the arithmetic mean is the sum of those numbers divided by the total quantity of numbers in the list. The mean is more colloquially referred to as the "average," and is a common metric used to analyze data.

A chart consists of one or more series, which are lists of data points. Typically, each data point is a pair of numbers that provide both the X value and Y value to be plotted. For some charts it may be helpful to know the average value of the data points plotted in a series. Consider a chart that shows gross sales by month for a particular product. In this case the X axis would list off the months (January, February, ...) while the Y axis would show the gross sales per month. In such a chart it might be helpful to know the mean (average) gross sales per month.

You could certainly compute the mean gross sales per month with a few lines of code that loop through the data points, sum up the values, and then divide by the count of data points. There's no need to write that code, however, because the Microsoft Chart controls provide a built-in function for computing the mean of a series:

'Determine the mean of the series SalesByMonth Dim mean As Double = ChartID.DataManipulator.Statistics.Mean("SalesByMonth")

Similarly, you can compute the median or the variance using the Median or the Variance functions in much the same way.

The demo available for download at the end of this article includes a page named MeanSales.aspx, which displays a line chart showing monthly sales for the products in a specific category for a specific year along with the mean monthly sales. The mean monthly sales are displayed in two ways: as text and as a stripe line on the chart itself. The screen shot below shows the monthly sales for those products in the Produce category for 1997. Note that the average sales per month - $4,809.88 - is shown both as text on the page and as an orange line on the chart.

To create this chart I started by adding two DropDownList controls to the page named ddlCategory and ddlForYear. The ddlCategory DropDownList is bound to a SqlDataSource control that populates it with the categories in the Categories database table. The ddlForYear contains three ListItems - 1996, 1997, and 1998 - one for each year for which sales data exist. I also added a Label control (lblAverageSales) to display the mean sales per month as text.

Next, I added the Chart control to the page named chtCategorySales and bound it to a SqlDataSource control named dsCategorySales. The SqlDataSource control executes the following query, which returns each month with sales for products in a particular category for a particular year.

SELECT MONTH(o.OrderDate) AS Month, SUM(od.UnitPrice * od.Quantity) AS Total FROM Orders AS o    INNER JOIN [Order Details] AS od ON       o.OrderID = od.OrderID    INNER JOIN Products AS p ON       p.ProductID = od.ProductID WHERE (p.CategoryID = @CategoryID) AND (YEAR(o.OrderDate) = @ForYear) GROUP BY MONTH(o.OrderDate) ORDER BY Month

The Chart control contains a single line chart series named SalesByMonth, which has its XValueMember and YValueMembers properties set to the names of the columns returned from the database query (Month and Total, respectively). The Chart control's declarative markup also defines a chart area (MainChartArea) with some Y axis settings. Namely, I configured the Y axis to format its labels as currency values (with no decimal places) and to add an orange stripe line. A stripe line is a line (or band) that can be added to the X or Y axis and used to show a particular value or range of values. In this case, I use the stripe line to show the average sales per month. The position of the stripe line is based on its IntervalOffset property, which is programmatically set to the mean value (we'll explore this code momentarily).

<asp:Chart ID="chtCategorySales" runat="server" Width="550px"        Height="350px" DataSourceID="dsCategorySales">    <Series>       <asp:Series Name="SalesByMonth" ChartType="Line" BorderWidth="5"          ChartArea="MainChartArea" YValueMembers="Total" XValueMember="Month"></asp:Series>    </Series>        <ChartAreas>       <asp:ChartArea Name="MainChartArea" Area3DStyle-Enable3D="false">          <AxisY>             <LabelStyle Format="C0" />             <StripLines>                <asp:StripLine TextAlignment="Near" BorderDashStyle="Solid" BorderColor="Orange" BorderWidth="4" BackColor="Orange" />             </StripLines>          </AxisY>       </asp:ChartArea>    </ChartAreas> </asp:Chart>

The SqlDataSource plus the Chart's declarative markup is sufficient to display a chart of the gross sales per month for the selected category and year. In order to show the mean sales per month we need to write a few lines of code. Keep in mind that we cannot calculate the mean until the series is populated with data. When using a Chart that is bound to a data source control you know that the data has been plotted once the DataBound event is raised. Therefore, I've created an event handler for this event and placed the code to compute and display the mean there. (If you are adding the points programmatically rather than from a declarative data source control then you would add this code immediately after the code that plots your data. The forecasting demo we'll look at shortly provides such an example.)

Protected Sub chtCategorySales_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chtCategorySales.DataBound    'Determine the mean    Dim mean As Double = chtCategorySales.DataManipulator.Statistics.Mean("SalesByMonth")    'Display mean as text    lblAverageSales.Text = mean.ToString("C")    'Display stripe line    chtCategorySales.ChartAreas("MainChartArea").AxisY.StripLines(0).IntervalOffset = mean End Sub

Here we calculate the mean, display it in the lblAverageSales Label control, and assign it to the stripe line's IntervalOffset property. The net effect is that when the chart is plotted the mean sales per month is calculated and displayed both as text on the page and as a stripe line on the chart.

Using Forecasting Statistical Formulae

There are a variety of statistical formulae that can be used for forecasting. In brief, these formulae typically work by arriving at an equation whose line closely models the existing, known data points. This equation can then be extended to forecast future values. To apply a forecasting formula you must supply the following inputs:

• The forecasting parameters, which must be formatted into a comma-delimited string. There are four optional parameters:
  • Regression Type - determines the type of equation to fit against the known points. You can specify a a numeric value to indicate the polynomial regression, or you may use one of the following regression types: Linear, Exponential, Logarithmic, or Power.
  • Period - the number of time units in the future to forecast. For example, to forecast three months in the future for monthly sales you would use a value of 3 here.
  • Approximate Error - a Boolean value that indicates whether to output the approximation error.
  • Forecast Error - a Boolean value that indicates whether to output the forecasting error.
• The input series, namely the data for which the forecasts are being made.
• The output series. If you are outputting the approximate or forecasting errors then you will need to have a series for both the forecast and the errors.

To apply a forecasting formula use the code like the following:

ChartID.DataManipulator.FinancialFormula(FinancialFormula.Forecasting, parameters, inputSeries, outputSeries)

The demo available for download at the end of this article includes a page named Trendline.aspx, which uses an exponential regression to predict sales figures three months into the future. The following screen shot shows the sales figures for Dairy Products sales in 1997 and the forecast for the first quarter of 1998. Things are looking up!

Like the mean sales demo, to create the Trendline.aspx I started by adding and configuring two DropDownList controls to the page to capture the category and year used to power the report. I then added the Chart control to the page. Rather than use a SqlDataSource control to declaratively bind the data to the chart, I instead decided to bind the data to the chart programmatically. (Using a SqlDataSource here certainly would have worked just fine.) Before we look at the code, let's take a peak at the Chart control's declarative markup. Recall that in the previous example we added a stripe line to the Y axis to show the mean sales per month. We don't need a stripe line for this chart, but we do need to define two series - one for the sales per month (SalesByMonth) and another for the forecasting trend line (TrendLine).

<asp:Chart ID="chtCategorySales" runat="server" Width="550px" Height="350px">    <Series>       <asp:Series Name="SalesByMonth" ChartType="Line" BorderWidth="5"          ChartArea="MainChartArea"></asp:Series>       <asp:Series BorderWidth="4" Name="TrendLine" ChartType="Line" BorderColor="Orange"></asp:Series>    </Series>        ... </asp:Chart>

If you want to include the approximate error or forecasting error you'd need to include series for these (most likely Range series).

The sales per month data is actually added to the chart from code in the Page_Load event handler. Specifically, a connection is made to the database, an ad-hoc query is executed that gets the sales data, and the results are enumerated, with each record returned from the database added to the SalesByMonth series. After the data has been plotted, an Exponential regression is used to add the forecast data to the TrendLine series. An abbreviated version of the Page_Load event handler code follows:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load    Using myConnection As New SqlConnection       ... Get sales per month data from database for selected category / year          and add it to the chart point-by-point ...    End Using    'Add a trend line using an exponential regression    chtCategorySales.DataManipulator.FinancialFormula(FinancialFormula.Forecasting, _                                         "Exponential,3,false,false", _                                         chtCategorySales.Series("SalesByMonth"), _                                         chtCategorySales.Series("TrendLine")) End Sub

The code that actually queries the database and plots the points one at a time has been removed for brevity. Download the demo to see the full code; refer to Plotting Chart Data for detailed examples on adding chart data point-by-point.

The red code in the snippet above adds the forecasting trend line to the chart's TrendLine series, forecasting sales per month three months into the future.

Conclusion

In addition to merely displaying plotted data, the Microsoft Chart controls include functionality for analyzing that data using a variety of statistical formulae. This article explored how to use this functionality to compute the mean (average) value in a series and how to add a trend line to forecast future results. These two examples just scratch the surface of the statistical capabilities of the Chart control - there are many more formulae available, including functions to compute moving and weighted moving averages, ranges, statistical distributions, and more.

Happy Programming!

By Scott Mitchell

---

Attachments:

Download the code for this article

Further Reading

The Chart Controls Forecasting Formula