Pivot Table Overview

The Pivot Table empowers users with the ability to transform multidimensional data into insightful visualizations. It allows for dynamic handling of data fields across rows, columns, and summary areas, all tailored to fit specific business needs.

Source code

1
import { defineCustomElements } from '@revolist/revogrid/loader';
2
defineCustomElements();
3

4
import { PivotPlugin } from '@revolist/revogrid-enterprise';
5
import { AdvanceFilterPlugin } from '@revolist/revogrid-pro';
6
import NumberColumnType from '@revolist/revogrid-column-numeral';
7
import { ECOMMERCE_PIVOT } from '../sys-data/ecommerce.pivot';
8
import type { DataType } from '@revolist/revogrid';
9

10
export function load(parentSelector: string, rows: DataType[]) {
11
  const grid = document.createElement('revo-grid');
12

13
  // Set grid properties
14
  grid.range = true;
15
  grid.resize = true;
16
  grid.filter = true;
17
  grid.colSize = 200;
18
  grid.readonly = true;
19
  grid.theme = 'compact';
20

21
  // Set the data source
22
  grid.source = rows;
23

24
  // Define column types
25
  grid.columnTypes = {
26
    integer: new NumberColumnType(),
27
    currency: new NumberColumnType('$0,0.00'),
28
  };
29

30
  // Define plugins
31
  grid.plugins = [PivotPlugin, AdvanceFilterPlugin];
32

33
  // Set additional data
34
  grid.additionalData = {
35
    pivot: {
36
      ...ECOMMERCE_PIVOT,
37
    },
38
  };
39

40
  // Append the grid to the specified parent element
41
  document.querySelector(parentSelector)?.appendChild(grid);
42
}

1
<template>
2
    <RevoGrid
3
      hide-attribution
4
      range
5
      resize
6
      filter
7
      :colSize="200"
8
      :source="rows"
9
      :additionalData="additionalData"
10
      :theme="isDark ? 'darkCompact' : 'compact'"
11
      :plugins="plugins"
12
      :column-types="columnTypes"
13
      readonly
14
    />
15
</template>
16

17
<script setup lang="ts">
18
import { computed, ref } from 'vue';
19
import { currentThemeVue } from '../composables/useRandomData';
20
const { isDark } = currentThemeVue();
21

22
import NumberColumnType from '@revolist/revogrid-column-numeral';
23
import RevoGrid, {
24
  type DataType,
25
  type GridPlugin,
26
} from '@revolist/vue3-datagrid';
27

28
import {
29
  PivotPlugin,
30
} from '@revolist/revogrid-enterprise';
31
import {
32
  AdvanceFilterPlugin,
33
} from '@revolist/revogrid-pro';
34

35
import { ECOMMERCE_PIVOT } from '../sys-data/ecommerce.pivot';
36

37
const props = defineProps<{
38
  rows: DataType[];
39
  hasConfigurator?: boolean;
40
}>();
41

42
const columnTypes = ref({
43
  integer: new NumberColumnType(),
44
  currency: new NumberColumnType('$0,0.00'),
45
});
46

47
const plugins: GridPlugin[] = [PivotPlugin, AdvanceFilterPlugin];
48
const additionalData = computed(() => {
49
  return {
50
    pivot: {
51
      ...ECOMMERCE_PIVOT,
52
      hasConfigurator: props.hasConfigurator,
53
    },
54
  };
55
});
56

57
</script>

1
// @ts-ignore
2
import { PivotPlugin } from '@revolist/revogrid-enterprise';
3
import { AdvanceFilterPlugin } from '@revolist/revogrid-pro';
4
import React, { useMemo } from 'react';
5
import { RevoGrid, type DataType } from '@revolist/react-datagrid';
6
import NumberColumnType from '@revolist/revogrid-column-numeral';
7
import { ECOMMERCE_PIVOT } from '../sys-data/ecommerce.pivot';
8

9
interface PivotProps {
10
  rows: DataType[];
11
}
12

13
function Pivot({ rows }: PivotProps) {
14
  const columnTypes = useMemo(
15
    () => ({
16
      integer: new NumberColumnType(),
17
      currency: new NumberColumnType('$0,0.00'),
18
    }),
19
    []
20
  );
21

22
  const plugins = useMemo(() => [PivotPlugin, AdvanceFilterPlugin], []);
23
  const additionalData = useMemo(
24
    () => ({
25
      pivot: {
26
        ...ECOMMERCE_PIVOT,
27
      },
28
    }),
29
    []
30
  );
31

32
  return (
33
    <RevoGrid
34
      hide-attribution
35
      range
36
      resize
37
      filter
38
      colSize={200}
39
      source={rows}
40
      additionalData={additionalData}
41
      theme='compact'
42
      plugins={plugins}
43
      columnTypes={columnTypes}
44
      readonly
45
    />
46
  );
47
}
48

