# Formatting Pivot Layouts

Canonical page: https://www.adaptabletools.com/docs/handbook-layouts-pivot-formatting

- 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

[Layouts](https://www.adaptabletools.com/docs/handbook-layouts/index.md) · [Pivot Layouts](https://www.adaptabletools.com/docs/handbook-layouts-pivot/index.md) · [All Pivot Layout Topics](https://www.adaptabletools.com/docs/handbook-layouts-pivot/index.md#pivot-layout-topics).
Format and style rules — [Format Columns](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md) and [Styled Columns](https://www.adaptabletools.com/docs/handbook-styled-column-overview/index.md).

AdapTable ensures that [Column Formatting and Styling](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md) 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).

**Example: Pivot - Formatting Aggregation Columns**

Adding Styles and Column Formatting to Pivot Aggregation Columns

 

- This demo shows [Format Columns](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md) in Pivoting at its most basic.  We apply 3 Format Columns definitions (2 with [Expression-based Conditions](https://www.adaptabletools.com/docs/handbook-column-formatting-conditions/index.md)):
  - To render cells in the `Open Issues` column red with white font where they > 35
  - 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](https://www.adaptabletools.com/docs/handbook-column-formatting-adaptable-style/index.md) 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

- 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

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
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: '[github_stars]> 9000',
          Style: {
            BackColor: 'var(--ab-color-palette-21)',
            ForeColor: 'var(--ab-color-palette-17)',
          },
          Scope: {
            ColumnIds: ['github_stars'],
          },
        },
        {
          Name: 'formatColumn-open_issues_count',
          Rule: '[open_issues_count] > 35',
          Style: {
            BackColor: 'var(--ab-color-palette-30)',
            ForeColor: 'var(--ab-color-palette-14)',
          },
          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,
        },
      ],
    },
  },
};
```

**`src/gridOptions.ts`**

```ts
import {ColDef, GridOptions, themeBalham} from 'ag-grid-enterprise';

import {rowData, WebFramework} from './rowData';
import {columnDefs} from './columnDefs';

export const gridOptions: GridOptions<WebFramework> = {
  theme: themeBalham,
  defaultColDef: {
    resizable: true,
    sortable: true,
    editable: true,
    filter: true,
    floatingFilter: true,
  },
  columnDefs: columnDefs,
  rowData: rowData,
  sideBar: true,
  suppressMenuHide: true,
  cellSelection: true,
  statusBar: {
    statusPanels: [
      {statusPanel: 'agTotalRowCountComponent', align: 'left'},
      {statusPanel: 'agFilteredRowCountComponent'},
      {
        key: 'Center Panel',
        statusPanel: 'AdaptableStatusPanel',
        align: 'center',
      },
    ],
  },
};
```

## Pivot Result Columns

[Pivot Result Columns](https://www.adaptabletools.com/docs/handbook-layouts-pivot-result-columns/index.md) 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.

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

**Example: Pivot - Formatting Result Columns**

Applying Column Formatting and Styling to Pivot Result Columns

 

- In this example we have created 2 [Format Columns](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md) 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](https://www.adaptabletools.com/docs/handbook-column-formatting-display-format-number/index.md) with Prefix and Integer Digits
  - `pivot_language_JavaScript_github_stars` has a [Column Formatting Style](https://www.adaptabletools.com/docs/handbook-column-formatting-adaptable-style/index.md) applied
- For this reason, unlike the demo above, the Format Columns can be seen in `Pivot Cols Layout` (but not `Pivot Aggregation Layout`)

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
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: 'var(--ab-color-palette-21)',
            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’.

**Example: Pivot - Formatting Row Groups**

Applying Column Formatting and Styling to Pivot Row Grouping Columns

 

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

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

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
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: 'var(--ab-color-palette-28)',
            ForeColor: 'var(--ab-color-palette-14)',
            Alignment: 'Center',
          },
        },
        {
          Name: 'formatColumn-ag-Grid-AutoColumn',
          Scope: {
            ColumnIds: ['ag-Grid-AutoColumn'],
          },
          Style: {
            BackColor: 'var(--ab-color-palette-17)',
            ForeColor: 'var(--ab-color-palette-14)',
            Alignment: 'Center',
          },
        },
      ],
    },
  },
};
```

## Pivot Total Columns

The specially generated Pivot Total Columns can also be formatted.

See [Formatting Pivot Total Columns](https://www.adaptabletools.com/docs/handbook-layouts-pivot-total-columns/index.md#formatting--styling) 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.

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

**Example: Excluding formatting from Pivot Layouts**

Ensuring Styles are excluded from Pivot Layouts

 

- 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

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

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
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: '[github_stars] > 9000',
          Style: {
            BackColor: 'var(--ab-color-palette-21)',
            ForeColor: 'var(--ab-color-palette-17)',
          },
          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: 'var(--ab-color-palette-30)',
          },
          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,
        },
      ],
    },
  },
};
```

**`src/gridOptions.ts`**

```ts
import {ColDef, GridOptions, themeBalham} from 'ag-grid-enterprise';

import {rowData, WebFramework} from './rowData';
import {columnDefs} from './columnDefs';

export const gridOptions: GridOptions<WebFramework> = {
  theme: themeBalham,
  defaultColDef: {
    resizable: true,
    sortable: true,
    editable: true,
    filter: true,
    floatingFilter: true,
  },
  columnDefs: columnDefs,
  rowData: rowData,
  sideBar: true,
  suppressMenuHide: true,
  cellSelection: true,
  statusBar: {
    statusPanels: [
      {statusPanel: 'agTotalRowCountComponent', align: 'left'},
      {statusPanel: 'agFilteredRowCountComponent'},
      {
        key: 'Center Panel',
        statusPanel: 'AdaptableStatusPanel',
        align: 'center',
      },
    ],
  },
};
```
