Using Microsoft's Chart Controls In An ASP.NET Application: Sorting and Filtering Chart DataBy Scott Mitchell
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
Pointscollection; 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
Pointscollection. 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 BYclause 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
Pointscollection 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
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.
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
DataManipulatorthat, as its name implies, can be used to manipulate the chart's data. To sort the chart's data you can call the
- 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
chtExpensesthat contains a series named "Expenses", we could sort the data by the Y values in ascending order using the following statement:
You can also sort a series more directly using the following syntax:
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
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
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
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.
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.
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
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
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:
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.
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.
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
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
Sortmethod, the Chart control's
DataManipulatorobject 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
FilterTopNmethod 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
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,
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.
DataBound event handler contains nothing more than a call to
FilterTopN. The only difference between the two method calls is that
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:
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.
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
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
Filter method and pass in an instance of the class.
The demo includes two custom filtering classes in the
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 operator number. For example, a format string of
10|<=would filter out all points whose Y value is less than or equal to 10.
CustomYValueNumberChartFilterclass filter out all values greater than 10 you would instantiate the class like so:
The code for the
CustomYValueOddEvenChartFilter class follows:
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,
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
passing in the appropriate filtering instructions into the constructor:
The screen shot below shows the chart filtered to show only those categories with 10 or fewer products.
By default, the Microsoft Chart control renders each and every one of the data points in its
Pointscollection 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
IDataPointFilterinterface. Download the demo to get your hands on the complete source code!