Aggregates in ASP.NET Core Grid control

28 Jan 2022 / 3 minutes to read

Aggregate values are displayed in the footer, group footer, or group caption of the Grid. It can be configured through e-grid-aggregates tag helper. field and type are the minimum properties required to represent an aggregate column.

By default, the aggregate value can be displayed in the footer, group, and caption cells. To show the aggregate value in one of the cells, use the footerTemplate, groupFooterTemplate, or groupCaptionTemplate property.

Built-in aggregate types

The aggregate type should be specified in the type property to configure an aggregate column.

The built-in aggregates are,

Sum
Average
Min
Max
Count
TrueCount
FalseCount

Multiple aggregates can be used for an aggregate column by setting the type property with an array of aggregate types.

Multiple types for a column is supported only when one of the aggregate templates is used.

Footer aggregate value is calculated for all the rows, and it is displayed in the footer cells. Use the footerTemplate property to render the aggregate value in footer cells.

tagHelper

footer-agg.cs

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowPaging="true">
    <e-grid-aggregates>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column  field="Freight" type="Sum" footerTemplate="Sum:${Sum}"></e-aggregate-column>                        
            </e-aggregate-columns>
        </e-grid-aggregate>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column field="Freight" type="Average"  footerTemplate="Average:${Average}"></e-aggregate-column>
            </e-aggregate-columns>
        </e-grid-aggregate>
    </e-grid-aggregates>
    <e-grid-pagesettings pageCount="5"></e-grid-pagesettings>
    <e-grid-columns>                
        <e-grid-column field="CustomerID" headerText="Customer Name" width="150"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" TextAlign="Right" format="C2" width="160"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd"  width="130"></e-grid-column>               
        <e-grid-column field="ShipCountry"  headerText="Ship Country" width="140"></e-grid-column>                
    </e-grid-columns>
</ejs-grid>

