AdapTable Version:

Framework:

Pivot Layouts

Summary

Pivot Layouts enable AG Grid instances with Pivoting to be defined
All elements of pivoting are supported - Pivot Columns, Row Groups and Aggregations
Pivot Layouts can also include Format Column styles
Pivot Layouts can be defined at design-time through Initial Adaptable State, or created at runtime in the UI Wizard

AdapTable provides full Pivoting support through Pivot Layouts.

These are Layouts which define pivoting requirements and will open AG Grid in pivot mode.

Caution

Do not set pivotMode to true in GridOptions; instead define a Pivot Layout and AdapTable will take care of the rest

They will automatically display any provided pivot, row-grouping and aggregation columns.

Pivot Layouts can be provided in 2 ways:

by developers at design-time using Layout Initial State
by run-time users using the AdapTable Layout UI Wizard

Understanding Pivot Layouts

Pivot Layouts display aggregated or summarised data.

Users can "slice and dice" their data, ie. configure rows and columns differently to perform aggregations.

When using Pivot Layouts, large amounts of data can be reduced to a compact consumable view, so grid users can view only those dimensions (and data) relevant at any particular point of time.

Hint

Pivot Grids are called this because, essentially the data is "pivoted" or rotated
In other words data, which in Table Layouts would be viewed as part of a row, is pivoted to form a Column

Pivoting in AG Grid

AG Grid's Pivoting capabilities are best of class, offering a full and powerful set of pivot-related features.

To understand how pivoting works in AG Grid it is worth distinguishing between 2 types of Columns:

Columns which exist in the ColDefs and which configure the pivoting (and definable in a Pivot Layout)
Columns dynamically generated by AG Grid when pivoting (i.e. absent from ColDefs and Pivot Layouts)

There are 3 sets of 'configurable' columns provided by AG Grid that each creates generated columns.

Note

Each of these Columns are fully supported by AdapTable and available in Pivot Layouts

PivotAggregationColumns contain the values which are aggregated and displayed in Pivot Result Columns
PivotColumns generate Pivot Column Groups for each unique value they contain
PivotGroupedColumns generate Pivot Row Grouped Columns with a row for each distinct column value

Note

A Pivot Result Column is generated for each unique permutation of Pivot Column value & Aggregation Column
If there are no Pivot Columns, then no Pivot Column Groups are created (but Pivot Result Columns still display)

This table shows how AG Grid configures the 3 Pivot-related Column types in ColDefs, the columns they generate, the Columns Tool Panel section they appear in and the property required to make them "draggable".

AdapTable Pivot Layout	Generated Columns	Tool Panel Section	"Draggable" prop	ColDef Prop
`PivotAggregationColumns`	Pivot Result Columns	Values	`enableValue`	`aggFunc`
`PivotColumns`	Pivot Column Groups	Column Labels	`enablePivot`	`pivot`
`PivotGroupedColumns`	Pivot Row Grouped Columns	Row Groups	`enableRowGroup`	`rowGroup`

Pivot Layouts

Fork Fork

