How to add a custom aggregation in ASP.NET MVC Pivot Table

28 Aug 20265 minutes to read

The ASP.NET MVC Pivot Table component allows you to extend its functionality by adding custom aggregation types to the built-in aggregation menu. This lets you perform calculations beyond the built-in aggregations, such as Sum, Average, Min, and Max. (Other built-in options include Count, DistinctCount, Product, Median, Percentage, and more; the custom mechanism described here is fully compatible with all of them.)

Prerequisites

Before adding custom aggregation types, ensure the following are in place:

  • The Syncfusion.EJ2.PivotView namespace is imported in the code-behind file (using Syncfusion.EJ2.PivotView;).
  • The Pivot Table’s value fields expose the aggregate menu to end users. This is enabled by default; if it has been disabled in your configuration, re-enable it from the value-field settings.
  • Your Syncfusion ASP.NET MVC version is recent enough to expose both the DataBound and AggregateCellInfo events. See the Syncfusion ASP.NET MVC PivotView CR reference for the version matrix.

Adding custom aggregation types

You can use the DataBound event to add your own custom aggregation types to the Pivot Table’s aggregate menu. This event fires after the Pivot Table has been fully rendered, making it the perfect spot to modify the component’s UI elements.

In the following example, two custom aggregation types CustomAggregateType 1 (which calculates a weighted average) and CustomAggregateType 2 (which calculates the percentage of total) are added to the aggregate menu.

The calculation logic for these custom aggregation types is implemented using the AggregateCellInfo event.

Wiring a custom aggregation type to a measure

After registering the custom type in the aggregate menu (via the DataBound event), assign it to a measure field by setting that field’s type property in the Pivot Table’s data-source settings to the registered name (for example, CustomAggregateType 1). The custom type then appears as a selectable option in that field’s aggregate menu and is used to compute its values.

Removing or updating a custom aggregation type

To remove or replace a registered custom type, re-run the same DataBound registration block with the updated list (and call any cleanup needed for previously bound types) so the aggregate menu is rebuilt with the new set of options. Avoid keeping stale custom-type names bound to value fields after they are removed, otherwise the Pivot Table will fall back to its default aggregation for the affected cells.

AggregateCellInfo event parameters

This event provides parameters including:

  • fieldName - Holds the current cell’s field name.
  • row - Holds the current cell’s row value.
  • column - Holds the current cell’s column value.
  • value - Holds the value of the current cell.
  • cellSets - Holds raw data for the aggregated value cell.
  • rowCellType - Holds the row cell type.
  • columnCellType - Holds the column cell type.
  • aggregateType - Holds the aggregate type of the cell.
  • skipFormatting - Boolean property that allows skipping formatting if applied.
@using Syncfusion.EJ2.PivotView

@Html.EJS().PivotView("pivotview").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
    formatsettings.Name("Amount").Format("C0").Add();
}).Rows(rows =>
{
    rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
    columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
    values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(new PivotViewGridSettings { ColumnWidth = 140 }).AggregateCellInfo("aggregateCell").DataBound("dataBound").Render()
<script>
    var SummaryType = [
        'Sum',
        'Count',
        'DistinctCount',
        'Avg',
        'CustomAggregateType1',
        'CustomAggregateType2'
    ];
    var L10n = ej.base.L10n;
    L10n.load({
       'en-US': {
            pivotview: {
                CustomAggregateType1: 'Custom Aggregate Type 1',
                CustomAggregateType2: 'Custom Aggregate Type 2',
            },
            pivotfieldlist: {
                CustomAggregateType1: 'Custom Aggregate Type 1',
                CustomAggregateType2: 'Custom Aggregate Type 2',
            }
        }
    });
    function dataBound() {
        var pivotObj = document.getElementById('pivotview').ej2_instances[0];
        pivotObj.getAllSummaryType = function () {
            return SummaryType;
        };
        pivotObj.pivotFieldListModule.aggregateTypes = SummaryType;
        pivotObj.pivotFieldListModule.getAllSummaryType = function () {
            return SummaryType;
        };
    }
     function aggregateCell(args){
        if (args.aggregateType === 'CustomAggregateType1') {
            args.value = args.value * 100;
        }
        if (args.aggregateType === 'CustomAggregateType2') {
            args.value = args.value / 100;
        }
    }
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Add custom aggregation type to the menu