Excel-like Filtering using Telerik UI for ASP.NET AJAX Grid

Have you ever filtered data in an Excel workbook? I bet the answer is yes. “Who doesn’t use Excel, dah”—I guess that would have been your reaction. If you have ever done a data filter in an Excel workbook you are familiar with the filter dialog that you use. Let me refresh your memory for a second. Here is a screenshot of the filter dialog that comes up in Excel:

Figure 1 MS Excel Filter menu

As you can see, the filter dialog provides you Sort Ascending, Sort Descending, Filters, Search Box and a Multi-select list with unique values available in the column. You can perform any of the listed actions on this column. Wouldn’t it be great if we had the same capability in our web applications?

In this post, we will talk about one of the new feature we have added to our ASP.NET AJAX Grid—namely “Excel-like Filtering.”

About Excel-like Filtering

With the Q3 2015 release of our Telerik® UI for ASP.NET AJAX, this Excel-like filtering feature was introduced. If you are eager to have a look at the feature first, jump right into our Excel-like filtering demo. Here is a screenshot of how the filter dialog looks in ASP.NET AJAX Grid:

Figure 2 RadGrid Excel-like Filter menu

The functionalities provided by the filter dialog are:

1. Sorting
2. Grouping
3. Column(s) selection
4. Search Box
5. Multi-select ListBox
6. Filter conditions

When the Excel-like filter is enabled on the Grid, an end user can perform any/all of the functionalities on any column in the grid. Way back in Q3 2013 we introduced a functionality called Excel-like Multi-select filter for the Grid. But at that time all we had was just the list box, and you could select the values you want to filter on the column. We have enhanced that feature to include other actions that you see in Excel filter dialog. I would say it’s a complete feature now.

Enabling Excel-like Filter on a Grid

Now that we know what this feature is all about, let’s see how to enable it on an ASP.NET AJAX Grid. Our RadGrid, apart from the default filtering, supports a filtering based on multiple selected values from a list, i.e. a Multi-select ListBox. The way you do this is by using a property called FilterType on the RadGrid and setting its value to CheckList. For the new Excel-like filtering a new FilterType has been exposed now. The value is called HeaderContext. Once the FilterType has been set to HeaderContext, you will then need to turn on the header context menu by using the property EnableHeaderContextMenu. It’s a Boolean so you can set it to True/False. Let’s see some code now:

Notice that the FilterType property is set on the RadGrid and EnableHeaderContextMenu is set on the MasterTableView. You can also set EnableHeaderContextMenu on the RadGrid level. In that case it will be global, i.e. all MaterTableView’s get this property set on them internally. So if you have more than one master table view and you want to enable Excel-like filtering on all of them, you can just set the EnableHeaderContextMenu property at the RadGrid level instead of each table view level.

Providing Data for Multi-select ListBox

As mentioned earlier, the Excel-like filter dialog provides you an option of a Multi-select list box. The Multi-select list box will display the unique available values for that column. You will need to provide a data source for the listbox. In the code snippet shown above, I am listening to the OnFilterCheckListItemsRequested event and provide a handler for that. Inside the handler I fetch the appropriate data and provide that to list box as data source. Here is the event handler code:

GetDataTable is a helper method that I have which just gets me distinct values from the selected column.

Summary

The Excel-like filtering is a cool new functionality in RadGrid that you and your end users can benefit from Q3 2015 onwards. It takes very minimal setup to enable that feature in your applications. If you want to provide an Excel-like filtering experience in your web applications this new feature is definitely worth trying. We look forward to hearing your feedback on it. Do let us know your thoughts in the comments section below.