React

Search results

Aggregation in React Pivot Table component

02 Aug 2021 / 9 minutes to read

This feature is applicable only for the relational data source.

End user can perform calculations over a group of values (exclusively for value fields bound in value axis) using the aggregation option. By default, values are added (summed) together. The other aggregation types are explained below.

The fields with data type such as number support all aggregation types mentioned below except for “CalculatedField”. The fields with data type such as string, date, datetime, boolean, etc., support “Count” and “DistinctCount” aggregation types alone.

Operator	Description
Sum	Displays the pivot table values with sum.
Product	Displays the pivot table values with product.
Count	Displays the pivot table values with count.
DistinctCount	Displays the pivot table values with distinct count.
Min	Displays the pivot table with minimum value.
Max	Displays the pivot table with maximum value.
Avg	Displays the pivot table values with average.
Median	Displays the pivot table values with median.
Index	Displays the pivot table values with index.
PopulationStDev	Displays the pivot table values with standard deviation of population.
SampleStDev	Displays the pivot table values with sample standard deviation.
PopulationVar	Displays the pivot table values with variance of population.
SampleVar	Displays the pivot table values with sample variance.
RunningTotals	Displays the pivot table values with running totals.
DifferenceFrom	Displays the pivot table values with difference from the value of the base item in the base field.
PercentageOfDifferenceFrom	Displays the pivot table values with percentage difference from the value of the base item in the base field.
PercentageOfGrandTotal	Displays the pivot table values with percentage of grand total of all values.
PercentageOfColumnTotal	Displays the pivot table values in each column with percentage of total values for the column.
PercentageOfRowTotal	Displays the pivot table values in each row with percentage of total values for the row.
PercentageOfParentTotal	Displays the pivot table values with percentage of total of all values based on selected field.
PercentageOfParentColumnTotal	Displays the pivot table values with percentage of its parent total in each column.
PercentageOfParentRowTotal	Displays the pivot table values with percentage of its parent total in each row.
CalculatedField	Displays the pivot table with calculated field values. It allows user to create a new calculated field alone.

Assigning aggregation type for value fields through API

For each value field, the aggregation type can be set using the property type in values class. Meanwhile, aggregation types like DifferenceFrom and PercentageOfDifferenceFrom can check for specific field of specific item using baseField and baseItem properties. Likewise, PercentageOfParentTotal type can for specific field using baseField property. For instance, the aggregation type DifferenceFrom would intake the specified field and its corresponding member as input and its value is compared across other members in the same field and also across different fields to formulate an appropriate output value.

type: It allows to set the aggregate type of the field.
baseField: It allows to set the specific field to aggregate the values.
baseItem: It allows to set the specific member to aggregate the values.

Source

Preview

index.tsx

index.jsx

import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';

class App extends React.Component<{}, {}>{

  public dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount', type: 'Sum' }]
  }
  public pivotObj: PivotViewComponent;
  render() {
return <PivotViewComponent  ref={d => this.pivotObj = d!} id='PivotView' height={350} dataSourceSettings={this.dataSourceSettings}></PivotViewComponent>
  }
};

ReactDOM.render(<App />, document.getElementById('pivotview'));

import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
class App extends React.Component {
    constructor() {
        super(...arguments);
        this.dataSourceSettings = {
            columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
            dataSource: pivotData,
            expandAll: false,
            filters: [],
            drilledMembers: [{ name: 'Country', items: ['France'] }],
            formatSettings: [{ name: 'Amount', format: 'C0' }],
            rows: [{ name: 'Country' }, { name: 'Products' }],
            values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount', type: 'Sum' }]
        };
    }
    render() {
        return <PivotViewComponent ref={d => this.pivotObj = d} id='PivotView' height={350} dataSourceSettings={this.dataSourceSettings}></PivotViewComponent>;
    }
}
;
ReactDOM.render(<App />, document.getElementById('pivotview'));

By default, the aggregation will be considered as Sum to the value fields which had number type and for the value fields which had non-number type values such as string, date, datetime, boolean, etc., the aggregation type will be considered as Count.

Modifying aggregation type for value fields at runtime

Aggregation types can be changed easily through UI at runtime. The value fields bound to grouping bar and field list appears with a dropdown icon which helps to select an appropriate aggregation type for the respective value field. On selection, the values in the pivot table will be changed dynamically.

