Using Microsoft's Chart Controls In An ASP.NET Application: Sorting and Filtering Chart Data
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.
Introduction
The Microsoft Chart controls make it easy to take data - such as sales numbers, website traffic statistics, and so on - and turn it into a chart, which can be saved to
an image file or displayed from a web page. In Plotting Chart Data we examined a myriad of ways to turn
data into a chart, including: plotting the chart data point by point; binding data to the Chart's Points collection; programmatically binding data structured
data to the chart; and declaratively binding data using one of ASP.NET's data source controls, such as the SqlDataSource or ObjectDataSource.
Oftentimes, web pages that display charts include user interface elements that let the user filter or sort the plotted data. For example, when viewing a chart of expenses,
the user may want to only show expenses between two dates, or may want to sort the expenses by category. One way to provide such functionality is to sort or filter the
data before binding it to the chart. Alternatively, you can bind the original data to the chart and then instruct the Chart control to sort the data, or to apply a
filter. This article shows how to use these sorting and filtering capabilities. Read on to learn more!
A Few Important Words About Sorting and Filtering Chart Data...
By default, the Chart control does not sort its data points; rather, it displays them in the order in which they were added to the Points collection. Of course,
you can take steps to sort the data prior to binding it to the chart. For example, if you are binding the chart to the results of a database query you can include an
ORDER BY clause in the query to have the database sort the results before returning them to your ASP.NET application. Another option is to have the Chart
control order the data points within its Points collection prior to rendering the chart.
Similarly, by default, the Chart plots all of the points in the Points collection. To prevent certain points from appearing in the rendered chart you can either
filter out the undesired points before binding the data to the chart or you can bind all of the points to the chart and then instruct the Chart control to filter out certain
ones.
When sorting or filtering the Chart control's data points it is important that the code to perform the sorting or filtering occurs after the Chart control has been populated with
its data points, but before the chart has been rendered. If you are programmatically binding the data points to the chart - either by adding the data point by point or by
using the Chart control's DataBindTable method - then you should put the sorting or filtering code immediately after the code that binds the data to the chart.
If you are declaratively binding data to the chart using a data source control then you will need to create an event handler for the Chart control's DataBound event and
put the sorting and filtering code there. (The sorting and filtering demos available for download at the end of this article bind data to the Chart control using a SqlDataSource
control; consequently, you'll find the sorting and filtering code in the Chart's DataBound event handler.)
Finally, throughout this article I use terms like "sorting the chart" or "filtering the chart." Actually, the chart itself cannot be sorted or filtered, as it does not contain
data points. As discussed in Getting Started, a chart contains one or more series. A series is composed
of a collection of data points. Therefore, you can only sort or filter a series in the chart, not the chart itself. While the Chart control has methods to sort and filter
data, you have to specify the series in the chart to apply the sorting or filtering logic.
Simple Sorting
It's easy to sort the data points within a chart by the X values, Y values, or axis labels, in either ascending or descending order - it's just one line of code. The Chart
control includes a property named DataManipulator that, as its name implies, can be used to manipulate the chart's data. To sort the chart's
data you can call the DataManipulator object's Sort method, specifying:
The order to sort the data points (ascending or descending),
What aspect of the series to sort by - the X values, the Y values, the axis labels, etc., and
The series whose data points to sort
For example, given a chart named chtExpenses that contains a series named "Expenses", we could sort the data by the Y values in ascending order using the
following statement:
The two statements above are semantically equivalent. Furthermore, note the second parameter to both methods. The "Y" string indicates that the series should be sorted by
its Y values. Other legal values for this second parameter include: "X", "AxisLabel", "Y2", "Y3", and so on.
The demo available for download at the end of this article includes a demo named Sort.aspx that displays a bar chart summarizing how many products are associated
with each of the categories is the Northwind database. The data bound to the chart comes from a SqlDataSource that specifies the following SELECT statement:
SELECT CategoryName, COUNT(*) as ProductCount
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
GROUP BY CategoryName
This query returns one record for each category. Each record contains two columns: the category name and the number of products associated with said category.
In addition to the Chart control, the Sort.aspx demo includes a DropDownList control from which the user can specify whether they want to see the chart data
sorted alphabetically by its axis labels (the category names) or sorted by the Y values (the number of products in each category). The declarative markup for this DropDownList
control follows:
The Value of the DropDownList's items corresponds to the what aspect of the series to sort by. If the user wants to sort the results by the category name,
then we will need to pass in the string "AxisLabel" into the Sort method. If they want to sort by the number of products then we'll need to pass the string
"Y" into the Sort method.
Another DropDownList on the page lets the visitor specify whether to sort the data in ascending or descending order.
The actual sorting logic happens in the Chart control's DataBound method. The Sort method is called, passing in the appropriate sort direction and sort by
parameters based on the user's DropDownList selections.
Protected Sub chtCategoriesProductCount_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chtCategoriesProductCount.DataBound
Dim sortDirection As PointSortOrder = PointSortOrder.Ascending
If ddlSortDirection.SelectedValue = "Descending" Then
sortDirection = PointSortOrder.Descending
End If
chtCategoriesProductCount.Series("Categories").Sort(sortDirection, ddlSortBy.SelectedValue)
End Sub
That's all there is to it! The following screen shot shows the page when first loaded. Note that the data is sorted by category name in ascending order (alphabetically) -
Beverages, Condiments, Confections, and so forth.
The next screen shot shows the chart when sorted by the number of products in descending order.
Custom Sorting
The code we just examined sorts the data by a specific aspect of the series - the X values or the Y values or the Axis labels - but what if we need a more intricate sorting
logic? Perhaps we'd like to be able to sort the categories listed by gross sales, meaning that the bar chart would start out listing the category with the least sales all
the way up to the category with the most sales, regardless of the category name or the number of products associated with the category. Such an ordering would, perhaps, reveal
a correlation between the number of products a category contains and its sales.
Such custom sorting logic is also needed if the with which "tied" items are displayed matters. For example, in the screen shot immediately above, there are three categories
with exactly 12 products: Seafood, Beverages, and Condiments. It would be nice to have such ties ordered alphabetically by the category name, so that the above chart listed
Beverages first, then Condiments, and finally Seafood.
If you have complex sorting logic or just need to specify how ties should be broken, you can create a custom sorting class that implements the Generics-based IComparer
interface. In a nutshell, this class takes in two DataPoint objects, A and B, and indicates if A comes before B, if A comes after B, or if A and B are the same.
To sort using this custom sorting logic class you pass an instance of the class into the Sort method.
The demo includes a class in the App_Code folder named CustomYValueChartComparer that sorts DataPoints by their Y values, breaking ties
by sorting against the AxisLabel value in alphabetical order. The complete code for this class follows:
Imports System.Web.UI.DataVisualization.Charting
Public Class CustomYValueChartComparer
Implements IComparer(Of DataPoint)
Private _SortDirection As PointSortOrder
Public Sub New(ByVal sortDirection As PointSortOrder)
_SortDirection = sortDirection
End Sub
Public Function Compare(ByVal pointA As DataPoint, ByVal pointB As DataPoint) As Integer Implements IComparer(Of System.Web.UI.DataVisualization.Charting.DataPoint).Compare
'Compare Y values
Dim result As Integer
If _SortDirection = PointSortOrder.Ascending Then
result = pointA.YValues(0).CompareTo(pointB.YValues(0))
Else
result = pointB.YValues(0).CompareTo(pointA.YValues(0))
End If
'If Y values are equal, break tie via AxisLabel values
If result = 0 Then
result = pointA.AxisLabel.CompareTo(pointB.AxisLabel)
End If
Return result
End Function
End Class
The first thing to note is that this class implements the IComparer(Of DataPoint) (or IComparer<DataPoint>, in C# parlance). Because this
class implements this interface it must provide a Compare method that takes in two DataPoint objects and returns an integer value indicating
how the two DataPoints should be ordered relative to one another.
The CustomYValueChartComparer class contains a private member variable named _SortDirection that indicates whether to sort the data in ascending
order or descending order. The value for this member variable is specified through the constructor.
The Compare method compares the Y value in each DataPoint using the CompareTo method. The CompareTo method returns a value of
zero if both values are equivalent. In that case, we break the tie by comparing the AxisLabel values of the two DataPoints.
To use this custom sorting logic, simply pass an instance of the class into the Sort method, like so:
The following screen shot shows the categories ordered by their number of products in descending order, but this time the three categories with 12 products each are sorted
alphabetically.
Top/Bottom N Filtering
In addition to its Sort method, the Chart control's DataManipulator object also includes methods for filtering. One such method is FilterTopN,
which filters out all but the top N (or bottom N) items from the chart. The FilterTopN method accepts the following input parameters:
N, the number of items to show,
The input and output series,
The series aspect to filter - "X", "Y", "AxisLabels", etc., and
Whether to take the filter out all but the top N data points (the default) or the bottom N data points
Note: The input series is the series whose data points are filtered, whereas the output series is the series where these filtered data points
are displayed. In short, you can have a Chart control with two series in two different chart areas with one series showing all of the points and the second series showing
a filtered subset of the first series' points. In this arrangement, the first series would be the input series and the second series would be the output series. The demos
I've created use the same series as both the input and output.
For example, given a bar chart named chtExpenses and a series named "Expenses", the following statement would display only the 5 most expensive data points:
The demo available for download at the end of this article includes a demo called FilterTopN.aspx, which includes two bar chart controls,
chtCategoriesProductCountTopN and chtCategoriesProductCountBottomN. Both bar charts
are bound to a SqlDataSource control that returns how many products are associated with each category. There are also two DropDownList controls that list the numbers 1, 2, 3, 4,
and 5. The chtCategoriesProductCountTopN bar chart displays the N categories with the most products, where N is the number selected from the
DropDownList (1, 2, 3, ...). The chtCategoriesProductCountBottomN bar chart shows the N categories with the least number of products.
The code for this ASP.NET page is quite simple. There are two event handlers in the code-behind class: one for each of the Chart's DataBound event handlers.
Each DataBound event handler contains nothing more than a call to FilterTopN. The only difference between the two method calls is that
when calling FilterTopN for chtCategoriesProductCountTopN a value of True is passed into the fourth and final parameter. This value
indicates that only the top N items should be displayed. The chtCategoriesProductCountBottomN bar chart's DataBound event handler calls
FilterTopN passing in False, indicating that only the bottom N items should be displayed.
The code for these two event handlers follows:
Protected Sub chtCategoriesProductCountTopN_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chtCategoriesProductCountTopN.DataBound
Dim filterCount As Integer = Convert.ToInt32(ddlLargest.SelectedValue)
chtCategoriesProductCountTopN.DataManipulator.FilterTopN(filterCount, "Categories", "Categories", "Y", True)
End Sub
Protected Sub chtCategoriesProductCountBottomN_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chtCategoriesProductCountBottomN.DataBound
Dim filterCount As Integer = Convert.ToInt32(ddlSmallest.SelectedValue)
chtCategoriesProductCountBottomN.DataManipulator.FilterTopN(filterCount, "Categories", "Categories", "Y", False)
End Sub
The screen shot below shows the chtCategoriesProductCountTopN bar chart when displaying the top four categories. Download and run the demo to see the
chtCategoriesProductCountBottomN chart in action.
Custom Filtering
The FilterTopN method is useful for filtering out all but the top (or bottom) data point values in the Y values (or X values or axis label values), but
what if you need to perform more intricate filtering? Perhaps you want to filter out those categories with fewer than 10 products. To implement such custom filtering you
can create a class that implements the IDataPointFilter interface. Such a class will need to provide a method named FilterDataPoint, which takes
in a DataPoint object (among other inputs) and returns a Boolean value - True to filter the point out of the chart and False to leave the point in the chart.
To use such a custom class, you call the the DataManipulator object's Filter method and pass in an instance of the class.
The demo includes two custom filtering classes in the App_Code folder:
CustomYValueOddEvenChartFilter, which filters out either odd or even Y values, and
CustomYValueNumberChartFilter, which allows the developer to specify a format string with the pattern number|operator, which
filters all Y values where the Y value operatornumber. For example, a format string of 10|<= would filter out all points whose Y value is
less than or equal to 10.
The constructor for each of these classes accepts a single input parameter that specifies how the filtering should proceed. For example, to have the
CustomYValueNumberChartFilter class filter out all values greater than 10 you would instantiate the class like so:
Dim myFilter As New CustomYValueNumberChartFilter("10|>")
The code for the CustomYValueOddEvenChartFilter class follows:
Imports System.Web.UI.DataVisualization.Charting
Public Class CustomYValueOddEvenChartFilter
Implements IDataPointFilter
Public Enum NumberFilter
Odd
Even
End Enum
Private _NumberFilter As NumberFilter
Public Sub New(ByVal filter As NumberFilter)
_NumberFilter = filter
End Sub
Public Function FilterDataPoint(ByVal point As System.Web.UI.DataVisualization.Charting.DataPoint, ByVal series As System.Web.UI.DataVisualization.Charting.Series, ByVal pointIndex As Integer) As Boolean Implements System.Web.UI.DataVisualization.Charting.IDataPointFilter.FilterDataPoint
If _NumberFilter = NumberFilter.Even Then
Return Convert.ToInt32(point.YValues(0)) Mod 2 = 0
ElseIf _NumberFilter = NumberFilter.Odd Then
Return Convert.ToInt32(point.YValues(0)) Mod 2 = 1
Else
Return True
End If
End Function
End Class
The FilterDataPoint method is where the decision to include a specific point (or not) is made. Recall that if this method returns True then the data point is
not included in the chart. The code for this method in the CustomYValueOddEvenChartFilter class determines whether a data point's Y value is odd or even
by converting the data point to an integer and then computing the remainder when dividing the value by 2. (X Mod Y computes the remainder of
X / Y. The Mod operator in C# is the percent sign (%).) If we want to filter out even numbers then we return True
if the data point value Mod 2 equals 0, False otherwise. Conversely, if we want to filter out odd numbers then we return True when the data point value Mod 2 equals 1,
False otherwise.
The CustomFilter.aspx page demonstrates using these two custom filtering classes. The page contains - you guessed it - a bar chart showing how many products
are in each category in the Northwind database. The page also includes a DropDownList with options to show all categories, to only show categories with an even number of
products, to only show categories with an odd number of products, to only show categories with more than 10 products, and so on.
The Chart control's DataBound event handler inspects the value of the DropDownList and calls the DataManipulator object's Filter method,
passing in the appropriate filtering instructions into the constructor:
Protected Sub chtCategoriesProductCount_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chtCategoriesProductCount.DataBound
If ddlFilterCriteria.SelectedValue = "Odd" Then
chtCategoriesProductCount.DataManipulator.Filter(New CustomYValueOddEvenChartFilter(CustomYValueOddEvenChartFilter.NumberFilter.Odd), "Categories", "Categories")
ElseIf ddlFilterCriteria.SelectedValue = "Even" Then
chtCategoriesProductCount.DataManipulator.Filter(New CustomYValueOddEvenChartFilter(CustomYValueOddEvenChartFilter.NumberFilter.Even), "Categories", "Categories")
ElseIf ddlFilterCriteria.SelectedValue <> "All" Then
chtCategoriesProductCount.DataManipulator.Filter(New CustomYValueNumberChartFilter(ddlFilterCriteria.SelectedValue), "Categories", "Categories")
End If
End Sub
The screen shot below shows the chart filtered to show only those categories with 10 or fewer products.
Conclusion
By default, the Microsoft Chart control renders each and every one of the data points in its Points collection in the order with which they were added. However,
it is possible to instruct the Chart control to order its data points or to filter out certain data points. In this article we saw how to sort the Y values and axis labels in
a bar chart, as well as how to sort based on custom logic through the use of a class that implements IComparer. We also looked at filtering out all but the top
(or bottom) N data points, as well as how to apply custom filtering logic using by creating a class that implements the IDataPointFilter interface.
Download the demo to get your hands on the complete source code!
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.