public IActionResult Index()
{
    var Order = OrderDetails.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

The aggregate values must be accessed inside the template using their corresponding type name.

How to format aggregate value

You can format the aggregate value result by using the format property.

tagHelper

format-agg.cs

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowPaging="true">
    <e-grid-aggregates>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column  field="Freight" type="Sum" format="C2" footerTemplate="Sum:${Sum}"></e-aggregate-column>                        
            </e-aggregate-columns>
        </e-grid-aggregate>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column field="Freight" type="Average" format="C2"  footerTemplate="Average:${Average}"></e-aggregate-column>
            </e-aggregate-columns>
        </e-grid-aggregate>
    </e-grid-aggregates>
    <e-grid-pagesettings pageCount="5"></e-grid-pagesettings>
    <e-grid-columns>                
        <e-grid-column field="CustomerID" headerText="Customer Name" width="150"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" TextAlign="Right" format="C2" width="160"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd"  width="130"></e-grid-column>               
        <e-grid-column field="ShipCountry"  headerText="Ship Country" width="140"></e-grid-column>                
    </e-grid-columns>
 </ejs-grid>

public IActionResult Index()
{
    var Order = Product.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Group and caption aggregate

Group and caption aggregate values are calculated from the current group items. If groupFooterTemplate is provided, the aggregate values are displayed in the group footer cells; and if groupCaptionTemplate is provided, aggregate values are displayed in the group caption cells.

tagHelper

group-caption.cs

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowGrouping="true" allowPaging="true">
    <e-grid-aggregates>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column field="UnitsInStock" type="Sum" groupFooterTemplate="Total units: ${Sum}"></e-aggregate-column>
                <e-aggregate-column field="Discontinued" type="Truecount" groupFooterTemplate="Discontinued: ${Truecount}"></e-aggregate-column>
                <e-aggregate-column field="UnitsInStock" type="Max" groupCaptionTemplate="Maximum: ${Max}"></e-aggregate-column>
            </e-aggregate-columns>
        </e-grid-aggregate>
    </e-grid-aggregates>
    <e-grid-groupsettings columns="@(new string[] { "CategoryName" })"></e-grid-groupsettings>
    <e-grid-pagesettings pageCount="5"></e-grid-pagesettings>
    <e-grid-columns>
        <e-grid-column field="CategoryName" headerText="Category Name" width="160"></e-grid-column>
        <e-grid-column field="ProductName" headerText="Product Name" width="170"></e-grid-column>
        <e-grid-column field="QuantityPerUnit" headerText="Quantity Per Unit" textAlign="Right" width="170"></e-grid-column>
        <e-grid-column field="UnitsInStock" headerText="Units In Stock" width="170"></e-grid-column>
        <e-grid-column field="Discontinued" headerText="Discontinued" displayAsCheckBox="true" type="boolean" textAlign="Center" width="150"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

public IActionResult Index()
{
    var Order = Product.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

The aggregate values must be accessed inside the template using their corresponding type name.

Custom aggregate

To calculate the aggregate value with your own aggregate functions, use the custom aggregate option. To use custom aggregation, specify the type as Custom, and provide the custom aggregate function in the customAggregate property in e-grid-aggregates tag helper.

The custom aggregate function will be invoked with different arguments for total and group aggregations.

Total aggregation: The custom aggregate function will be called with the whole data and current aggregateColumn object.
Group aggregation: This will be called with the current group details and aggregateColumn object.

tagHelper

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowPaging="true">
    <e-grid-aggregates>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column field="ShipCountry" type="Custom" footerTemplate="Brazil Count:${Custom}" customAggregate="@("customAggregateFn")"></e-aggregate-column>
            </e-aggregate-columns>
        </e-grid-aggregate>
    </e-grid-aggregates>
    <e-grid-pagesettings pageCount="5"></e-grid-pagesettings>
    <e-grid-columns>
        <e-grid-column field="CustomerID" headerText="Customer Name" width="150"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" width="160"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" textAlign="Right" width="130"></e-grid-column>
        <e-grid-column field="ShipCountry" headerText="Ship Country" width="140"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

<script>
    function customAggregateFn(data, aggColumn) {
        return data.result.filter(function (item) {
            return item[aggColumn.columnName] === 'Brazil';
        }).length;
    }
</script>

To access the custom aggregate value inside the template, use the key as Custom.

Reactive aggregate update

When using batch editing, the aggregate values will be refreshed on every cell save. The footer, group footer, and group caption aggregate values will be refreshed.

Adding a new record to the grouped grid will not refresh the aggregate values.

tagHelper

reactive-agg-batch-edit.cs

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowPaging="true" allowGrouping="true" height="273" toolbar="@(new List<string>() { "Add","Delete","Update","Cancel" })">
    <e-grid-groupsettings showDropArea="false" columns="@(new string[] { "ShipCountry" })"></e-grid-groupsettings>
    <e-grid-editSettings allowDeleting="true" allowEditing="true" mode="Batch"></e-grid-editSettings>
    <e-grid-aggregates>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column field="Freight" type="Sum" footerTemplate="Sum: ${Sum}" format="C2"></e-aggregate-column>
                <e-aggregate-column field="Freight" type="Sum" groupFooterTemplate="Sum: ${Sum}" format="C2"></e-aggregate-column>
                <e-aggregate-column field="Freight" type="Average" groupCaptionTemplate="Average: ${Average}" format="C2"></e-aggregate-column>
            </e-aggregate-columns>
        </e-grid-aggregate>
    </e-grid-aggregates>
    <e-grid-columns>
        <e-grid-column field="CustomerID" headerText="Customer Name" width="150"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" width="160"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" textAlign="Right" width="130"></e-grid-column>
        <e-grid-column field="ShipCountry" headerText="Ship Country" width="140"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

public IActionResult Index()
{
    var Order = Product.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Refresh aggregates in inline edit mode

By default, reactive aggregate update is not supported by inline and dialog edit modes as it is not feasible to anticipate the value change event for every editor. But, you can refresh the aggregates manually in the inline edit mode using the refresh method of aggregate module.

In the following code, the input event for the Freight column editor has been registered and the aggregate value has been refreshed manually.

tagHelper

reactive-agg-inline-edit.cs

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowPaging="true" allowGrouping="true" height="273" toolbar="@(new List<string>() { "Add", "Edit", "Delete","Update","Cancel" })" actionBegin="actionBegin" load="load">
    <e-grid-editSettings allowDeleting="true" allowEditing="true" mode="Normal"></e-grid-editSettings>
    <e-grid-aggregates>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column field="Freight" type="Sum" footerTemplate="Sum: ${Sum}" format="C2"></e-aggregate-column>
            </e-aggregate-columns>
        </e-grid-aggregate>
    </e-grid-aggregates>
    <e-grid-columns>
        <e-grid-column field="CustomerID" headerText="Customer Name" width="150"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" editType="numericedit" width="160"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" textAlign="Right" width="130"></e-grid-column>
        <e-grid-column field="ShipCountry" headerText="Ship Country" width="140"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

<script>
    let selectedRecord = {};

    function load(args) {
        this.columns[1].edit = { params: { change: changeFn } };
    }

    function actionBegin(args) {
        if ((args.requestType === 'beginEdit')) {
            selectedRecord = {};
            selectedRecord = args.rowData;
        }
    }

    function changeFn(args) {
        let gridObj = document.getElementById("Grid").ej2_instances[0];
        selectedRecord['Freight'] = args.value;
        gridObj.aggregateModule.refresh(selectedRecord);
    }
</script>

public IActionResult Index()
{
    var Order = Product.GetAllRecords();
    ViewBag.DataSource = Order;
    return View();
}

Contents

Built-in aggregate types
Footer aggregate
How to format aggregate value
Group and caption aggregate
Custom aggregate
Reactive aggregate update
Refresh aggregates in inline edit mode

Contents

Built-in aggregate types
Footer aggregate
How to format aggregate value
Group and caption aggregate
Custom aggregate
Reactive aggregate update
Refresh aggregates in inline edit mode

Please provide additional information

Thank you for your feedback and comments.We will rectify this as soon as possible!