Custom Aggregates

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 'bootstrap.css'; import '@grapecity/wijmo.styles/wijmo.css'; import './styles.css'; import * as wjCore from '@grapecity/wijmo'; import * as wjGrid from '@grapecity/wijmo.grid'; // document.readyState === 'complete' ? init() : window.onload = init; // function init() { // // generate some random data var countries = 'US,Germany,UK,Japan,Italy,Greece'.split(','), products = 'Phones,Computers,Cameras,Stereos'.split(','), data = []; for (var i = 0; i < 200; i++) { data.push({ id: i, country: countries[i % countries.length], product: products[i % products.length], sales: Math.random() * 10000, expenses: Math.random() * 5000, }); } // // 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: [ { 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' ] }) }); // // 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 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 wjCore.toggleClass(e.cell, 'negative', negative); } // }); } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>GrapeCity Wijmo FlexGrid Custom Aggregation</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app'); </script> </head> <body> <div class="container-fluid"> <div id="theGrid"> </div> </div> </body> </html> .wj-flexgrid { max-height: 250px; margin: 10px 0; } .wj-cell.wj-frozen-row { border-bottom: none; } .negative { color: red; } body { margin-bottom: 20px; } import 'bootstrap.css'; import '@grapecity/wijmo.styles/wijmo.css'; import './styles.css'; import { Component, enableProdMode, NgModule } from '@angular/core'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { BrowserModule } from '@angular/platform-browser'; import { WjGridModule } from '@grapecity/wijmo.angular2.grid'; import * as wjcCore from '@grapecity/wijmo'; import * as wjcGrid from '@grapecity/wijmo.grid'; @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { data: wjcCore.CollectionView; // DataSvc will be passed by derived classes constructor() { this.data = new wjcCore.CollectionView(this._getData(), { groupDescriptions: [ new wjcCore.PropertyGroupDescription('Grand Total', () => { return ''; }), 'country' ] }); } initializeGrid(flex: wjcGrid.FlexGrid) { // start collapsed flex.collapseGroupsToLevel(1); // custom cell calculation flex.formatItem.addHandler((s: wjcGrid.FlexGrid, e: wjcGrid.FormatItemEventArgs) => { // cells and column footer panels only if (e.panel == s.cells) { // get row, column, and data item (or group description) let r = s.rows[e.row], c = s.columns[e.col], item = s.rows[e.row].dataItem, group = r instanceof wjcGrid.GroupRow ? item : null, negative = false; // assume value is not negative // calculate profit if (c.binding == 'profit') { let profit = group ? group.getAggregate('Sum', 'sales') - group.getAggregate('Sum', 'expenses') : item.sales - item.expenses; e.cell.textContent = wjcCore.Globalize.format(profit, c.format); negative = profit < 0; } // update 'negative' class on cell wjcCore.toggleClass(e.cell, 'negative', negative); } }); } private _getData() { // create some random data let countries = 'US,Germany,UK,Japan,Italy,Greece'.split(','), products = 'Phones,Computers,Cameras,Stereos'.split(','), data = []; for (let i = 0; i < 200; i++) { data.push({ id: i, country: countries[i % countries.length], product: products[i % products.length], sales: Math.random() * 10000, expenses: Math.random() * 5000 }); } return data; } } @NgModule({ imports: [WjGridModule, BrowserModule], declarations: [AppComponent], bootstrap: [AppComponent] }) export class AppModule { } enableProdMode(); // Bootstrap application with hash style navigation and global services. platformBrowserDynamic().bootstrapModule(AppModule); <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>GrapeCity Wijmo FlexGrid Custom Aggregation</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- Polyfills --> <script src="node_modules/core-js/client/shim.min.js"></script> <script src="node_modules/zone.js/dist/zone.min.js"></script> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.js"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html> <div class="container-fluid"> <!-- the grid --> <wj-flex-grid #flex [(itemsSource)]="data" (initialized)="initializeGrid(flex)"> <wj-flex-grid-column [binding]="'id'" [header]="'ID'" [width]="60" [isReadOnly]="true"></wj-flex-grid-column> <wj-flex-grid-column [binding]="'country'" [header]="'Country'"></wj-flex-grid-column> <wj-flex-grid-column [binding]="'product'" [header]="'Product'"></wj-flex-grid-column> <wj-flex-grid-column [binding]="'sales'" [header]="'Sales'" [aggregate]="'Sum'"></wj-flex-grid-column> <wj-flex-grid-column [binding]="'expenses'" [header]="'Expenses'" [aggregate]="'Sum'"></wj-flex-grid-column> <wj-flex-grid-column [binding]="'profit'" [header]="'Profit'" [dataType]="number" [isReadOnly]="true"></wj-flex-grid-column> </wj-flex-grid> </div> .wj-flexgrid { max-height: 250px; margin: 10px 0; } .wj-cell.wj-frozen-row { border-bottom: none; } .negative { color: red; } body { margin-bottom: 20px; } <template> <div class="container-fluid"> <wj-flex-grid :itemsSource="data" :initialized="initializedGrid"> <wj-flex-grid-column binding="id" header="ID" :width=60 :isReadOnly=true></wj-flex-grid-column> <wj-flex-grid-column binding="country" header="Country"></wj-flex-grid-column> <wj-flex-grid-column binding="product" header="Product"></wj-flex-grid-column> <wj-flex-grid-column binding="sales" header="Sales" aggregate="Sum"></wj-flex-grid-column> <wj-flex-grid-column binding="expenses" header="Expenses" aggregate="Sum"></wj-flex-grid-column> <wj-flex-grid-column binding="profit" header="Profit" :dataType=2 :isReadOnly=true></wj-flex-grid-column> </wj-flex-grid> </div> </template> <script> import "@grapecity/wijmo.styles/wijmo.css"; import "bootstrap.css"; import Vue from "vue"; import "@grapecity/wijmo.vue2.grid"; import * as wjcGrid from '@grapecity/wijmo.grid'; import * as wjcCore from '@grapecity/wijmo'; import { getData } from "./data"; new Vue({ el: "#app", data: function() { return { data: new wjcCore.CollectionView(getData(), { groupDescriptions: [ new wjcCore.PropertyGroupDescription('Grand Total', () => { return ''; }), 'country' ] }) }; }, methods:{ initializedGrid(flex){ // start collapsed flex.collapseGroupsToLevel(1); // custom cell calculation flex.formatItem.addHandler((s, e) => { // cells and column footer panels only if (e.panel == s.cells) { // get row, column, and data item (or group description) let r = s.rows[e.row], c = s.columns[e.col], item = s.rows[e.row].dataItem, group = r instanceof wjcGrid.GroupRow ? item : null, negative = false; // assume value is not negative // calculate profit if (c.binding == 'profit') { let profit = group ? group.getAggregate('Sum', 'sales') - group.getAggregate('Sum', 'expenses') : item.sales - item.expenses; e.cell.textContent = wjcCore.Globalize.format(profit, c.format); negative = profit < 0; } // update 'negative' class on cell wjcCore.toggleClass(e.cell, 'negative', negative); } }); } } }); </script> <style> .wj-flexgrid { max-height: 250px; margin: 10px 0; } .wj-cell.wj-frozen-row { border-bottom: none; } .negative { color: red; } body { margin-bottom: 20px; } </style> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>AutoComplete</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app.vue'); </script> </head> <body> <div id="app"> </div> </body> </html> export function getData() { // create some random data let countries = 'US,Germany,UK,Japan,Italy,Greece'.split(','), products = 'Phones,Computers,Cameras,Stereos'.split(','), data = []; for (let i = 0; i < 200; i++) { data.push({ id: i, country: countries[i % countries.length], product: products[i % products.length], sales: Math.random() * 10000, expenses: Math.random() * 5000 }); } return data; } import 'bootstrap.css'; import '@grapecity/wijmo.styles/wijmo.css'; import './app.css'; // import * as React from 'react'; import * as ReactDOM from 'react-dom'; import * as wjGrid from '@grapecity/wijmo.react.grid'; import * as wjcGrid from '@grapecity/wijmo.grid'; import * as wjcCore from '@grapecity/wijmo'; import { getData } from './data'; class App extends React.Component { constructor(props) { super(props); this.state = { data: new wjcCore.CollectionView(getData(), { groupDescriptions: [ new wjcCore.PropertyGroupDescription('Grand Total', () => { return ''; }), 'country' ] }) }; } render() { return <div className="container-fluid"> <wjGrid.FlexGrid initialized={this.initializedGrid.bind(this)} itemsSource={this.state.data}> <wjGrid.FlexGridColumn binding="id" header="ID" width={60} isReadOnly={true}> </wjGrid.FlexGridColumn> <wjGrid.FlexGridColumn binding="country" header="Country"> </wjGrid.FlexGridColumn> <wjGrid.FlexGridColumn binding="product" header="Product"> </wjGrid.FlexGridColumn> <wjGrid.FlexGridColumn binding="sales" header="Sales" aggregate="Sum"> </wjGrid.FlexGridColumn> <wjGrid.FlexGridColumn binding="expenses" header="Expenses" aggregate="Sum"> </wjGrid.FlexGridColumn> <wjGrid.FlexGridColumn binding="profit" header="Profit" dataType={2} isReadOnly={true}> </wjGrid.FlexGridColumn> </wjGrid.FlexGrid> </div>; } initializedGrid(flex) { // start collapsed flex.collapseGroupsToLevel(1); // custom cell calculation flex.formatItem.addHandler((s, e) => { // cells and column footer panels only if (e.panel == s.cells) { // get row, column, and data item (or group description) let r = s.rows[e.row], c = s.columns[e.col], item = s.rows[e.row].dataItem, group = r instanceof wjcGrid.GroupRow ? item : null, negative = false; // assume value is not negative // calculate profit if (c.binding == 'profit') { let profit = group ? group.getAggregate('Sum', 'sales') - group.getAggregate('Sum', 'expenses') : item.sales - item.expenses; e.cell.textContent = wjcCore.Globalize.format(profit, c.format); negative = profit < 0; } // update 'negative' class on cell wjcCore.toggleClass(e.cell, 'negative', negative); } }); } } ReactDOM.render(<App />, document.getElementById('app')); <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>GrapeCity Wijmo FlexGrid Custom Aggregation</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app'); </script> </head> <body> <div id="app"></div> </body> </html> .wj-flexgrid { max-height: 250px; margin: 10px 0; } .wj-cell.wj-frozen-row { border-bottom: none; } .negative { color: red; } body { margin-bottom: 20px; } export function getData() { // create some random data let countries = 'US,Germany,UK,Japan,Italy,Greece'.split(','), products = 'Phones,Computers,Cameras,Stereos'.split(','), data = []; for (let i = 0; i < 200; i++) { data.push({ id: i, country: countries[i % countries.length], product: products[i % products.length], sales: Math.random() * 10000, expenses: Math.random() * 5000 }); } return data; }