output

output

By default, all the aggregation types are displayed in the dropdown menu available in buttons. However, based on the request for an application, we may need to show selective aggregation types on our own. This can be achieved using the aggregateTypes property.

Source

Preview

index.tsx

index.jsx

import { GroupingBar, FieldList, Inject, IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';

class App extends React.Component<{}, {}>{

  public dataSourceSettings: IDataOptions = {
dataSource: pivotData as IDataSet,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount', type: 'Sum' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
filters: [],
  }
  public pivotObj: PivotViewComponent;
  public pivotAggregateTypes: any = ['DistinctCount', 'Avg', 'Product'];
  render() {
return <PivotViewComponent  ref={d => this.pivotObj = d!} id='PivotView' height={350} dataSourceSettings={this.dataSourceSettings} showFieldList={true} showGroupingBar={true} aggregateTypes={this.pivotAggregateTypes}><Inject services={[GroupingBar, FieldList]}/></PivotViewComponent>
  }
};

ReactDOM.render(<App />, document.getElementById('pivotview'));

import { GroupingBar, FieldList, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
class App extends React.Component {
    constructor() {
        super(...arguments);
        this.dataSourceSettings = {
            dataSource: pivotData,
            drilledMembers: [{ name: 'Country', items: ['France'] }],
            columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
            values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount', type: 'Sum' }],
            rows: [{ name: 'Country' }, { name: 'Products' }],
            filters: [],
        };
        this.pivotAggregateTypes = ['DistinctCount', 'Avg', 'Product'];
    }
    render() {
        return <PivotViewComponent ref={d => this.pivotObj = d} id='PivotView' height={350} dataSourceSettings={this.dataSourceSettings} showFieldList={true} showGroupingBar={true} aggregateTypes={this.pivotAggregateTypes}><Inject services={[GroupingBar, FieldList]}/></PivotViewComponent>;
    }
}
;
ReactDOM.render(<App />, document.getElementById('pivotview'));

Hiding aggregation type from button text

By default, in value axis each field would be displayed by its name and aggregation type together. To hide aggregation type and display field name alone, set the property showAggregationOnValueField in dataSourceSettings to false.

Source

Preview

index.tsx

index.jsx

import { IDataOptions, IDataSet, PivotViewComponent, Inject, GroupingBar } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';

class App extends React.Component<{}, {}>{

  public dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount', type: 'Sum' }],
showAggregationOnValueField: false
  }
  public pivotObj: PivotViewComponent;
  render() {
return <PivotViewComponent  ref={d => this.pivotObj = d!} id='PivotView' height={350} dataSourceSettings={this.dataSourceSettings} showGroupingBar={true}><Inject services={[GroupingBar]}/></PivotViewComponent>
  }
};

ReactDOM.render(<App />, document.getElementById('pivotview'));

import { PivotViewComponent, Inject, GroupingBar } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
class App extends React.Component {
    constructor() {
        super(...arguments);
        this.dataSourceSettings = {
            columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
            dataSource: pivotData,
            expandAll: false,
            filters: [],
            drilledMembers: [{ name: 'Country', items: ['France'] }],
            formatSettings: [{ name: 'Amount', format: 'C0' }],
            rows: [{ name: 'Country' }, { name: 'Products' }],
            values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount', type: 'Sum' }],
            showAggregationOnValueField: false
        };
    }
    render() {
        return <PivotViewComponent ref={d => this.pivotObj = d} id='PivotView' height={350} dataSourceSettings={this.dataSourceSettings} showGroupingBar={true}><Inject services={[GroupingBar]}/></PivotViewComponent>;
    }
}
;
ReactDOM.render(<App />, document.getElementById('pivotview'));

Hiding aggregation type icon from UI

By default, the icon to set aggregation type is enabled in the grouping bar. To disable this icon, set the property showValueTypeIcon in groupingBarSettings to false.

Icon to change the aggregation type can be hidden only in Grouping Bar but not in Field List at the moment.

Source

Preview

index.tsx

index.jsx

import { GroupingBar, GroupingBarSettings,  IDataOptions, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';

class App extends React.Component<{}, {}>{
  public groupingSettings: GroupingBarSettings = {
showValueTypeIcon: false
  } as GroupingBarSettings;

  public dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
  }
  public pivotObj: PivotViewComponent;
  render() {
return <PivotViewComponent  ref={d => this.pivotObj = d!} id='PivotView' height={350} groupingBarSettings={this.groupingSettings} dataSourceSettings={this.dataSourceSettings} showGroupingBar={true} ><Inject services={[GroupingBar]}/> </PivotViewComponent>
  }
};

ReactDOM.render(<App />, document.getElementById('pivotview'));

import { GroupingBar, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
class App extends React.Component {
    constructor() {
        super(...arguments);
        this.groupingSettings = {
            showValueTypeIcon: false
        };
        this.dataSourceSettings = {
            columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
            dataSource: pivotData,
            expandAll: false,
            filters: [],
            formatSettings: [{ name: 'Amount', format: 'C0' }],
            rows: [{ name: 'Country' }, { name: 'Products' }],
            values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
        };
    }
    render() {
        return <PivotViewComponent ref={d => this.pivotObj = d} id='PivotView' height={350} groupingBarSettings={this.groupingSettings} dataSourceSettings={this.dataSourceSettings} showGroupingBar={true}><Inject services={[GroupingBar]}/> </PivotViewComponent>;
    }
}
;
ReactDOM.render(<App />, document.getElementById('pivotview'));

Event

AggregateCellInfo

The event aggregateCellInfo triggers every time while rendering each value cell. This allows user to change the cell value and skip formatting if applied. It has following parameters:

fieldName - It holds current cell’s field name.
row - It holds current cell’s row value.
column - It holds current cell’s row value.
value - It holds value of current cell.
cellSets - It holds raw data for the aggregated value cell.
rowCellType - It holds row cell type value.
columnCellType - It holds column cell type value.
aggregateType - It holds aggregate type of the cell.
skipFormatting - boolean property, it allows to skip formatting if applied.

Source

Preview

index.tsx

index.jsx

import { IDataOptions, IDataSet, PivotViewComponent, AggregateEventArgs } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';

class App extends React.Component<{}, {}>{

  public dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C2'}],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
  }
  public pivotObj: PivotViewComponent;

  aggregateCell(args: AggregateEventArgs): void {
  args.skipFormatting = true;
  }

  render() {
return <PivotViewComponent  ref={d => this.pivotObj = d!} id='PivotView' height={350} aggregateCellInfo={this.aggregateCell.bind(this)} dataSourceSettings={this.dataSourceSettings}></PivotViewComponent>
  }
};

ReactDOM.render(<App />, document.getElementById('pivotview'));

import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
class App extends React.Component {
    constructor() {
        super(...arguments);
        this.dataSourceSettings = {
            columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
            dataSource: pivotData,
            expandAll: false,
            filters: [],
            drilledMembers: [{ name: 'Country', items: ['France'] }],
            formatSettings: [{ name: 'Amount', format: 'C2' }],
            columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
            values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
            rows: [{ name: 'Country' }, { name: 'Products' }],
        };
    }
    aggregateCell(args) {
        args.skipFormatting = true;
    }
    render() {
        return <PivotViewComponent ref={d => this.pivotObj = d} id='PivotView' height={350} aggregateCellInfo={this.aggregateCell.bind(this)} dataSourceSettings={this.dataSourceSettings}></PivotViewComponent>;
    }
}
;
ReactDOM.render(<App />, document.getElementById('pivotview'));

Contents

Assigning aggregation type for value fields through API
Modifying aggregation type for value fields at runtime
Show desired aggregation types in its dropdown menu
Hiding aggregation type from button text
Hiding aggregation type icon from UI
Event
AggregateCellInfo

Contents

Assigning aggregation type for value fields through API
Modifying aggregation type for value fields at runtime
Show desired aggregation types in its dropdown menu
Hiding aggregation type from button text
Hiding aggregation type icon from UI
Event
AggregateCellInfo

Please provide additional information

Thank you for your feedback and comments.We will rectify this as soon as possible!

Aggregation in React Pivot Table component

Assigning aggregation type for value fields through API

Modifying aggregation type for value fields at runtime

Show desired aggregation types in its dropdown menu

Hiding aggregation type from button text

Hiding aggregation type icon from UI

Event

AggregateCellInfo