# Grouping & Aggregators
**index**
* [Demo](#demo)
* [Description](#description)
* [Setup](#setup)
* [Draggable Dropzone Location](#draggable-dropzone-location)
* [Aggregators](#aggregators)
* [SortComparers](https://github.com/ghiscoding/slickgrid-universal/blob/master/packages/common/src/sortComparers/sortComparers.index.ts)
* [GroupTotalsFormatter](#group-totals-formatter)
* [Custom GroupTotalsFormatter](#custom-grouptotalsformatter)
* [Set a Grouping](#set-a-grouping)
* [Clear Grouping / Collapse All / Expand All](#clear-grouping--collapse-all--expand-all)
* [Styling - Change Icons](#styling-change-icons)
### Demo
**Regular Grouping**
[Demo Page](https://ghiscoding.github.io/slickgrid-universal/#/example02) / [Demo Component](https://github.com/ghiscoding/slickgrid-universal/blob/master/demos/vanilla/src/examples/example02.ts)
**Draggable Grouping**
[Demo Page](https://ghiscoding.github.io/slickgrid-universal/#/example03) / [Demo Component](https://github.com/ghiscoding/slickgrid-universal/blob/master/demos/vanilla/src/examples/example03.ts)
#### Description
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
1. 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`
2. 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$`
#### Draggable Dropzone Location
The Draggable Grouping can be located in either the Top-Header or the Pre-Header as described below.
**Pre-Header**
Draggable Grouping can be located in either the Pre-Header of the Top-Header, however when it is located in the Pre-Header then the Header Grouping will not be available (because both of them would conflict with each other). Note that prior to the version 5.1 of Slickgrid-Universal, the Pre-Header was the default and only available option.
```ts
this.gridOptions = {
createPreHeaderPanel: true,
showPreHeaderPanel: true,
preHeaderPanelHeight: 26,
draggableGrouping: {
// ... any draggable plugin option
},
groupItemMetadataOption: {
// enable toggle of group by clicking on the toggle icon or its title (not just the toggle icon), defaults to false
toggleOnNodeTitle: true,
}
}
```
**Top-Header**
**requires v5.1 and higher**
This is the preferred section since the Top-Header is on top of all headers (including pre-header) and it will always be the full grid width. Using the Top-Header also frees up the Pre-Header section for the potential use of Header Grouping.
When using Draggable Grouping and Header Grouping together, you need to enable both top-header and pre-header.
```ts
this.gridOptions = {
// we'll use top-header for the Draggable Grouping
createTopHeaderPanel: true,
showTopHeaderPanel: true,
topHeaderPanelHeight: 35,
// pre-header will include our Header Grouping (i.e. "Common Factor")
createPreHeaderPanel: true,
showPreHeaderPanel: true,
preHeaderPanelHeight: 26,
}
```
#### Aggregators
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
```ts
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)
#### Group Totals Formatter
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.
**Available Group Total Formatters**
You can see the full list under [`groupingFormatters.index.ts`](https://github.com/ghiscoding/slickgrid-universal/blob/master/packages/common/src/grouping-formatters/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`
**ViewModel**
```typescript
initializeGrid() {
this.columns = [
{
id: 'title', name: 'Title', field: 'title'
},
{
id: 'duration', name: 'Duration', field: 'duration',
type: '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: 'Total: ' /*, 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)
},
groupItemMetadataOption: {
// enable toggle of group by clicking on the toggle icon or its title (not just the toggle icon), defaults to false
toggleOnNodeTitle: true,
}
};
}
```
**Prefix/Suffix to a `groupTotalsFormatter`**
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.
**ViewModel**
```ts
{
id: 'cost', name: 'Cost', field: 'cost',
groupTotalsFormatter: GroupTotalFormatters.sumTotalsDollar,
params: { groupFormatterPrefix: 'Total: ', groupFormatterSuffix: ' USD' }
}
```
#### Custom `groupTotalsFormatter`
You can also create a custom `groupTotalsFormatter` similarly to a Formatter, just a create a function that will return a string, for example:
**ViewModel**
```typescript
defineGrid() {
this.columns = [
{
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 '';
}
```
#### Set a Grouping
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
**ViewModel**
```ts
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} (${g.count} items)`;
},
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
});
}
```
#### Clear Grouping / Collapse All / Expand All
To "Clear all Grouping", "Collapse all Groups" and "Expand all Groups", we can simply call the associated `DataView` function, like so:
**ViewModel**
```ts
clearGrouping() {
this.dataviewObj.setGrouping([]);
}
collapseAllGroups() {
this.dataviewObj.collapseAllGroups();
}
expandAllGroups() {
this.dataviewObj.expandAllGroups();
}
```
#### Styling (change icons)
The current icons are chevron (right/down), however if you wish to use +/- icons. You can simply update the SASS variables to use whichever SVG icon paths. The SASS variables you can change are
```scss
$slick-icon-group-color: $slick-primary-color;
$slick-icon-group-expanded-svg-path: "M19,19V5H5V19H19M19,3A2,2 0 0,1 21,5V19A2,2 0 0,1 19,21H5A2,2 0 0,1 3,19V5C3,3.89 3.9,3 5,3H19M11,7H13V11H17V13H13V17H11V13H7V11H11V7Z";
$slick-icon-group-collapsed-svg-path: "M19,19V5H5V19H19M19,3A2,2 0 0,1 21,5V19A2,2 0 0,1 19,21H5A2,2 0 0,1 3,19V5C3,3.89 3.9,3 5,3H19M17,11V13H7V11H17Z";
$slick-icon-group-font-size: 20px;
$slick-icon-group-font-weight: bold;
$slick-icon-group-margin-right: 2px;
/* Grouping Totals Formatter */
$slick-group-totals-formatter-color: gray;
$slick-group-totals-formatter-bgcolor: white;
$slick-group-totals-formatter-font-size: 14px;
$slick-group-totals-formatter-font-weight: normal;
```
For more info on SASS styling and variables, please read the [Wiki - SASS Styling](/slickgrid-universal/styling/styling.md),
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://ghiscoding.gitbook.io/slickgrid-universal/grid-functionalities/grouping-aggregators.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.