When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, September 23, 2009

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.
  • 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 - 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!

    - continued -

    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:

    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
    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:

    <asp:DropDownList ID="ddlSortBy" runat="server" AutoPostBack="true">
       <asp:ListItem Value="AxisLabel">Category Name</asp:ListItem>
       <asp:ListItem Value="Y">Number of Products</asp:ListItem>
    </asp:DropDownList>

    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 chart data is sorted by the axis label values in alphabetical order.

    The next screen shot shows the chart when sorted by the number of products in descending order.

    The chart data is sorted by its Y values 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:

    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.

    The chart data is sorted by its Y values in descending order, with ties broken by sorting the axis label values.

    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:

    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
       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.

    The chart shows the four categories with the most products.

    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 operator number. 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.

    The chart is filtered to show only those categories with 10 or less 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!

  • By Scott Mitchell


    Attachments:

  • Download the code for this article
  • 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! )



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article