Excel-Style Filtering for the ASP.NET AJAX Grids

[Infragistics] Damyan Petev / Wednesday, May 30, 2012

A closer look at our range of grid controls across platforms can reveal a certain trend when it comes to filtering – the Windows Forms Grid, the WPF specific XamDataGrid and the cross-platform XamGrid – they all allow for a number of different filtering settings and one of the main stars in all is the so called Excel-Style Filtering. With the last release the ASP.NET AJAX Data Grids are also on route to getting such a feature, although it’s still a CTP. Closely following the functionality found in the Microsoft Office Excel, such filtering allows for users to select the condition from a checkbox list that is prepopulated with the distinct values for the column. You can say this feature is being added by popular demand and for the very same reason it is kept as close to Excel feel as possible – as I’ve explained before such UI seems more friendlier to those familiar with how Excel Filtering works. It might not only be more appealing to the those using your application, but it can also really shine when you have large amounts of repeating values in columns. Moreover, if you have an user that generally doesn’t have expectations for the contents in your grid (but also can’t see all the data records due to them being a considerable amount and/or paged), this feature will provide a sort of hint when presenting all the available values in its menu.

Of course, this filtering style is best explained with an example and visual material – coming up next!

The ASP.NET AJAX data grid with Excel-Style filtering Menu, showing just the Sales titles and a column filter applied to the CIty column

As expected this filtering doesn’t take a row on its own, but rather produce an easily recognizable icon in each column header, that will also indicate when a filter is active on that column. In the “Contact Title” column (the one with a few set roles repeating multiple times) it’s now extremely easy to narrow down the data to just the employees in Sales or just Owners.

Enabling the Excel Filter

And aside from making the end-users happy it is also very easy to get started with! Assuming you already have a WebDataGrid set up (check this page on the Documentation to get started with the WebDataGrid) all you need to do is add the Filtering behaviour to your grid. Note that you can do pretty much everything with the extensive menus you can find in the control properties and by clicking its smart tag. In the ‘Edit Grid Behaviors’ menu you will find Filtering and in its properties – the Filter Type property (also marked as CTP) that you can set to to Excel Style filter:

The WebDataGrid smart tag design menu showing the FIltering option and its type

To each column its own

You can disable the filter menu on per column basis, which can be done in the Column Settings collection in both the designer and in code.

To provide additional functionality you can add Column filters in addition to the filter menu. That means you can initially or at run time define filtering using all the well known type-specific conditions equals, contains and so on. Those can be accessed in the Filtering properties as seen above in the Column Filters collection. For a complete guide on how to add those in design or run-time you can read on the WebDataGrid Filtering page.

And here’s the snippet of the final code for Excel Filtering with some column settings and pre-defined additional filter for the ‘City’ column:

  1. <Behaviors>
  2.             <ig:Filtering FilterType="ExcelStyleFilter">
  3.                 <ColumnSettings>
  4.                     <ig:ColumnFilteringSetting ColumnKey="Phone" Enabled="False" />
  5.                     <ig:ColumnFilteringSetting ColumnKey="CustomerID" Enabled="False" />
  6.                 </ColumnSettings>
  7.                 <ColumnFilters>
  8.                     <ig:ColumnFilter ColumnKey="City">
  9.                         <ConditionWrapper>
  10.                             <ig:RuleTextNode Rule="Contains" Value="e" />
  11.                         </ConditionWrapper>
  12.                     </ig:ColumnFilter>
  13.                 </ColumnFilters>
  14.             </ig:Filtering>
  15.         </Behaviors>

Add hierarchy!

At the start I explicitly mentioned ‘ASP.NET Grids’ and that was to indicate that not just the flat data grid, but also the hierarchical one now offers this feature. Again using this feature is as easy as picking an option in the menu (and again here’s a Getting Started with WebHierarchicalDataGrid topic).

The ASP.NET AJAX hierarchical Data Grid with Excel Style Filtering and inheritance.

Once more you can set that up in the designer smart tag menus and in code, only this time you not only can set rules and availability for each column but also allow the feature to be inherited by the child layouts (called Bands). Each band also exposes collections of Column settings and filters and you can set if inheritance for the behavior should be enabled. The illustration above is a very simple implementation for enabling the Excel filtering and letting it propagate down to child bands and here’s a snippet for that:

  1. <Behaviors>
  2.             <ig:Filtering EnableInheritance="true" FilterType="ExcelStyleFilter">
  3.             </ig:Filtering>
  4.         </Behaviors>

Simple as that!

Conclusion

The new Excel style filter, while still a CTP feature, is set to deliver more user friendly and familiar to many filtering interface. It is the popularity and comfort of Excel that calls for such functionality and it’s not just the feel, but also the benefits of prompting the user of the the possibly interesting values in a glance and providing a two-click filtering for those cases with a few dominant ones. It will work well for both the flat data WebDataGrid and the WebHierarchicalDataGrid. It will allow the user to pick just the data he needs and also allow for the developer to aid in that by adding additional column filters!

Try the demo project or visit the ASP.NET Online Samples and as with all the Community Previews – feedback is more than welcomed – tell us what you think and what you want to see it become. As always, you can follow us on Twitter @DamyanPetev and @Infragistics and stay in touch on Facebook!