Displaying the count of distinct values in Grid column footer template

Environment

Product	Telerik UI for ASP.NET MVC Grid
Progress Telerik UI for ASP.NET MVC version	Created with the 2023.2.829 version

Description

How can I calculate the count of the distinct values in a specified column and display the result within its footer template?

Solution

The Grid's DataSource supports Count() aggregate field that calculates the total number of values. However, if you need to get the number of the distinct values, follow the steps below:

1. Execute a function within the ClientFooterTemplate() that will return the distinct values. Ensure that the server operations of the DataSource are disabled.

       @(Html.Kendo().Grid<OrderViewModel>()
           .Name("grid")
           .Columns(columns =>
           {
               columns.Bound(p => p.OrderID);
               columns.Bound(x => x.ShipName).ClientFooterTemplate("#: getDistinctValues() #");
           })
           ...
           .DataSource(dataSource => dataSource
               .Ajax()
               .PageSize(10)
               .ServerOperation(false)
               ...
           )
           .Pageable()
           .Sortable()
           .Scrollable()
           .Groupable()
           .Filterable()
           ...
       )
   

       function getDistinctValues() {
           ...
       }
   

2. Calculate the distinct values of the "ShipName" field on the client with jQuery.

       function getDistinctValues() {
           var grid = $("#grid").getKendoGrid(); // Get a reference to the Grid.
           var allGridData = grid.dataSource.data(); // Get the Grid's data.
           var appliedFilters = grid.dataSource.filter(); // Get the current filter expressions.
           var aggregateResult = 0;
   
           if (appliedFilters != null) { // Check if the Grid is filtered.
               var dataQuery = new kendo.data.Query(allGridData); // Create a Query (https://docs.telerik.com/kendo-ui/api/javascript/data/query).
               var filteredData = dataQuery.filter(appliedFilters).data; // Get a copy of the filtered data according to the applied filter expression.
               aggregateResult = getAggregates(filteredData); // Pass the filtered records to the getAggregates() function to calculate the distinct values count.
           } else {
               aggregateResult = getAggregates(allGridData); // If the Grid is not filtered, pass all records to the getAggregates() function.
           }
   
           return `Total: ${aggregateResult}`;
       }
   
       function getAggregates(gridData) {
           var distinctValues = [];
           $.each(gridData, function (i, el) { // Loop through the Grid records.
               if (distinctValues.indexOf(el.ShipName) == -1) {
                   distinctValues.push(el.ShipName);  // Store the unique values.
               }
           });
           return distinctValues.length; // Return their count.
       }
   

3. Handle the DataBound event of the Grid and update the displayed distinct values count when the Grid is filtered and/or grouped.

       function onDataBound(e) {
           if (e.sender.dataSource.filter() != null) { // Check if the data is filtered.
               var appliedFilters = e.sender.dataSource.filter(); // Get the current filter expressions.
               var allGridData = e.sender.dataSource.data(); //Get all Grid records.
               var dataQuery = new kendo.data.Query(allGridData); //Create a Query.
               var filteredData = dataQuery.filter(appliedFilters).data; // Get a copy of the filtered data according to the applied filter expression.
               var updatedAggregate = getAggregates(filteredData); // Update the aggregation result.
               var columnFooterIndex = e.sender.dataSource.group().length + 2; // Get the column index when the Grid is grouped. "+2" is added because the "ShipName" column is the second column the Grid declaration. Replace the value with the index of the column at your end (1-based index).
               e.sender.footer.find(`.k-footer-template td:nth-child(${columnFooterIndex})`).html(`Total: ${updatedAggregate}`); // Update the column footer template.
           }
       }
   

For a runnable example based on the code above, refer to the REPL example on displaying distinct values in Grid column footer.

More ASP.NET MVC Grid Resources

• ASP.NET MVC Grid Documentation

• ASP.NET MVC Grid Demos

• ASP.NET MVC Grid Product Page

• Telerik UI for ASP.NET MVC Video Onboarding Course (Free for trial users and license holders)

• Telerik UI for ASP.NET MVC Forums

See Also

• Client-Side API Reference of the Grid for ASP.NET MVC

• Server-Side API Reference of the Grid for ASP.NET MVC

• Telerik UI for ASP.NET MVC Breaking Changes

• Telerik UI for ASP.NET MVC Knowledge Base