# Filtering Pivot Layouts

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

- Both Column Filters and the Grid Filter can be added to Pivot Layouts

[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).
Table layouts store filters the same way — [Other Table Layout Properties](https://www.adaptabletools.com/docs/handbook-layouts-table-grouping-filtering/index.md).

In AdapTable, [Column Filters](https://www.adaptabletools.com/docs/handbook-column-filter/index.md) and the [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md) are always associated with a specific Layout.

- AdapTable provides very advanced Filtering with many configuration options and UI Components available
- See the sections on [Column Filters](https://www.adaptabletools.com/docs/handbook-column-filter/index.md) and the [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md) for more details

## Column Filters

Column Filters can be applied to any of the 3 main elements of a Pivot Layout:
- Pivot Columns
- Pivot Aggregation Columns
- Pivot Row Grouped Columns

### Defining a Pivot Layout With Column Filters

Add the main properties nearly all Pivot Layouts require

Pivot Layouts can include [Column Filters](https://www.adaptabletools.com/docs/handbook-column-filter/index.md) 

These can be added to any of the 3 main elements of a Pivot Layout:
- PivotColumns
- PivotGroupedColumns
- PivotAggregationColumns

```ts [[2, 14, "ColumnFilters"]]
// Provide Column Filters on 3 Columns
// Github Stars - PivotAggregationColumn
// Language - PivotColumn
// License - PivotGroupedColumn
const initialState: InitialState = {
  Layout: {
    CurrentLayout: 'Pivot Layout',
    Layouts: [
    {
      Name: 'Pivot Layout',
      PivotColumns: ['language'],
      PivotGroupedColumns: ['license'],
      PivotAggregationColumns: [{ ColumnId: 'github_stars', AggFunc: 'sum', }],
      ColumnFilters: [
      {
        ColumnId: 'github_stars', // Pivot Aggregation Column
        Expression: '[github_stars] > 5000',
      },
      {
        ColumnId: 'language', // Pivot Column
        Expression: 'CONTAINS([language], "Script")',
      },
      {
        ColumnId: 'license', // Pivot Grouped Column
        Expression: '[license] IN ("MIT License", "Other")',
      }],
     }],
  },
}
```

### Pivot Aggregation Columns

Column Filters can be attached to Pivot Aggregation Columns and they will display normally.

Additionally, AdapTable automatically applies the Aggregation Column filter to **all** associated dynamic Pivot Result Columns.

In other words if you add a Filter to an aggregation column and then you create a Pivot Column, the Column Filter will be applied automatically to every Pivot Result Column that uses that aggregation.

- You will only see the Filter details in the Aggregation Column but not in any derived Pivot Result Columns
- This is because there is also the possibility to create Filters Directly on Pivot Result Columns - see below

**Example: Pivot: Column Filtering**

Applying Column Filtering when Pivoting

 

- In this example we provide 2 Layouts which both have Filters applied:
- `Pivot Aggregation Filter Layout` - has only aggregations and no Pivot Columns - and 2 Column Filters:
  - `Language` - a PivotGroupedColumn - `NotContains` "Script"
  - `GitHub Stars` - an Aggregation Column - `GreaterThan` 2000
- `Pivot Column Filter Layout` - has both aggregations and a Pivot Column (Language) - and 3 Column Filters:
  - `Language` - a PivotGroupedColumn - `NotContains` "Script"
  - `License` - a Pivot Column - In "MIT License", "Other"
  - `GitHub Stars` - an Aggregation Column - `GreaterThan` 2000
- Note: We see the details of the Filters in `Pivot Aggregation Filter Layout` as they are just Aggregations but not in the `Pivot Column Filter Layout`
 

- In the `Pivot Column Filter Layout`, remove `Language` from Column Labels in Column Tool Panel (i.e. make it an *Aggregation Only* Layout) and note how `Github Stars` is directly filtered
- Clear the Github Stars Column Filter in the Filters Toolbar in the Dashboard and note how the data changes

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
  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: Column Filtering',
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout', 'ColumnFilter']}],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Pivot Aggregation Filter Layout',
      Layouts: [
        {
          Name: 'Pivot Aggregation Filter Layout',
          PivotColumns: [''],
          PivotGroupedColumns: ['license'],
          SuppressAggFuncInHeader: true,
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
          ColumnFilters: [
            {
              ColumnId: 'language',
              Expression: 'NOT (CONTAINS([language], "Script"))',
            },
            {
              ColumnId: 'github_stars',
              Expression: '[github_stars] > 20000',
            },
          ],
        },
        {
          Name: 'Pivot Column Filter Layout',
          PivotColumns: ['language'],
          PivotGroupedColumns: ['license'],
          SuppressAggFuncInHeader: true,
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
          ColumnFilters: [
            {
              ColumnId: 'language',
              Expression: 'NOT (CONTAINS([language], "Script"))',
            },
            {
              ColumnId: 'license',
              Expression: '[license] IN ("MIT License", "Other")',
            },
            {
              ColumnId: 'github_stars',
              Expression: '[github_stars] > 20000',
            },
          ],
        },
      ],
    },
  },
};
```
**`src/onAdaptableReady.ts`**

```ts
import {AdaptableReadyInfo} from '@adaptabletools/adaptable';

export const onAdaptableReady = ({agGridApi}: AdaptableReadyInfo) => {
  agGridApi.openToolPanel('columns');
};
```
**`src/gridOptions.ts`**

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

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

export const gridOptions: GridOptions = {
  theme: themeBalham,
  defaultColDef: {
    resizable: true,
    sortable: true,
    editable: true,
    filter: true,
    floatingFilter: true,
  },
  autoGroupColumnDef: {
    floatingFilter: true,
    filter: 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

In the demo above we filtered on the Github Stars PivotAggregation Column, and that Column Filter was applied to all its associated Pivot Result Columns.

However it is possible to filter only on specific, named, [Pivot Result Columns](https://www.adaptabletools.com/docs/handbook-layouts-pivot-result-columns/index.md).

This is done by configurig the Filter using AG Grid’s autogenerated ColumnId.

Any Column Filters applied by Run-time users to a Pivot Result Column will be persisted using that `ColumnId`

**Example: Pivot Result Columns: Filtering**

Applying Column Filtering in Pivot Result Columns

 

- In this example we have created 2 Column Filters on Pivot Result Columns in the Layout Definition (both using AG Grid's autogenerated `ColumnId`)
  - `pivot_language_HTML_github_stars` where it is `GreaterThan` 20,000
  - `pivot_language_JavaScript_github_watchers` where it is `GreaterThan` 250
  

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 Result Columns: Filtering',
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'ColumnFilter'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Pivot Layout',
      Layouts: [
        {
          Name: 'Pivot Layout',
          PivotColumns: ['language'],
          PivotGroupedColumns: ['license'],
          SuppressAggFuncInHeader: true,
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
          ],
          ColumnFilters: [
            {
              ColumnId: 'pivot_language_JavaScript_github_watchers',
              Expression: '[pivot_language_JavaScript_github_watchers] > 250',
            },
            {
              ColumnId: 'pivot_language_HTML_github_stars',
              Expression: '[pivot_language_HTML_github_stars] > 20000',
            },
          ],
        },
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'popularity',
            'license',
            'open_issues_count',
            'created_at',
            'has_wiki',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

## Grid Filter

The [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md) is a cross-column filter applied across the whole Grid.

### Defining a Pivot Layout With Grid Filter

Add the 4 main properties nearly all Pivot Layouts require:
- Name
- PivotColumns
- PivotGroupedColumns
- PivotAggregationColumns

Pivot Layouts can also include the Grid Filter.

This is a Boolean Expression evaluated by AdapTableQL.

```ts [[2, 11, "GridFilter"]]
// Provide a Grid Filter
const initialState: InitialState = {
  Layout: {
    CurrentLayout: 'Pivot Layout',
    Layouts: [
    {
      Name: 'Pivot Layout',
      PivotColumns: ['language'],
      PivotGroupedColumns: ['license'],
      PivotAggregationColumns: [{ ColumnId: 'github_stars', AggFunc: 'sum', }],
      GridFilter: {
        Expression: '[currency]="EUR" OR [price] > 5000',
      },
    }],
  },
}
```