49
export default Pivot;

1
// @ts-ignore
2
import { Component, ViewEncapsulation, Input, OnInit } from '@angular/core';
3
import { RevoGrid, type DataType } from '@revolist/angular-datagrid';
4
import NumberColumnType from '@revolist/revogrid-column-numeral';
5
import { PivotPlugin } from '@revolist/revogrid-enterprise';
6
import { AdvanceFilterPlugin } from '@revolist/revogrid-pro';
7
import { ECOMMERCE_PIVOT } from '../sys-data/ecommerce.pivot';
8

9
@Component({
10
  selector: 'pivot-grid',
11
  standalone: true,
12
  imports: [RevoGrid],
13
  template: `
14
    <revo-grid
15
      [hideAttribution]="true"
16
      [range]="true"
17
      [resize]="true"
18
      [filter]="true"
19
      [colSize]="200"
20
      [source]="rows"
21
      [additionalData]="additionalData"
22
      [theme]="'compact'"
23
      [plugins]="plugins"
24
      [columnTypes]="columnTypes"
25
      [readonly]="true"
26
    ></revo-grid>
27
  `,
28
  encapsulation: ViewEncapsulation.None,
29
})
30
export class PivotGridComponent implements OnInit {
31
  @Input() rows: DataType[] = [];
32

33
  columnTypes = {
34
    integer: new NumberColumnType(),
35
    currency: new NumberColumnType('$0,0.00'),
36
  };
37

38
  plugins = [PivotPlugin, AdvanceFilterPlugin];
39

40
  additionalData = {
41
    pivot: {
42
      ...ECOMMERCE_PIVOT,
43
    },
44
  };
45

46
  ngOnInit() {
47
    // Any additional initialization logic if needed
48
  }
49
}

1
import type { GridPlugin } from '@revolist/revogrid';
2
import {
3
  type PivotConfigDimension,
4
  type PivotConfig,
5
  PivotPlugin,
6
} from '@revolist/revogrid-enterprise';
7
import {
8
  commonAggregators,
9
  ratingStarRenderer,
10
  progressLineWithValueRenderer,
11
  progressLineRenderer,
12
  changeRenderer,
13
  thresholdRenderer,
14
  mergeCellProperties,
15
  RowOddPlugin,
16
  AdvanceFilterPlugin,
17
  SameValueMergePlugin,
18
  RowSelectPlugin,
19
  FilterHeaderPlugin,
20
} from '@revolist/revogrid-pro';
21

