-
-
Notifications
You must be signed in to change notification settings - Fork 29
Grouping & Aggregators
- Demo
- Description
- Setup
- Aggregators
- SortComparers
- GroupTotalsFormatter
- Custom GroupTotalsFormatter
- Set a Grouping
- Clear Grouping / Collapse All / Expand All
- Styling - Change Icons
Fully dynamic and interactive multi-level grouping with filtering and aggregates that is achieved with the help of the DataView
object in SlickGrid
. Each grouping level can have its own aggregates (over child rows, child groups, or all descendant rows). An aggregate can be seen as sub-totals, totals, average, ... or any defined group(s).
How does it work in SlickGrid
?
The important thing to understand while working with SlickGrid
is that Grouping requires you to provide 2 things, if you omit 1 of them, it will simply not work. These 2 things are
- You will need to define which type of aggregate (accumulator) you want to use
- Slickgrid-Universal provides the following built-in
Aggregators
:Avg
,Min
,Max
,Sum
,Clone
,Distinct
- Slickgrid-Universal provides the following built-in
- You need to add a
groupTotalsFormatter
on the column definition you want it to be calculated- this is very similar to a Formatter, except that they are designed to show aggregate results, e.g::
Total: 142.50$
- this is very similar to a Formatter, except that they are designed to show aggregate results, e.g::
The Aggregators
is basically the accumulator, the logic that will do the sum (or any other aggregate we defined). We simply need to instantiate the Aggregator
by passing the column definition field
that will be used to accumulate. For example, if we have a column definition of Cost and we want to calculate it's sum, we can call the Aggregator
as follow
new Aggregators.Sum('cost')
The available built-in Aggregators
are
-
Aggregators.Avg
(calculate the Average of a group) -
Aggregators.Min
(returns the Minimum value of a group) -
Aggregators.Max
(returns the Maximum value of a group) -
Aggregators.Sum
(calculate the Sum of a group) -
Aggregators.Clone
(will clone the same grouped text and display it in as an aggregated value) -
Aggregators.Distinct
(will show distinct value)
When defining your column definitions, you will need to decide which of the column will have an aggregate. Once that decision is made, you will add a groupTotalsFormatter
to that column definition in question (a Formatter for the group total). For example, let say that we have a cost and we want a total sum grouped by a duration, the code would look like below.
You can see the full list under groupingFormatters.index.ts
Note: the Group Total Formatters named as currency will have these extra params
(groupFormatterPrefix
, groupFormatterSuffix
, groupFormatterCurrencyPrefix
, groupFormatterCurrencySuffix
) and also the other common Formatter params
(minDecimal
, maxDecimal
, decimalSeparator
, thousandSeparator
, displayNegativeNumberWithParentheses
).
avgTotalsPercentageFormatter
avgTotalsDollarFormatter
avgTotalsCurrencyFormatter
avgTotalsFormatter
minTotalsFormatter
maxTotalsFormatter
sumTotalsColoredFormatter
sumTotalsCurrencyFormatter
sumTotalsCurrencyColoredFormatter
sumTotalsDollarColoredBoldFormatter
sumTotalsDollarColoredFormatter
sumTotalsDollarBoldFormatter
sumTotalsDollarFormatter
sumTotalsFormatter
sumTotalsBoldFormatter
initializeGrid() {
this.columnDefinitions = [
{
id: 'title', name: 'Title', field: 'title'
},
{
id: 'duration', name: 'Duration', field: 'duration',
type: FieldType.number,
groupTotalsFormatter: GroupTotalFormatters.sumTotals,
params: { groupFormatterPrefix: 'Total: ' }
},
{
id: 'cost', name: 'Cost', field: 'cost',
exportWithFormatter: true, // for a Dollar Formatter, we also want it to be displayed in the export to file
formatter: Formatters.dollar,
groupTotalsFormatter: GroupTotalFormatters.sumTotalsDollar,
params: { groupFormatterPrefix: '<b>Total</b>: ' /*, groupFormatterSuffix: ' USD'*/ }
}
];
this.gridOptions = {
enableGrouping: true, // don't forget to enable the grouping
exportOptions: {
sanitizeDataExport: true // you can also sanitize the exported data (it will remove any HTML tags)
}
};
}
We can also pass prefix and/or suffix to each groupTotalsFormatter
by adding them to the params
object. Also note that you can also type HTML to be interpreted. For example, let say we would like Total:
to show as bold and a suffix of 'USD' , you can write it this way:
Note prefix/suffix are concatenated without spaces, if you require a space then make sure to add it in accordingly.
{
id: 'cost', name: 'Cost', field: 'cost',
groupTotalsFormatter: GroupTotalFormatters.sumTotalsDollar,
params: { groupFormatterPrefix: '<b>Total</b>: ', groupFormatterSuffix: ' USD' }
}
You can also create a custom groupTotalsFormatter
similarly to a Formatter, just a create a function that will return a string, for example:
defineGrid() {
this.columnDefinitions = [
{
id: 'cost', name: 'Cost', field: 'cost',
groupTotalsFormatter: this.sumTotalsFormatter
}
];
}
sumTotalsFormatter(totals, columnDef) {
const val = totals.sum && totals.sum[columnDef.field];
if (val != null) {
return 'total: ' + ((Math.round(parseFloat(val) * 100) / 100));
}
return '';
}
Once you have added a groupTotalsFormatter
and defined which aggregate you want to use, you will want to create a grouping function. If we take again our example of a grid with multiple task and we want to group our task by duration and calculate the duration average and the cost total sum, we can write the following function
groupByDuration() {
this.dataviewObj.setGrouping({
getter: 'duration', // the column `field` to group by
formatter: (g) => {
// (required) what will be displayed on top of each group
return `Duration: ${g.value} <span style="color:green">(${g.count} items)</span>`;
},
comparer: (a, b) => {
// (optional) comparer is helpful to sort the grouped data
// code below will sort the grouped value in ascending order
return SortComparers.numeric(a.value, b.value, SortDirectionNumber.asc);
},
aggregators: [
// (optional), what aggregators (accumulator) to use and on which field to do so
new Aggregators.Avg('percentComplete'),
new Aggregators.Sum('cost')
],
aggregateCollapsed: false, // (optional), do we want our aggregator to be collapsed?
lazyTotalsCalculation: true // (optional), do we want to lazily calculate the totals? True is commonly used
});
}
To "Clear all Grouping", "Collapse all Groups" and "Expand all Groups", we can simply call the associated DataView
function, like so:
clearGrouping() {
this.dataviewObj.setGrouping([]);
}
collapseAllGroups() {
this.dataviewObj.collapseAllGroups();
}
expandAllGroups() {
this.dataviewObj.expandAllGroups();
}
The current icons are Font Awesome chevron (right/down), however if you wish to use +/- icons. You can simply update the SASS variables to use whichever icons (or even Font Family icon) you desire. The SASS variables you can change are
$icon-group-color: $primary-color;
$icon-group-expanded: "\f107";
$icon-group-collapsed: "\f105";
$icon-group-font-size: ($icon-font-size + 2px);
$icon-group-font-weight: bold;
$icon-group-margin-right: 2px;
$icon-group-height: 20px;
$icon-group-width: 14px;
/* Grouping Totals Formatter */
$group-totals-formatter-color: gray;
$group-totals-formatter-bgcolor: white;
$group-totals-formatter-font-size: 14px;
For more info on SASS styling and variables, please read the Wiki - SASS Styling,
- Slickgrid-Universal Wikis
- Installation
- Styling
- Interfaces/Models
- Column Functionalities
- Events
- Grid Functionalities
- Auto-Resize / Resizer Service
- Resize by Cell Content
- Column Picker
- Composite Editor Modal
- Custom Tooltip
- Context Menu
- Custom Footer
- Export to Excel
- Export to File (csv/txt)
- Grid Menu
- Grid State & Presets
- Grouping & Aggregators
- Header Menu & Header Buttons
- Pinning (frozen) of Columns/Rows
- Row Selection
- Tree Data Grid
- SlickGrid & DataView objects
- Backend Services