Custom Aggregates in FlexGrid

The FlexGrid columns have an aggregate property that allows you to show data summaries for the whole grid or for each group.

In some cases, however, the aggregate property is not flexible enough, and you may need to calculate aggregates using custom code.

The grid below includes a 'Profit' column that shows the difference between 'Sales' and 'Expenses'.

The 'Profit' column is calculated in the formatItem event. The profit for regular data items is based on the actual data items. The profit for groups is calculated using the group's getAggregate method.

import * as wjCore from '@grapecity/wijmo';
import * as wjGrid from '@grapecity/wijmo.grid';

// create a group to show the grand totals
var grandTotalsGroup = new wjCore.PropertyGroupDescription('Grand Total', 
    function(item, propName) {
        return '';
    }
);

// grid with custom aggregates
var theGrid = new wjGrid.FlexGrid('#theGrid', {
    autoGenerateColumns: false,
        columns: [ // column definitions with aggregates
        { binding: 'id', header: 'ID', width: 60, isReadOnly: true },
        { binding: 'country', header: 'Country' },
        { binding: 'product', header: 'Product' },
        { binding: 'sales', header: 'Sales', aggregate: 'Sum' },
        { binding: 'expenses', header: 'Expenses', aggregate: 'Sum' },
        { binding: 'profit', header: 'Profit', dataType: 'Number', isReadOnly: true }
    ],
    itemsSource: new wjCore.CollectionView(data, {
        groupDescriptions: [
        grandTotalsGroup,
        'country'
        ]
    })
});

// custom cell calculation
theGrid.formatItem.addHandler(function(s, e) {

// cells and column footer panels only
if (e.panel == s.cells) {
    // get row, column, and data item (or group description)
    var r = s.rows[e.row];
    var c = s.columns[e.col];
    var item = s.rows[e.row].dataItem;
    var group = r instanceof wjGrid.GroupRow ? item : null;

    // assume value is not negative
    var negative = false;

    // calculate profit
    if (c.binding == 'profit') {
        var profit = group
            ? group.getAggregate('Sum', 'sales') - group.getAggregate('Sum', 'expenses')
            : item.sales - item.expenses;
        e.cell.textContent = wjCore.Globalize.format(profit, c.format);
        negative = profit < 0;
    }
    // update 'negative' class on cell
    wijmo.toggleClass(e.cell, 'negative', negative);
    }
});