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.
|
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
chtExpenses that contains a series named "Expenses", we could sort the data by the Y values in ascending order using the
following statement:
chtExpenses.DataManipulator.Sort(PointSortOrder.Ascending, "Y", "Expenses")
|
You can also sort a series more directly using the following syntax:
chtExpenses.Series("Expenses").Sort(PointSortOrder.Ascending, "Y")
|
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
|
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:
<asp:DropDownList ID="ddlSortBy" runat="server" AutoPostBack="true">
|
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
|
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
|
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:
chtCategoriesProductCount.Series("Categories").Sort(New CustomYValueChartComparer(sortDirection))
|
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
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:
chtExpenses.DataManipulator.FilterTopN(5, "Expenses", "Expenses", "Y", True)
|
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
|
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, andCustomYValueNumberChartFilter, which allows the developer to specify a format string with the patternnumber|operator, which filters all Y values where the Y value operator number. For example, a format string of10|<=would filter out all points whose Y value is less than or equal to 10.
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
|
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
|
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!
Happy Programming!
Attachments:
| 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.
|
