Pivot Views

A pivot view allows you to visualise and summarize your tabular data in a more concise way.

Pivots are used to group, aggregate, sort, reorganize, count, total or average your data. It allows users to transform row values into columns and aggregate multiple data entries into a single row.

In AdapTable, pivot views differ from table views in that pivot views have pivotColumns defined (as opposed to columns, which are specified in table views).

Deep Dive

A Note on Terminology

Using pivotColumns

The pivotColumns property needs to be defined, even if it's an empty array. When it's an empty array, the pivot view will only show the group columns and also a column for each aggregation that was configured in the view (via the pivotAggregationColumns property).

Each pivot column needs a columnId property, which is the id of the column to pivot by. The referenced column needs to be available to the view, through either the globalEntities.availableColumns object in state or the view.privateEntities.availableColumns.

Example of a pivot view with no pivot columns, but 2 aggregation columns
const pivotView = {
  id: 'pivotView',
  pivotColumns: [{ columnId: 'language' }],
  pivotAggregationColumns: [{ columnId: 'stars', aggregation: 'sum' }],
};

Note

Unlike columns defined in a table view, which have an id property, pivot columns have a columnId - this is because the pivot columns are generated - one for each unique value in the referenced column. Therefore, pivot columns have dynamic ids - hence the columnId property which is used to tell Adaptable what is the source for the pivoted values.

A pivot column can have any other property a normal table-view column can have:

  • label
  • width
  • flex
  • sortable
  • visible
  • minWidth
  • maxWidth

... and others. See the Working with Columns page for the definitive list.

A Pivot View demo with aggregation and grouping
Fork

This demo has one item in the pivotColumns array, namely the language column. This means for each language value in the dataset, there will be a column group generated in the pivot view.

In addition, there is one aggregation configured - the stars column is aggregated with the sum function.

Finally, we have one group column, which groups by the license column.

Note

The array in view.pivotColumns can also be empty. In this case, no columns and column groups will be generated in the DataGrid. However, the pivot view will have one column for each aggregation defined in the view.pivotAggregationColumns array. The example below demonstrates this.

Same example as above but with no pivot columns
Fork

This demo has the same grouping as above, by license.

Two aggregations are configured - the stars and closed_issues_count columns are aggregated with the sum function.

The main difference from the previous example is that the pivotColumns array is empty.

Using aggregation columns - via pivotAggregationColumns

Aggregation columns are somewhat similar to pivot columns, in that they are generated columns. Each aggregation column you define will have a columnId that references the column to aggregate (the columnId is a reference to an existing column defined in the globalEntities.availableColumns object).

Aggregation columns are generated columns: for each of the pivot column values a column group is created, and each column group will have all the aggregation columns defined in the view.

So an aggregation column won't have a stable id, but will be dynamic - therefore it doesn't have an id property, but rather a columnId property.

Note

When there are zero pivotColumns, there will be only as many aggregations columns generated as they are defined - since in this scenario, there are no pivot column values.

Example of a pivot view with no pivot columns, but 2 aggregation columns
const pivotView = {
  id: 'pivotView',
  pivotColumns: [],
  pivotAggregationColumns: [
    {
      columnId: 'stars',
      aggregation: 'sum',
      label: 'GH Stars (sum)',
    },
    {
      columnId: 'closed_issues_count',
      aggregation: 'sum',
      label: 'Closed Issues (sum)',
    },
  ],
};

When there are pivotColumns defined, for each pivot column value, there will be a column group created, and each column group will have all the aggregation columns defined in the view.

Example of a pivot view with 1 pivot columns and 2 aggregation columns
const pivotView = {
  id: 'pivotView',
  pivotColumns: [
    {
      columnId: 'license',
    },
  ],
  pivotAggregationColumns: [
    {
      columnId: 'price',
      label: 'Price',
      aggregation: 'sum',
    },
    {
      columnId: 'price',
      label: 'Price',
      aggregation: 'avg',
    },
  ],
};

Each value in the license field in the example above, will have a corresponding column group, and each column group will have 2 aggregation columns - one for the sum of the price, and one for the average of the price.

To summarise, a pivot aggregation column can have the following properties:

  • id - the id of the group column - mandatory

  • label - the label of the group column - optional

  • aggregation - the aggregation to use - mandatory. Current available values are:

    • for dataType = number
      • sum
      • avg
      • min
      • max
      • count
      • distinct
    • for dataType = text
      • count
      • distinct
Example with one pivot column and 2 aggregation columns
Fork

The example has the following configuration

  • one pivot column defined - the language column.
  • two aggregations are configured - the stars and closed_issues_count columns are aggregated with the sum function.
  • one group column - group by license

Using group columns - via pivotGroupColumns

For defining grouping in pivot views, you need to use the pivotGroupColumns property, which will be an array of columns, each with an id property (this is the id that will be used for the respective group column).

Besides the id propety, a group column in pivot view needs to have a groupBy property (either a string or an array, just like group columns in table views), which references the column(s) to group by. So the values in the groupBy need to be columns already defined in the globalEntities.availableColumns object (or in the view.privateEntities.availableColumns).

Find Out More

See Defining Row Groups for more details on the definition of a group column. Group columns work the same in both table and pivot views.

Grouping with multiple individual group columns
const pivotGroupColumns = [
  {
    id: 'license-group';
    label: 'Type of License';
    groupBy: 'license';
  },
  {
    id: 'x';
    label: 'X';
    groupBy: 'language';
  },
];
Grouping with a single group columns that groups by multiple fields
const pivotGroupColumns = [
  {
    id: 'multi-group';
    label: 'Group by license and language';
    groupBy: ['license', 'language'];
  },
];

To summarise, a pivot group column can have the following properties:

  • id - the id of the group column - mandatory
  • groupBy - the column(s) to group by (string or string[]) - mandatory
  • label - the label of the group column - optional

Column totals

You can use showTotalColumns to decide whether and where to show column totals. Available options are:

  • false - don't show column totals
  • 'start' - show column totals at the start
  • 'end' - show column totals at the end

Default value is 'end'.

Note

This is only used if there are at least 2 pivot columns defined.

Pivot with showTotalColumns = start
Fork

There are two pivot columns - language and has_pages - and the showTotalColumns property is set to 'start'.

PreviousTable ViewsNextWorking with Columns