This Demo creates 2 Pivoting Layouts (as well as a Table Layout) using the 3 types of Columns described above:
Pivot Cols Layout - which is set as the CurrentLayout - and has the following features:
- 1 PivotGroupedColumn: Language - generates the Pivot Row Grouped Column named Group
- 1 PivotColumn: License - AG Grid generates 3 Pivot Column Groups (BSD 3-Clause, MIT Licence & Other) to map the column's 3 unique values
- 2 PivotAggregationColumns: Github Stars & Github Watchers with sum & count aggFuncs; AG Grid generates 6 Pivot Result Columns (1 for each column in the 3 Pivot Column Groups)
Pivot Sum Layout - which has the following features:
- 1 PivotGroupedColumn: License
- 3 PivotAggregationColumns: Github Stars, Github Watchers and Total - all using sum aggregation (note: Total is an aggregatable Calculated Column)
- No PivotColumns: Pivot Layouts do not need to include pivot columns

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Pivoted Layouts',
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'Total',
          Query: {
            ScalarExpression: '[github_watchers] + [github_stars]',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Aggregatable: true,
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Pivot Cols Layout',
      Layouts: [
        {
          Name: 'Pivot Cols Layout',
          PivotColumns: ['license'],
          PivotGroupedColumns: ['language'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'count',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
          ColumnSizing: {
            week_issue_change: {Width: 475},
          },
        },
        {
          Name: 'Pivot Sum Layout',
          PivotColumns: [],
          PivotGroupedColumns: ['license'],
          SuppressAggFuncInHeader: true,
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'Total',
              AggFunc: 'sum',
            },
          ],
        },
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'github_stars',
            'language',
            'github_watchers',
            'updated_at',
            'description',
            'created_at',
            'has_wiki',
            'pushed_at',
            'open_issues_count',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

Defining Pivot Layouts

Pivot Layouts can be provided in 2 main ways:

configured by developers at design-time using Pivot Layout Initial Adaptable State
created or edited by run-time users using the Pivot Layout UI Wizard

Opening Pivot Cells as Tables

AdapTable allows any cell in a Pivot Layout to be viewed in a Table.

This is done via a Context Menu Item entitled Expand Aggregated Value.

Note

The menu item appears in all Pivot Layout Cells, dynamically created Pivot Result Columns and Aggregation Columns

Selecting this Menu Item will open a Table that shows all the cells that make up the pivot cell (e.g. which row groups, pivot columns etc have been selected).

Hint

Use the pivotPreviewColumns property in Layout Options to set which other columns will be displayed in the table

Expanding Aggregated Values

(Recorded with AdapTable v13.1)

Pivot Layouts - Expand Cells

Fork Fork

This example shows how to expand a Pivot Cell to see Aggregated Values
It provides 4 different Layouts to show different combinations of Pivot Columns and Row Groups
In each Layout 2 columns are aggregated: GitHub Stars and Github Watchers
The 4 self-descriptive Layouts are:
- NO Pivot Cols - NO Row Groups
- NO Pivot Cols - YES Row Groups
- YES Pivot Cols - NO Row Groups
- YES Pivot Cols - YES Row Groups
We provided the pivotPreviewColumns property in Layout Options to add the Name column to the table that is generated

Try It Out

Switch between all 4 Layouts and in each one right-click on a Pivot Cell and select Expand Aggregated Value to see the different tables produced

import {
  AdaptableOptions,
  PivotPreviewColumnsContext,
} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Pivot Layouts Expand Cells',
  layoutOptions: {
    pivotPreviewColumns: (context: PivotPreviewColumnsContext) => {
      return ['name'];
    },
  },
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'NO Pivot Cols - NO Row Groups',
      Layouts: [
        {
          Name: 'NO Pivot Cols - NO Row Groups',
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
          PivotColumns: [],
        },
        {
          Name: 'NO Pivot Cols - YES Row Groups',
          PivotGroupedColumns: ['language'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
          PivotColumns: [],
        },
        {
          Name: 'YES Pivot Cols - NO Row Groups',
          PivotColumns: ['license'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
        },
        {
          Name: 'YES Pivot Cols - YES Row Groups',
          PivotColumns: ['license'],
          PivotGroupedColumns: ['language'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
        },
      ],
    },
  },
};

Total Rows and Columns

Pivot Layouts (like Table Layouts) support Grand Total Rows.

These rows can be placed at top or bottom of the Grid, and display the total of all Aggregations.

Additionally Pivots can be provided with 3 Pivot Total Columns

Server-Side Row Model

All the examples in this section (like most on this site) use AG Grid's Client Side Row Model.

It is possible to access pivoting in AdapTable and AG Grid while using the Server-Side Row Model.

However it falls upon the developers to provide all the pivoted data themselves as well as the associated, often quite complex, logic.

Caution

This includes providing all the "dynamic" columns when a Column is pivoted
This is not a straightforward task and we recommend only using Server-Side Row Model when absolutely required

Find Out More

See Server-Side Row Model Developer Guide: Pivoting for full details and a working example

FAQ

Can we use Cell Summaries in Pivot Columns? Yes, this was added in AdapTable 20 and is operable in both Pivot Result Columns and Aggregation Columns

Can we save the display order of Pivot Result Columns? No, this is not possible. You can set which Columns will create Pivot Result Columns but not the Pivot Result Columns. AG Grid dynamically generates these using the Column's distinct values, so they can vary each time the Layout loads.

PreviousTechnical Reference NextDefining Pivot Layouts

Framework: