Formatting Pivot Layouts

Summary

All the different types of Columns used when pivoting in AG Grid can be formatted and styled by AdapTable:
- Aggregation Columns
- Pivot Result Columns
- Pivot Column Groups
- Pivot Row Grouped Columns
- Pivot Total Columns
It is also possible to exclude pivoting from a given Style or Display Format

AdapTable ensures that Column Formatting and Styling work automatically when AG Grid pivoting is applied.

Formatting is applied for all the different types of Columns created when Pivoting.

Pivot Aggregation Columns

Providing Formatting to an Aggregated Column will style not only that column but also each associated Pivot Result Column that is created for it.

For example, if we provide a Format for a "Price" Column, that will be applied each time Price is displayed in the Grid as a Pivot Result Column (potentially multiple times based on the Pivoting applied).

Pivot - Formatting Aggregation Columns

Fork Fork

This demo shows Format Columns in Pivoting at its most basic. We apply 3 Format Columns definitions (2 with conditions):
- A Predicate Conditional Style to render cells in the Open Issues column red with white font where they > 35
- An Expression Conditional Style to render cells in the GitHub Stars column yellow with brown font where they > 9,000
- A Light Blue Italic and Right Alignment Formatting Style for the GitHub Watchers column
We add 3 Layouts and both Formats are fully applied in all of them
- Pivot Aggregation Layout - which has Pivot Aggregations - Github Stars and Github Watchers - but no Pivot Columns (and so no Pivot Result Columns)
- Pivot Cols Layout - we pivot on License which creates 3 sets of Pivot Results Columns - and the formats are applied accordingly
- Table Layout - a standard Table Layout which displays the same Column Formats

Try It Out

Switch from the Standard to the Pivot Layout and see that the Format Columns are still applied
Note that the Standard Layout includes 3 Format Columns, while the Pivot Layout has 2

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Pivot - Formatting Aggregation Columns',
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-github_stars',
          Rule: {
            BooleanExpression: '[github_stars]> 9000',
          },
          Style: {
            BackColor: 'Yellow',
            ForeColor: 'Brown',
          },
          Scope: {
            ColumnIds: ['github_stars'],
          },
        },
        {
          Name: 'formatColumn-open_issues_count',
          Rule: {
            Predicates: [
              {
                PredicateId: 'GreaterThan',
                Inputs: [35],
              },
            ],
          },
          Style: {
            BackColor: 'Red',
            ForeColor: 'White',
          },
          Scope: {
            ColumnIds: ['open_issues_count'],
          },
        },
        {
          Name: 'formatColumn-github_watchers',
          Style: {
            ForeColor: 'LightBlue',
            FontStyle: 'Italic',
            Alignment: 'Right',
          },
          Scope: {
            ColumnIds: ['github_watchers'],
          },
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Pivot Aggregation Layout',
      Layouts: [
        {
          Name: 'Pivot Aggregation Layout',
          PivotColumns: [''],
          PivotGroupedColumns: ['language'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'open_issues_count',
              AggFunc: 'sum',
            },
          ],
        },
        {
          Name: 'Pivot Cols Layout',
          PivotColumns: ['license'],
          PivotGroupedColumns: ['language'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'open_issues_count',
              AggFunc: 'sum',
            },
          ],
        },
        {
          Name: 'Table Layout',
          TableColumns: [
            'name',
            'github_stars',
            'language',
            'github_watchers',
            'open_issues_count',
            'updated_at',
            'created_at',
            'has_wiki',
            'pushed_at',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

Pivot Result Columns

Pivot Result Columns can individually be provided with specific, bespoke, Column Formatting.

This is different to the example above where formatting applied on an aggregation column, automatically included all Pivot Result Columns.

In order to do this, you will need to know the autogenerated id of the Column created by AG Grid.

Note

In practice run-time users create Format Columns in the UI on specific columns which are then persisted in the Layout

Pivot - Formatting Result Columns

Fork Fork

In this example we have created 2 Format Columns on Pivot Result Columns in the Layout Definition (both using AG Grid's autogenerated ColumnId)
- pivot_language_HTML_github_watchers has a Number Display Format with Prefix and Integer Digits
- pivot_language_JavaScript_github_stars has a Column Formatting Style applied
For this reason, unlike the demo above, the Format Columns can be seen in Pivot Cols Layout (but not Pivot Aggregation Layout)

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Pivot - Formatting Result Columns',
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Pivot Cols Layout',
      Layouts: [
        {
          Name: 'Pivot Aggregation Layout',
          PivotColumns: [''],
          PivotGroupedColumns: ['license'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
        },
        {
          Name: 'Pivot Cols Layout',
          PivotColumns: ['language'],
          PivotGroupedColumns: ['license'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-pivot_language_HTML_github_watchers',
          Scope: {
            ColumnIds: ['pivot_language_HTML_github_watchers'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 1,
              Prefix: 'GS ',
            },
          },
        },
        {
          Name: 'formatColumn-pivot_language_JavaScript_github_stars',
          Scope: {
            ColumnIds: ['pivot_language_JavaScript_github_stars'],
          },
          Style: {
            BackColor: '#2966a8',
            ForeColor: 'yellow',
            Alignment: 'Center',
          },
        },
      ],
    },
  },
};

Pivot Columns Groups

It is not possible to Format a specific pivot column group by name.

In other words you cannot set Scope to a Column Group.

Instead, you need to create a Format Column which includes each of the Pivot Result Columns in the Group.

Pivot Row Grouped Columns

It is also possible to Format the Pivot Row Grouped Column - the column created when row grouping in Pivoting.

The way to do this is to set the Column Scope of the Format Column to be ‘ag-Grid-AutoColumn’.

Pivot - Formatting Row Groups

Fork Fork

We provide a style for the Grouped column of white font on brown background.

Try It Out

Click the "Update Layout" button to change the Row Grouped Column in the Layout and note that the Format Style is unchanged

import {
  AdaptableButton,
  AdaptableOptions,
  DashboardButtonContext,
  Layout,
} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Pivot - Formatting Row Groups',
  dashboardOptions: {
    customDashboardButtons: [
      {
        label: 'Update Layout',
        buttonStyle: {
          tone: 'neutral',
          variant: 'outlined',
        },
        onClick: (
          _button: AdaptableButton<DashboardButtonContext>,
          context: DashboardButtonContext
        ) => {
          const currenLayout: Layout =
            context.adaptableApi.layoutApi.getCurrentLayout();
          const columnToGroup = currenLayout.PivotGroupedColumns?.includes(
            'license'
          )
            ? 'has_wiki'
            : 'license';

          context.adaptableApi.layoutApi.updateCurrentLayout(layout => {
            layout.PivotGroupedColumns = [columnToGroup];
            return layout;
          });
        },
      },
    ],
  },
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Pivot Layout',
      Layouts: [
        {
          Name: 'Pivot Layout',
          PivotColumns: ['language'],
          PivotGroupedColumns: ['license'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
        },
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'popularity',
            'license',
            'open_issues_count',
            'created_at',
            'has_wiki',
          ],
          RowGroupedColumns: ['license'],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-license',
          Scope: {
            ColumnIds: ['license'],
          },
          Style: {
            BackColor: 'Purple',
            ForeColor: 'White',
            Alignment: 'Center',
          },
        },
        {
          Name: 'formatColumn-ag-Grid-AutoColumn',
          Scope: {
            ColumnIds: ['ag-Grid-AutoColumn'],
          },
          Style: {
            BackColor: 'Brown',
            ForeColor: 'White',
            Alignment: 'Center',
          },
        },
      ],
    },
  },
};

Pivot Total Columns

The specially generated Pivot Total Columns can also be formatted.

Find Out More

See Formatting Pivot Total Columns for detailed instructions

Excluding Pivoting

By default all relevant Formatting will appear in Pivot Layouts.

It is possible to leverage, the ExcludeGroupRows option in the Format Column's RowScope property to exclude Pivot Layouts from a particular Format or Stlye.

Hint

Set ExcludeGroupRows to true in the Format Column's RowScope property to exclude Pivot Layouts

Excluding formatting from Pivot Layouts

Fork Fork

This demo provides 2 Format Columns that we see in the Pivot Layout - on GitHub Stars and GitHub Watchers columns
However there is a 3rd Format Column (that sets Open Issues to Red) but as this has ExcludeGroupRows set to true it does not appear in the Pivot Layout

Try It Out

Switch from the Pivot to the Table Layout and see the Format Column for the Open Issues column

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Pivot Exclude Formatting',
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-github_stars',
          Rule: {
            Predicates: [
              {
                PredicateId: 'GreaterThan',
                Inputs: [9000],
              },
            ],
          },
          Style: {
            BackColor: 'Yellow',
            ForeColor: 'Brown',
          },
          Scope: {
            ColumnIds: ['github_stars'],
          },
        },
        {
          Name: 'formatColumn-github_watchers',
          Style: {
            ForeColor: 'LightBlue',
            FontStyle: 'Italic',
            Alignment: 'Right',
          },
          Scope: {
            ColumnIds: ['github_watchers'],
          },
        },
        {
          Name: 'formatColumn-open_issues_count',
          Style: {
            ForeColor: 'Red',
          },
          Scope: {
            ColumnIds: ['open_issues_count'],
          },
          RowScope: {
            ExcludeGroupRows: true,
          },
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Pivot Layout',
      Layouts: [
        {
          Name: 'Pivot Layout',
          PivotColumns: ['license'],
          PivotGroupedColumns: ['language'],
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'open_issues_count',
              AggFunc: 'sum',
            },
          ],
        },
        {
          Name: 'Table Layout',
          TableColumns: [
            'name',
            'github_stars',
            'language',
            'github_watchers',
            'open_issues_count',
            'updated_at',
            'created_at',
            'has_wiki',
            'pushed_at',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

PreviousDefining Pivot Layouts NextFiltering Pivot Layouts

Framework: