Displaying the count of distinct values in Grid column footer template

Environment

Product	Telerik UI for ASP.NET Core Grid
Progress Telerik UI for ASP.NET Core 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()
           ...
       )
   

       @addTagHelper *, Kendo.Mvc
   
       <kendo-grid name="grid">
           <columns>
               <column field="OrderID"/>
               <column field="ShipName" footer-template="#: getDistinctValues() #"></column>
           </columns>
           <datasource type="DataSourceTagHelperType.Ajax" page-size="10" server-operation="false">
               <!-- Other configuration -->
           </datasource>
           <pageable enabled="true"/>
           <scrollable enabled="true"/>
           <filterable enabled="true"/>
           <sortable enabled="true" />
           <groupable enabled="true" />
           <!-- Other configuration -->
       </kendo-grid>
   
   

       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 following REPL samples:

• Sample code with the Grid HtmlHelper
• Sample code with the Grid TagHelper

More ASP.NET Core Grid Resources

• ASP.NET Core Grid Documentation

• ASP.NET Core Grid Demos

• ASP.NET Core Grid Product Page

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

• Telerik UI for ASP.NET Core Forums

See Also

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

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

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

• Telerik UI for ASP.NET Core Breaking Changes

• Telerik UI for ASP.NET Core Knowledge Base