Filtering Pivot Layouts

Summary

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

In AdapTable, Column Filters and the Grid Filter are always associated with a specific Layout.

Find Out More

AdapTable provides very advanced Filtering with many configuration options and UI Components available
See the sections on Column Filters and the Grid Filter 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

Developer Guide

Defining a Pivot Layout With Column Filters

// 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
        Predicates: [{ PredicateId: 'GreaterThan', Inputs: ['5000']}]
      },
      {
        ColumnId: 'language', // Pivot Column
        Predicates: [{ PredicateId: 'Contains', Inputs: ['Script']}],
      },
      {
        ColumnId: 'license', // Pivot Grouped Column
        Predicates: [{ PredicateId: 'In', Inputs: ['MIT License', 'Other']}],
      }],
     }],
  },
}

1

Provide the main Pivot Properties

Add the main properties nearly all Pivot Layouts require

2

Add Column Filters

Pivot Layouts can include Column Filters

These can be added to any of the 3 main elements of a Pivot Layout:

PivotColumns
PivotGroupedColumns
PivotAggregationColumns

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.

Caution

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

Pivot: Column Filtering

Fork Fork

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

Try It Out

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

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',
              Predicates: [
                {
                  PredicateId: 'NotContains',
                  Inputs: ['Script'],
                },
              ],
            },
            {
              ColumnId: 'github_stars',
              Predicates: [
                {
                  PredicateId: 'GreaterThan',
                  Inputs: [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',
              Predicates: [
                {
                  PredicateId: 'NotContains',
                  Inputs: ['Script'],
                },
              ],
            },
            {
              ColumnId: 'license',
              Predicates: [
                {
                  PredicateId: 'In',
                  Inputs: ['MIT License', 'Other'],
                },
              ],
            },
            {
              ColumnId: 'github_stars',
              Predicates: [
                {
                  PredicateId: 'GreaterThan',
                  Inputs: [20000],
                },
              ],
            },
          ],
        },
      ],
    },
  },
};

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.

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

Note

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

Pivot Result Columns: Filtering

Fork Fork

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

Grid Filter

The Grid Filter is a cross-column filter applied across the whole Grid.

Developer Guide

Defining a Pivot Layout With Grid Filter

// 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)',
      },
    }],
  },
}

1

Provide the main Pivot Properties

Add the 4 main properties nearly all Pivot Layouts require:

Name
PivotColumns
PivotGroupedColumns
PivotAggregationColumns

2

Add Grid Filter

Pivot Layouts can also include the Grid Filter.

This is a Boolean Expression evaluated by AdapTableQL.

PreviousFormatting Pivot Layouts NextSorting Pivot Layouts