AdapTable Version:

Framework:

Pivot Total Columns

Summary

AdapTable provides 3 Pivot Total Columns:
- Pivot Grand Total
- Pivot Column Total
- Pivot Aggregation Total

AdapTable provides Pivot Total Columns for use in AG Grid pivoting.

These are special columns which allow users to analyze data through different levels of aggregation.

There are 3 different types of Pivot Total Columns available, all of which can be defined, customised and positioned before or after the associated pivot data.

Column	Description
Pivot Grand Total	Provides a summary view of all pivot data
Pivot Column Total	Inserts subtotal columns for each Pivot Column Group
Pivot Aggregation Total	Totals each Pivot Result column value in each Pivot Column Group separately

Caution

Pivot Column Totals cannot be combined with Pivot Aggregation Total Columns in the same Layout

Hint

In addition, AdapTable provides Grand Total Rows which display the totals for all Aggregated Cells

Pivot Grand Total

Pivot Grand Total Columns provide a summary view of all pivot aggregation data.

One Pivot Grand Total Column is displayed for each Aggregation Column defined in the Pivot Layout, showing the total aggregation for that column.

Note

The Column calculates totals using the specified aggregation function for each column independently

Pivot Grand Totals

Fork Fork

This example contains 2 Layouts which each contain aggregations for the Gold, Silver and Bronze columns
Both Layouts are configured to display a Pivot Grand Total Column, so that 3 are provided in total, one for each aggregation
The Grand Total Before Layout displays the Pivot Grand Totals before the Pivot Result Columns, and the Grand Total After shows it at the end

Pivot Column Total

Pivot Column Totals are subtotal columns inserted for each Pivot Column Group.

AdapTable calculates each group-specific total using a common aggregation function.

Caution

All Aggregation Columns must use the same aggFunc (e.g. sum )

Pivot Column Totals

Fork Fork

Like the demo above, this example also includes 2 Layouts which each contain aggregations for the Gold, Silver and Bronze columns
Both Layouts are configured to display Pivot Column Totals - in one Layout the columns are placed at the start of each Group, and in the other Layout at the end
Note: a Pivot Column Total is displayed for each Pivot Column Grouping, e.g. for Athletics, Athletics/2000, Athletics/2004, Basketball, Basketball/2000 etc.

Pivot Aggregation Total

Pivot Aggregation Totals calculate and display totals for each Pivot Result Column within each Pivot Column Group independently.

The Pivot Aggregation Total Columns use column-specific aggregation functions.

Pivot Aggregation Totals can be configured in 2 ways:

per Layout
per specific Pivot Column groups

Per Layout Aggregation

Pivot Aggregation Totals can be configured so they are the same for each Pivot Result Column produced by the Aggregation.

Pivot Aggregation Totals Layout

Fork Fork

Like the demos above, this example also includes 2 Layouts which all contain aggregations for the Gold, Silver and Bronze columns
Both Layouts display Pivot Aggregation Total Columns for the whole Column:
- Aggregation Total Standard Layout shows Pivot Aggregation Totals for all 3 Aggregation Columns - Gold and Bronze before the Column and Silver after the Column
- Aggregation Total Selective Layout shows Pivot Aggregation Totals for Gold (before) and Bronze (after) (but no Aggregation Total for Silver)

Per Pivot Column Aggregation

Pivot Aggregation Totals can also be configured on a per Pivot Result Column basis.

