Custom Aggregates

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

However, in some cases, the aggregate property is not flexible enough. So, 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.

Country

Product

Sales

Expenses

Profit

Grand Total: (200 items)

10,544,840.09

5,143,141.32

5,401,698.77

Country: US (34 items)

1,883,457.04

1,024,257.58

859,199.46

Country: Germany (34 items)

1,835,043.22

704,881.55

1,130,161.68

Country: UK (33 items)

1,652,089.92

923,283.40

728,806.52

Country: Japan (33 items)

1,645,543.02

813,944.76

831,598.26

Country: Italy (33 items)

1,944,303.89

830,917.35

1,113,386.54

Country: Greece (33 items)

1,584,403.01

845,856.69

738,546.32

// This file locates: "Scripts/Lesson/C1FlexGrid/AggregatesCustom.js".
c1.documentReady(function () {
    // create a group to show the grand totals
    var grandTotalsGroup = new wijmo.collections.PropertyGroupDescription('Grand Total',
        function (item, propName) {
            return '';
        }
    );
    var countryGroup = new wijmo.collections.PropertyGroupDescription('Country');
 
    var theGrid = wijmo.Control.getControl('#theGrid');
    theGrid.collectionView.groupDescriptions.push(grandTotalsGroup);
    theGrid.collectionView.groupDescriptions.push(countryGroup);
 
    // start collapsed
    theGrid.collapseGroupsToLevel(1);
 
    // 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 wijmo.grid.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 = wijmo.Globalize.format(profit, c.format);
                negative = profit < 0;
            }
 
            // update 'negative' class on cell
            wijmo.toggleClass(e.cell, 'negative', negative);
        }
 
    });
});

// This file locates: "Content/css/Lesson/C1FlexGrid/AggregatesCustom.css".
.wj-cell.wj-frozen-row {
    border-bottom: none;
}
.negative {
  color: red;
}

using System.Web.Mvc;
 
namespace LearnMvcClient.Controllers
{
    public partial class C1FlexGridController : Controller
    {
        // GET: AggregatesCustom
        public ActionResult AggregatesCustom()
        {
            return View(Models.FlexGridData.GetSales(200));
        }
    }
}

@model IEnumerable<FlexGridData.Sale>
 
<h1>
    @Html.Raw(Resources.C1FlexGrid.AggregatesCustom_Title)
</h1>
<p>
    @Html.Raw(Resources.C1FlexGrid.AggregatesCustom_Text1)
</p>
<p>
    @Html.Raw(Resources.C1FlexGrid.AggregatesCustom_Text2)
</p>
<p>
    @Html.Raw(Resources.C1FlexGrid.AggregatesCustom_Text3)
</p>
<p>
    @Html.Raw(Resources.C1FlexGrid.AggregatesCustom_Text4)
</p>
@(Html.C1().FlexGrid<FlexGridData.Sale>().Id("theGrid").Height(250)
    .AutoGenerateColumns(false)
    .Bind(Model)
    .Columns(cs =>
    {
        cs.Add().Binding("Id").Header("ID").Width("60").IsReadOnly(true);
        cs.Add().Binding("Country").Header("Country");
        cs.Add().Binding("Product").Header("Product");
        cs.Add().Binding("Sales").Header("Sales").Aggregate(C1.Web.Mvc.Grid.Aggregate.Sum);
        cs.Add().Binding("Expenses").Header("Expenses").Aggregate(C1.Web.Mvc.Grid.Aggregate.Sum);
        cs.Add().Binding("Profit").Header("Profit").DataType(C1.Web.Mvc.Grid.DataType.Number).IsReadOnly(true);
    })
)