22
export const PIVOT_PLUGINS: GridPlugin[] = [FilterHeaderPlugin, RowSelectPlugin, SameValueMergePlugin, PivotPlugin, AdvanceFilterPlugin, RowOddPlugin] as any[];
23
export const ECOMMERCE_PIVOT: PivotConfig = {
24
  dimensions: [
25
    {
26
      prop: 'Age',
27
      columnType: 'integer',
28
      size: 100,
29
      sortable: true,
30
      filterPlaceholder: 'Age?',
31
    },
32
    {
33
      prop: 'Time',
34
      columnType: 'time',
35
      size: 100,
36
      sortable: true,
37
      columnProperties: (column) => {
38
        if (column.children && column.name === '00:00') {
39
          return {
40
            highlight: true,
41
          };
42
        }
43
      }
44
    },
45
    {
46
      prop: 'City',
47
      sortable: true,
48
      merge: true,
49
      filter: ['string', 'selection'],
50
      order: 'asc',
51
    },
52
    {
53
      prop: 'Gender',
54
      filter: ['string', 'selection'],
55
      sortable: true,
56
    },
57
    {
58
      prop: 'Membership Type',
59
      filter: ['string', 'selection'],
60
      sortable: true,
61
    },
62
    {
63
      prop: 'Total Spend',
64
      sortable: true,
65
      columnType: 'currency',
66
      filter: ['number'],
67
      filterPlaceholder: 'Total Spend?',
68
      thresholds: [
69
        { value: 900, className: 'high' },
70
        { value: 600, className: 'medium' },
71
      ],
72
      cellProperties: mergeCellProperties(thresholdRenderer, ({ value }) => ({
73
        class: {
74
          highlight: value > 20000,
75
          'align-right': true,
76
        },
77
      })),
78
      aggregators: {
79
        sum: commonAggregators.sum,
80
        avg: commonAggregators.avg,
81
        min: commonAggregators.min,
82
        max: commonAggregators.max,
83
      },
84
    },
85
    {
86
      name: 'Spend Change %',
87
      prop: 'Spend Change (%)',
88
      sortable: true,
89
      filter: ['number'],
90
      columnType: 'percent',
91
      cellTemplate: changeRenderer,
92
    },
93
    {
94
      name: 'Avg Rating',
95
      prop: 'Average Rating',
96
      filter: ['number', 'slider'],
97
      filterPlaceholder: 'Avg Rating?',
98
      sortable: true,
99
      maxStars: 5,
100
      maxValue: 5,
101
      thresholds: [
102
        { value: 4, className: 'high' },
103
        { value: 3, className: 'medium' },
104
        { value: 0, className: 'low' },
105
      ],
106
      cellParser: (model, column) => {
107
        const value = model[column.prop];
108
        if (Number(value)) {
109
          return value.toFixed(2);
110
        }
111
        return value;
112
      },
113
      cellTemplate(...args) {
114
        const column: PivotConfigDimension = args[1].column;
115
        if (column.dimension === 'values') {
116
          switch (column.aggregator) {
117
            case 'star':
118
              return ratingStarRenderer(...args);
119
            case 'prg':
120
              return progressLineRenderer(...args);
121
            case 'prgvalue':
122
              return progressLineWithValueRenderer(...args);
123
          }
124
        }
125
        return args[1].value;
126
      },
127
      aggregators: {
128
        prg: commonAggregators.avg,
129
        star: commonAggregators.avg,
130
        prgvalue: commonAggregators.avg,
131
      },
132
    },
133
    {
134
      prop: 'Discount Applied',
135
      sortable: true,
136
      filter: ['selection'],
137
    },
138
  ],
139
  rows: ['City', 'Age'],
140
  columns: ['Time'],
141
  values: [
142
    {
143
      prop: 'Total Spend',
144
      aggregator: 'sum',
145
    },
146
    {
147
      prop: 'Average Rating',
148
      aggregator: 'prg',
149
    },
150
  ],
151
  hasConfigurator: true,
152
  flatHeaders: false,
153
};

1
[{
2
  "Customer ID": "101",
3
  "Gender": "Female",
4
  "Age": "29",
5
  "City": "New York",
6
  "Membership Type": "Gold",
7
  "Total Spend": "1120.2",
8
  "Items Purchased": "14",
9
  "Average Rating": "4.6",
10
  "Discount Applied": "True",
11
  "Days Since Last Purchase": "25",
12
  "Satisfaction Level": "Satisfied",
13
  "Spend Change (%)": "12.020000000000005",
14
  "Savings": "112.02000000000001",
15
  "Items per Purchase": "0.5384615384615384",
16
  "Lifetime Value": "1680.3000000000002"
17
}, ...]

Pivot Table Element Map

It may be helpful as you begin learning the capabilities of the Pivot Table component.

Pivot Grid

Perfect for applications requiring complex data analysis, this plugin ensures flexibility and control while maintaining high performance.

Setup

Install the enterprise package
Terminal window
```
1
npm install @revolist/revogrid-enterprise
```

Import the plugin and basic aggregators

1
import { PivotPlugin, commonAggregators, type PivotConfig } from '@revolist/revogrid-enterprise';

Register the plugin

Add PivotPlugin to your grid’s plugins array:
```
1
grid.plugins = [PivotPlugin];
```

Define the pivot configuration

Create a PivotConfig object specifying your dimensions, rows, columns, and value aggregators:

1
const pivot: PivotConfig = {
2
  dimensions: [
3
    { prop: 'city' },
4
    { prop: 'age', sortable: true },
5
    {
6
      prop: 'dateOfBirth',
7
      aggregators: { count: commonAggregators.count },
8
    },
9
  ],
10
  rows: ['age'],
11
  columns: ['city'],
12
  values: [{ prop: 'dateOfBirth', aggregator: 'count' }],
13
};

Apply configuration to the grid

Pass the config through the additionalData property:
```
1
grid.additionalData = { pivot };
```

Key Features

Highlights

Dynamic Pivoting: Easily switch between standard grid and pivot mode.
Interactive Configurator: Drag and drop fields with real-time updates.
Custom Aggregation: Apply built-in or custom summarization functions.
High Performance: Optimized for large datasets even in pivot mode.
i18n Support: Ready for global applications with translation support.

Demo

Experience the Pivot Table in action: Pivot Table Demo