In other words, users can decide which Pivot Columns will show a Total (and the Column's position).

For example, to show Pivot Aggregation Total Columns only for gold with sport you can provide:

PivotColumns: ['sport', 'year'],
PivotAggregationColumns: [{
    ColumnId: 'gold',
    AggFunc: 'sum',
    Total: [{
        PivotColumnId: 'sport',
        ShowTotal: 'before'
    }]
}]

Pivot Aggregation Totals Pivot Column

Fork Fork

This example has a Layout which contains aggregations for the Gold, Silver and Bronze columns - but with Pivot Aggregatation Totals configured on a per-Column basis:
- Gold / Sport - before
- Gold / Year - none
- Silver / Sport - no Aggregation column configured (so no Pivot Total possible either)
- Silver / Year - true (i.e. before)
- Bronze / Sport - false
- Bronze / Sport - after

Positioning

All 3 Pivot Total Types share the same ability to position the Column, dynamically generated by AdapTable, before or after the relevant pivot data.

They each use the PivotTotalPosition object which can receive 4 arguments:

Column	Pivot Grand Total	Pivot Column Total	Pivot Aggregation Total
`before`	Before all Pivot Result Columns	Before each Pivot Column Group	Before each Aggregation
`after`	After all Pivot Result Columns	After each Pivot Column Group	After each Aggregation
`true`	same as `before`	same as `before`	same as `before`
`false`	Not displayed	Not displayed	Not displayed

Formatting & Styling

All Pivot Total columns automatically inherit the Column Formatting & Styling of their source value columns.

Hint

However custom formats can be provided targeting specific Pivot Total Columns, overriding the default inheritance

Total Columns Formatting Inheritance

Fork Fork

This example applies Column Styling to the Gold column as seen in the Standard Pivot Layout
But we also see this applied in the Pivot Totals Layout Layout which has PivotGrandTotal and PivotColumnTotal both set to true

Column Types

AdapTable provides a type property value for each Pivot Total.

This allows to to set the Column Scope for the Format to all Columns of a particular type.

These column types are available via the AdaptableColumnType as follows:

AdaptableColumnType Value	Pivot Total Column	Value for `type` property
`PivotGrandTotal`	Pivot Grand Total	`pivotGrandTotal`
ּ`PivotColumnTotal`	Pivot Column Total	`pivotColumnTotal` ּ
ּ`PivotAggregationTotal`	Pivot Aggregation Total	`pivotAggregationTotal` ּ
`PivotAnyTotal`	[All 3 Pivot Total types]	`pivotAnyTotal`

Find Out More

See the Developer Guide to using Column Types for further details

Deep Dive

Registering Column Types for use in the UI

Column types will always work when creating items via code or when setting Initial State.

However to make them also available in the UI wizard and associated dialoges, another step is required.

Thes column types need to be registered for UI availability by using the columnTypes property in Column Options.

columnOptions: {
    columnTypes: [
        'github', 
        AdaptableColumnType.PivotGrandTotal, 
        AdaptableColumnType.PivotColumnTotal,
        AdaptableColumnType.PivotAggregationTotal,
        AdaptableColumnType.PivotAnyTotal
    ],
  },

Total Columns Formatting Column Types

Fork Fork

In this example we provide Column Styling where we change the Back Colour for 3 Column Types:
- PivotGrandTotal - light blue - as seen in both Pivot Aggregation Layout and Pivot Cols Layout
- PivotColumnTotal - light green - as seen in Pivot Cols Layout
- PivotAggregationTotal - pink - as seen in Pivot Aggregation Layout

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {IOlympicData} from './rowData';
import {AdaptableColumnType} from '@adaptabletools/adaptable';

export const adaptableOptions: AdaptableOptions<IOlympicData> = {
  primaryKey: 'athlete',
  adaptableId: 'Total Column Formatting Column Types',
  columnOptions: {
    columnTypes: [
      AdaptableColumnType.PivotGrandTotal,
      AdaptableColumnType.PivotColumnTotal,
      AdaptableColumnType.PivotAggregationTotal,
    ],
  },
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-PivotGrandTotal',
          Scope: {
            ColumnTypes: [AdaptableColumnType.PivotGrandTotal],
          },
          Style: {
            BackColor: 'LightBlue',
            ForeColor: 'Black',
            FontStyle: 'Italic',
          },
        },
        {
          Name: 'formatColumn-PivotColumnTotal',
          Scope: {
            ColumnTypes: [AdaptableColumnType.PivotColumnTotal],
          },
          Style: {
            BackColor: 'LightGreen',
            ForeColor: 'Black',
            FontStyle: 'Italic',
          },
        },
        {
          Name: 'formatColumn-PivotAggregationTotal',
          Scope: {
            ColumnTypes: [AdaptableColumnType.PivotAggregationTotal],
          },
          Style: {
            BackColor: 'Pink',
            ForeColor: 'Black',
            FontStyle: 'Italic',
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Pivot Aggregation Layout',
      Layouts: [
        {
          Name: 'Pivot Aggregation Layout',
          PivotColumns: ['sport', 'year'],
          PivotGrandTotal: 'before',
          PivotGroupedColumns: ['country'],
          PivotAggregationColumns: [
            {
              ColumnId: 'gold',
              AggFunc: 'sum',
              Total: true,
            },
            {
              ColumnId: 'silver',
              AggFunc: 'sum',
              Total: true,
            },
            {
              ColumnId: 'bronze',
              AggFunc: 'sum',
              Total: true,
            },
          ],
        },
        {
          Name: 'Pivot Cols Layout',
          PivotColumns: ['sport', 'year'],
          PivotGrandTotal: 'before',
          PivotGroupedColumns: ['country'],
          PivotColumnTotal: true,
          PivotAggregationColumns: [
            {
              ColumnId: 'gold',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'silver',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'bronze',
              AggFunc: 'sum',
            },
          ],
        },
      ],
    },
  },
};

Formatting Pivot Column Totals

AdapTable allows you to choose to format a Pivot Column Total based on its expanded / collapsed state.

This is done by leveraging 2 functionalities in AdapTable:

Column Types - see section above
Ability to format Column Groups when expanded / collapsed

Formatting Pivot Column Totals

Fork Fork

In this example we provide Column Styling for PivotColumnTotal (we change the Back Colour to light green)
However we set the ColumnGroupScope to "Collapsed" so the Style is only visible when the Group is Collapsed

Try It Out

Collapse a Pivot Column Total (e.g. 'Athletics') and see how the green Format Column style is rendered

Customising Total Column Headers

The Column Header (i.e. Caption) displayed for the Pivot Total Columns is also customisable.

This is done using the columnHeader property in Column Options which allows customisation on a per Column Type, or individual Column, basis if required.

Find Out More

See the Developer Guide to configuring Column Headers for further details

Totals Columns Customising Header

Fork Fork

In this example we customise headers for the Pivot Total Columns by providing an implementation for the columnHeader property in Column Options
We add 'PGT' to Pivot Grand Totals and 'PCT' to Pivot Column Totals (see Pivot Grand Totals Layout) and 'PAT' to Pivot Aggregation Totals (see Pivot Aggregation Totals Layout)
Note: We provided 2 Layouts because you cannot have Pivot Column Totals and Pivot Aggregation Totals in the same Layout

Updating Total Columns at Runtime

The Pivot Totals configuration can be updated at run-time in 2 ways:

in the Pivot Aggregations section of the Layout Wizard
by updating the Layout programatically via the Layout API

Updating Pivot Total Columns

Fork Fork

This example provides a Custom Dashboard Button that flips the Pivot Grand Total Column to before and after the Pivot Result columns
It does this by leveraging the updateCurrentLayout function in Layout API to Update the Layout programatically

Framework: