Excel-like filters for Kendo UI Grid

In my previous post I showed how to set up the grid so that all data operations occur on the server. In this post, I’ll build on that a little further to enable filtering like Excel does.

Here is the default method of filtering:

And this is what I want:

Excuse the blurs, that is ‘real’ testdata

First we need to set up the little screens that will replace the default filtering. I used a div with some Razor to create a list of checkboxes:

<div id="customers" class="filterDropdown">
        @foreach (var c in ViewBag.CustomerFilterSource)
        {
            var customer = "Customers_" + c;

            <label for="@customer">@c</label>
            <br />
        }

    </div>

The data comes from a collection in the ViewBag that is loaded when the page is served.

Next thing is getting rid of the default filter window. It took me a while to figure this one out but it turned out to be really simple. I used jQuery to locate all the little filter buttons in the header and remove their click events and adding my own:

$("#reportGrid").find(".k-grid-filter").unbind("click").click(function () {
            var parent = $(this).parent();
            var filterList;
            switch (parent.attr("data-field")) {
                case "CustomerName":
                    filterList = $("#customers");
                    break;
                case "CertificateNumber":
                    filterList = $("#certificates");
                    break;
                case "ReportDate":
                    filterList = $("#reportdate");
                    break;
                case "ReportType":
                    filterList = $("#reporttypes");
                    break;
            }

            var offset = $(this).offset();
            filterList.css('top', offset.top + $(this).height()).css('left', offset.left + $(this).width()).slideDown();
            return false;
        });

The filter buttons are easy to locate as they have a specific CSS class applied to them. In the switch-case I determine which popup I want to show when the button is clicked. In the last three lines I place the popup in the same spot as the filter button so that it rolls out from where the user clicked.

Some code for the Apply buttons: (just showing one here)

$("#reportdate").hide();
        $("#applyReportDateFilter").click(function () {
            setDsFilter(generateDsFilter());
            $("#reportdate").slideUp();
        })

The popup is hidden since we only want to see it when one of the filterbuttons is clicked. The click event takes care of resetting the datasource filters and hiding the popup.

Now we get to the fun part! To keep the existing filter functionality of the grid’s datasource intact, we need to generate the filters ourselves. That way, the controller code from my previous post can be left unchanged to pick up the filters that the grid posts.

The filters on the datasource are contained in a single object, this object has a property called ‘logic’ which specifies if this is an AND or an OR query. The other is an array of objects named ‘filters’ that hold the columns and their filter values. So here is what needs to happen: Gather all the checkboxes that are checked, translate them into a valid JSON filter object and apply the new filter to the datasource.

function generateDsFilter() {

        var checkboxContainer = $("#checkboxFilters");
        var flt = { logic: "and", filters: [] };

        checkboxContainer.find("input:checked").each(function () {
            var name = $(this).attr("name");
            var val = $(this).attr("value");

            flt.filters.push({ field: name, operator: "eq", value: val });
        });

        return flt;
    }

The checkbox container you see holds all the windows that contain checkboxes. We need all of them so I put the windows together in a div for easy selecting with jQuery.

‘flt’ is the object that we need to fill with the filters. The logic is set to AND because we want an exclusive filter. We only want to see stuff that is checked. In a loop we pass all the checked checkboxes using jQuery and add them to the filters property of the main filter object.

To apply the filters to the datasource I added this function:

function setDsFilter(customFilter) {
        var grid = $("#reportGrid").data("kendoGrid").dataSource.filter(customFilter);
    }

I put this in a separate function because there is one thing we still need to fix. The datasource has no initial filter. With the filter being empty, no data is loaded.

To make sure the grid has an initial filter we need to set the ‘filter’ property on the datasource that is part of the grid code:

dataSource: {
                type: "json",
                serverPaging: true,
                serverSorting: true,
                serverFiltering: true,
                allowUnsort: true,
                filter: generateDsFilter(),
                pageSize: 25,
                transport: {
                    read: {
                        url: "Export/PagedData",
                        type: "POST",
                        dataType: "json",
                        contentType: "application/json; charset=utf-8"
                    },
                    parameterMap: function (options) {
                        return JSON.stringify(options);
                    }
                },
                schema: { data: "Items", total: "TotalItemCount" }
            }

This ensures that when the grid is first created, all the filters are applied BEFORE the data is loaded, in this case, all the data is loaded because all the checkboxes are checked.

When clicking the Apply button in one of the filter windows (see the code somewhere above) we regenerate the filters and apply them to the datasource. The grid will then call the Controller Action and post the filters to the server for you to handle.