Custom filtering using a toolbar button based on child grid rows in Kendo Grid for .NET core

[Ashish]

Hi,

I'd like to implement custom filtering based on rows of child grid.

The attached image shows the layout of my grid.

 

My questions are as follows:

Q1. How to filter the 'company' rows based on the 'customers' who sold more than 100 items?
For eg: After I click 'Customers Selling > 100 Items 4 months' button, I only should show 'Company1' row with 'Customer12' row in its child grid.
Q2. If I want to clear the filter, I should be able to do so as well. (How do I do that in a button? Maybe show active button when used? I'd like your opinion on that.)
Q3. To fetch these data, the server needs to do some expensive calculations, so how can I reuse the dataSource when the customer navigates through different pages instead of doing full fetch from controller while navigating to different pages?

My code looks like this:

The data models:

public class CompanyData
{
    public string CompanyId { get; set; }
    public int CustomersCount { get; set; }
    public List<CompanyCustomer> Customers { get; set; }
}

public class CompanyCustomer
{
    public string CustomerId { get; set; }
    public string CustomerName { get; set; }
    public int TotalSoldItems { get; set; }
}

 

The grid containing the child grid as well:

@(Html.Kendo()
        .Grid<ProjectName.Models.CompanyData>()
        .Name("CompanyDataSummary")
        .Columns(columns =>
        {
            columns.Bound(e => e.CompanyId).Title("Company Name").Width(60);
            columns.Bound(e => e.CustomersCount).Title("Customers Count").Width(50);
        })
        .Sortable()
        .Pageable()
        .Scrollable()
        .Filterable()
        .ClientDetailTemplateId("CustomerDetails")
        .HtmlAttributes(new { style = "height:450px;" })
        .AutoBind(false) // Don't load the data yet because I'll need to supply parameters for the fetch
        .ToolBar(toolbar => toolbar.ClientTemplateId("CustomerDetailsToolBarTemplate"))
        .DataSource(dataSource => dataSource
                    .Ajax()
                    .ServerOperation(true)
                    .PageSize(10)
                    .Model(model =>
                    {
                        model.Id("CompanyId");
                        model.Field("CustomerCount", typeof(int));
                    })
                    .Read(read => read.Action("GetCompanyDataAsync", "Reporting").Data("passArguments"))
        )
        .Events(events => events.DataBound("dataBound").DetailExpand("onExpand"))
)

<script id="CustomerDetails" type="text/kendo-tmpl">
    @(Html.Kendo()
                .Grid<ProjectName.Models.CompanyCustomer>()
                .Name("CustomerDetails_#=CompanyId#")
                .Columns(columns =>
                {
                    columns.Bound(o => o.CustomerName).Title("Customer Id").Width(60);
                    columns.Bound(o => o.TotalSoldItems).Title("Total Sold Items").Width(60);
                })
                .DataSource(dataSource => dataSource
                            .Ajax()
                            .PageSize(10)
                            .Model(model =>
                            {
                                model.Id("CustomerId");
                                model.Field("CustomerName", typeof(string));
                                model.Field("TotalSoldItems", typeof(int));
                            })
                            .ServerOperation(false)
                )
                .AutoBind(false)
                .Pageable()
                .Sortable()
                .ToClientTemplate()
        )
</script>

 

The toolbar template:

<script id="CustomerDetailsToolBarTemplate" type="text/kendo-tmpl">
    <div class="inlineBtn">
        @(Html.Kendo().Button()
              .Name("allYTDCustomers")
              .HtmlAttributes(new { type = "button", @class = "k-button" })
              .Content("All Customers YTD")
              .Events(e => e.Click("allYTDCustomersFetch"))
              .ToClientTemplate()
        )
    </div>
    <div class="inlineBtn">
        @(Html.Kendo().Button()
              .Name("CustomersGT100ItemsYTD")
              .HtmlAttributes(new { type = "button", @class = "k-button" })
              .Content("Customer selling > 100 items YTD")
              .Events(e=>e.Click("CustomersGT100ItemsYTDFetch"))
              .ToClientTemplate()
        )
    </div>
    <div class="inlineBtn">
        @(Html.Kendo().Button()
              .Name("allCustomersLast4Mths")
              .HtmlAttributes(new { type = "button", @class = "k-button" })
              .Content("Customers selling > 100 items last 4 months")
              .Events(e => e.Click("allCustomersLast4MthsFetch"))
              .ToClientTemplate()
        )
    </div>
</script>

 

The script that loads the child grid and the handler for toolbar button:

<script>
    function onExpand(e) {
        var companyId = e.sender.dataItem(e.masterRow).CompanyId;
        var customerData = e.sender.dataItem(e.masterRow).Customers;
        //Initialize the  child grid as well
        var childGridName = "#" + "CustomerDetails_" + companyId;

        var childGrid = $(childGridName).data("kendoGrid");
        if (childGrid !== undefined) {
            childGrid.dataSource.data(customerData);
        }
    }

    //Example function:
    function CustomersGT100ItemsYTDFetch() {
        // How to filter the company rows based on the customers who sold more than 100 items?
        // For eg: After I click that button, I only should show Company1 row with Customer12 in its child grid.
    }
</script>

 

The controller action method:

public async Task<ActionResult> GetCompanyDataAsync([DataSourceRequest] DataSourceRequest request, DateTime start, DateTime end)
{
    IEnumerable<CompanyData> companySummary = await _reporting.GetCompanyReportInformationAsync(start, end);
    return Json(companySummary.ToDataSourceResult(request));
}

 

 

 

[Nikolay]

Hello Ashish,

Thank you for sharing the Grid declaration.

Straight to the questions:

1. I suggest in the button click handler to filter child grids dataSource using the filter() method. For example:

function filter() {
        var pageSize = $("#grid").data("kendoGrid").dataSource.view().length; // get current page size length
        for (var i = 1; i <= pageSize; i++) {
            var gridDS = $("#CustomerDetails_" + i).data("kendoGrid").dataSource;
            gridDS.filter({ field: "TotalSoldItems", operator: "gte", value: 100 })
        }
    }

2. The applied filter can be cleared following the above approach using the filter() method:

gridDS.filter({}) //clears the filter

I can suggest implementing a Kendo UI Checkbox instead of using a button so the user knows when the filter is applied.

Another alternative would be on clicking the button to set different background color and/or opacity and reverting back to the original styling upon clearing the filter.

3. The Grid is designed when server operations are enabled to fetch the data every time upon initializing itself.

I can suggest saving the Grid data source locally with the first request and reusing it when navigating to different pages. These requests can be prevented in the RequestStart event handler with Event.preventDefault().

Let me know if you have any questions.

Regards,
Nikolay
Progress Telerik

[Ashish]

Hi Nikolay,

Thank you for the response. I have more follow up questions to your answers.

1. I can see that you're trying to apply a filter to all the child grids that live inside the parent rows in the first page of the grid.

How do we handle a case where let's say the page size of grid is 10, there are 20 items in the whole grid, and after applying this filter there will only be 10 rows left, shouldn't those filtered 10 rows be shown on the first page (meaning shouldn't we get rows that were originally on 2nd page to show on the first page of filtered grid)? How do we handle that situation? I see that you're only applying filtering to grids on a unfiltered page by page basis. Can you please show an example?

Before we even get to that, the immediate problem here is that, until the parent rows are expanded once, the child grid inside them are not even rendered. So those child grid references will say 'undefined'.

I also want the filter applied in those child grids to affect the parent rows. For eg: if we reference the image that I have attached in my original post. After I click that filter button/ checkbox, I should only see:

Company Name       Customers Count

Company1                1 (Notice I need to update this count as well. Because Customer11 child grid row is filtered out)

       Customer Id                        Total Sold Items

       Customer12                        120

Also notice that Company2 (which is a parent row) is filtered out altogether.

Can you please show an example to accomplish this?

 

3.Ok, I can create an event callback for 'RequestStart' and call 'event.preventDefault' inside there. But how do I know that this 'RequestStart' is firing from my mere page navigation and not a fresh request where I'm requesting new data according to my time filters (by giving new start an end date)?

And how do I save this locally to accomplish all this. Can you please give me an example for this as well?

Thank you so much for you time!

[Nikolay]

Hi Ashish,

Upon soring the child grids the main grid does not change its page size. If the initial page size is 10 after the spring is applied to the children the page size will remain 10, even if some of the child grids return no data after the filtering.

The situation with the undefined error can be overcome with a condition:

var pageSize = $("#grid").data("kendoGrid").dataSource.view().length; // get current page size length
        for (var i = 1; i <= pageSize; i++) {
            var grid = $("#grid" + i).data("kendoGrid");
            if (grid) {
                grid.dataSource.filter({ field: "Id", operator: "eq", value: 0 })
            } 
        }

Additionally, for the requirement you have, I think better suited will be a single Grid grouped by Company where ClientGroupHeaderTemplate can be used with aggregates. Please refer to the following demo for an example implementation:

• https://demos.telerik.com/aspnet-core/grid/aggregates

Let me now if this helps.

Regards,
Nikolay
Progress Telerik