JavaScript
EditEdit This Document
Demos
Support
Forum
Upgrade Guide
FREE TRIAL
Search results

Data Binding in JavaScript Pivot Table control

29 Nov 2022 / 20 minutes to read

JSON

For JSON data binding, the type property under dataSourceSettings needs to be set as JSON. By default, the default value is assumed as JSON.

Binding JSON data via local

In-order to bind local JSON data to the pivot table user can assign the local variable holding the JSON data to the dataSource property under dataSourceSettings.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';

let pivotTableObj: PivotView = new PivotView({
        dataSourceSettings: {
        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' }],
        rows: [{ name: 'Country' }, { name: 'Products' }],
        filters: [],
    },
    height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Using local variable, the JSON data can also be bound to the pivot table using DataManager option with the help of JsonAdaptor. Here the instance of DataManager holding JSON data is assigned to dataSource property under dataSourceSettings. The use of DataManager is optional here.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { DataManager, JsonAdaptor } from '@syncfusion/ej2-data';
import { pivotData } from './datasource.ts';

let result: DataManager = new DataManager({ json: pivotData, adaptor: new JsonAdaptor })
let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
        dataSource: result,
        drilledMembers: [{ name: 'Country', items: ['France'] }],
        columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
        values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
        rows: [{ name: 'Country' }, { name: 'Products' }],
        filters: []
    },
    height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

In the meantime, the JSON data from the local *.json file type can also be connected to the pivot table via the file uploader option. Here, the resulting string after uploading the file needs to be converted to JSON data that can be assigned to the dataSource property under dataSourceSettings. The following code example illustrates the same.

Copied to clipboard
import { PivotView } from '@syncfusion/ej2-pivotview';
import { Uploader } from '@syncfusion/ej2-inputs';

// Step 1: Initiate the file uploader
let uploadObj: Uploader = new Uploader({
});
uploadObj.appendTo('#fileupload');

let input = document.querySelector('input[type="file"]');
// Step 2: Add the event listener which fires when the *.JSON file is uploaded.
input.addEventListener('change', function (e: Event) {
    // Step 3: Initiate the file reader
    let reader: FileReader = new FileReader();
    reader.onload = function () {
        // Step 4: Getting the string output which is to be parsed as JSON.
        let result: any =JSON.parse(reader.result as string);
        let pivotGridObj: PivotView = new PivotView({
            dataSourceSettings: {
                // Step 5: The JSON result to be bound as data source.
                dataSource: result
                // Step 6: The appropriate report needs to be provided here.
            },
        });
        pivotGridObj.appendTo('#PivotTable');
    };
    reader.readAsText((input as any).files[0]);
});

Binding JSON data via remote

In-order to bind remote JSON data, mention the endpoint URL under dataSourceSettings property. The URL property supports both direct downloadable file (*.json) and web service URL.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView } from '@syncfusion/ej2-pivotview';

let pivotGridObj: PivotView = new PivotView({
    dataSourceSettings: {
        url: 'https://cdn.syncfusion.com/data/sales-analysis.json',
        expandAll: false,
        rows: [
            { name: 'EnerType', caption: 'Energy Type' }
        ],
        columns: [
            { name: 'EneSource', caption: 'Energy Source' }
        ],
        values: [
            { name: 'PowUnits', caption: 'Units (GWh)' },
            { name: 'ProCost', caption: 'Cost (MM)' }
        ],
        filters: []
    },
});
pivotGridObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

CSV

For CSV data binding, the type property under dataSourceSettings needs to be set as CSV mandatorily.

The CSV format is considered to be the most compact format compared to JSON since it is half the size of JSON. This helps to reduce the bandwidth while transferring to the browser.

Binding CSV data via local

In-order to bind local CSV data to the pivot table, user needs to convert it as string array and then directly assign it to the dataSource property under dataSourceSettings.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView } from '@syncfusion/ej2-pivotview';
import { csvdata } from './datasource.ts';
import { isNullOrUndefined } from '@syncfusion/ej2-base';

let pivotObj: PivotView = new PivotView({
    dataSourceSettings: {
        dataSource: getCSVData(),
        type: 'CSV',
        expandAll: false,
        formatSettings: [{ name: 'Total Cost', format: 'C0' }, { name: 'Total Revenue', format: 'C0' }, { name: 'Total Profit', format: 'C0' }],
        drilledMembers: [{ name: 'Item Type', items: ['Baby Food'] }],
        rows: [
            { name: 'Region' },
            { name: 'Country' }
        ],
        columns: [
            { name: 'Item Type' },
            { name: 'Sales Channel' }
        ],
        values: [
            { name: 'Total Cost' },
            { name: 'Total Revenue' },
            { name: 'Total Profit' }
        ],
        filters: []
    },
    height: 290,
    width: '100%'
});
pivotObj.appendTo('#PivotTable');
function getCSVData(): string[][] {
    let dataSource: string[][] = [];
    let jsonObject: string[] = csvdata.split(/\r?\n|\r/);
    for (let i: number = 0; i < jsonObject.length; i++) {
        if (!isNullOrUndefined(jsonObject[i]) && jsonObject[i] !== '') {
            dataSource.push(jsonObject[i].split(','));
        }
    }
    return dataSource;
}
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

In the meantime, the CSV data from the local *.csv file type can also be connected to the pivot table via the file uploader option. Here, the resulting string after uploading the file needs to be converted to string array that can be assigned to the dataSource property under dataSourceSettings. The following code example illustrates the same.

Copied to clipboard
import { PivotView } from '@syncfusion/ej2-pivotview';
import { Uploader } from '@syncfusion/ej2-inputs';

// Step 1: Initiate the file uploader
let uploadObj: Uploader = new Uploader({
});
uploadObj.appendTo('#fileupload');
// Step 2: Add the event listener which fires when the *.CSV file is uploaded.
let input = document.querySelector('input[type="file"]');
input.addEventListener('change', function (e: Event) {
    // Step 3: Initiate the file reader
    let reader: FileReader = new FileReader();
    reader.onload = function () {
        // Step 4: Getting the string output which is to be converted as string[][].
        let result: string[][] = (reader.result as string).split('\n').map(function (line) {
            return line.split(',');
        });
        let pivotGridObj: PivotView = new PivotView({
            dataSourceSettings: {
                // Step 5: The string[][] result to be bound as data source.
                dataSource: result,
                type: 'CSV',
                // Step 6: The appropriate report needs to be provided here.
            },
        });
        pivotGridObj.appendTo('#PivotTable');
    };
    reader.readAsText((input as any).files[0]);
});

Binding CSV data via remote

In-order to bind remote CSV data, mention the endpoint URL under dataSourceSettings property. The URL property supports both direct downloadable file (*.csv) and web service URL.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView } from '@syncfusion/ej2-pivotview';

let pivotObj: PivotView = new PivotView({
    dataSourceSettings: {
        url: 'https://bi.syncfusion.com/productservice/api/sales',
        type: 'CSV',
        expandAll: false,
        enableSorting: true,
        formatSettings: [{ name: 'Total Cost', format: 'C0' }, { name: 'Total Revenue', format: 'C0' }, { name: 'Total Profit', format: 'C0' }],
        drilledMembers: [{ name: 'Item Type', items: ['Baby Food'] }],
        rows: [
            { name: 'Region' },
            { name: 'Country' }
        ],
        columns: [
            { name: 'Item Type' },
            { name: 'Sales Channel' }
        ],
        values: [
            { name: 'Total Cost' },
            { name: 'Total Revenue' },
            { name: 'Total Profit' }
        ],
        filters: []
    },
    height: 300,
    width: '100%'
});
pivotObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Remote Data Binding

To interact with remote data source, provide the endpoint url within DataManager along with appropriate adaptor. By default, DataManager uses ODataAdaptor for remote data-binding.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { DataManager, WebApiAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';

let data: DataManager = new DataManager({
    url: 'https://bi.syncfusion.com/northwindservice/api/orders',
    adaptor: new WebApiAdaptor,
    crossDomain: true
});

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
        dataSource: data,
        expandAll: true,
        filters: [],
        columns: [{ name: 'ProductName', caption: 'Product Name' }],
        rows: [{ name: 'ShipCountry', caption: 'Ship Country' }, { name: 'ShipCity', caption: 'Ship City' }],
        formatSettings: [{ name: 'UnitPrice', format: 'C0' }],
        values: [{ name: 'Quantity' }, { name: 'UnitPrice', caption: 'Unit Price' }]
    },
    height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Binding with OData services

OData is a standardized protocol for creating and consuming data. User can retrieve data from OData service using the DataManager. Refer to the following code example for remote data binding using OData service.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';

let data: DataManager = new DataManager({
    url: 'https://bi.syncfusion.com/northwindservice/api/orders',
    adaptor: new ODataAdaptor,
    crossDomain: true
});

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
        dataSource: data,
        expandAll: true,
        filters: [],
        columns: [{ name: 'CustomerID', caption: 'Customer Name' }],
        rows: [{ name: 'OrderID', caption: 'Order ID' },{name: 'OrderDate', caption:'Order Date' } ],
        values: [{ name: 'Freight' }]
    },
    height: 350,
    width: '100%',
    gridSettings: { columnWidth: 120 }
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Binding with OData V4 services

The OData V4 is an improved version of OData protocols, and the DataManager can be used to retrieve and consume OData V4 services. For more details on OData V4 services, refer to the OData documentation. To bind OData V4 service, use the ODataV4Adaptor.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';

let data: DataManager = new DataManager({
    url: 'https://bi.syncfusion.com/northwindservice/api/orders',
    adaptor: new ODataV4Adaptor,
    crossDomain: true
});

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
        dataSource: data,
        expandAll: true,
        filters: [],
        columns: [{ name: 'CustomerID', caption: 'Customer Name' }],
        rows: [{ name: 'OrderID', caption: 'Order ID' },{name: 'OrderDate', caption:'Order Date' } ],
        values: [{ name: 'Freight' }]
    },
    height: 350,
    width: '100%',
    gridSettings: { columnWidth: 120 }
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Web API

User can use WebApiAdaptor to bind pivot table with Web API created using OData endpoint.

Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { DataManager, WebApiAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';

let data: DataManager = new DataManager({
    url: 'https://bi.syncfusion.com/northwindservice/api/orders',
    adaptor: new WebApiAdaptor,
    crossDomain: true
});

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
        dataSource: data,
        expandAll: true,
        filters: [],
        columns: [{ name: 'ProductName', caption: 'Product Name' }],
        rows: [{ name: 'ShipCountry', caption: 'Ship Country' }, { name: 'ShipCity', caption: 'Ship City' }],
        formatSettings: [{ name: 'UnitPrice', format: 'C0' }],
        values: [{ name: 'Quantity' }, { name: 'UnitPrice', caption: 'Unit Price' }]
    },
    height: 350
});
pivotTableObj.appendTo('#PivotTable');

Querying in Data Manager

By default, the data manager retrieves all the data from the provider which is mapped in it. The data from the provider can be filtered, sorted, paged, etc. by setting the own query in defaultQuery property in the data manager instance.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView } from '@syncfusion/ej2-pivotview';
import { DataManager, ODataAdaptor, Query } from '@syncfusion/ej2-data';

let remoteData: DataManager = new DataManager({
  url: 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders',
  adaptor: new ODataAdaptor(),
  crossDomain: true
});
remoteData.defaultQuery = new Query().take(2);

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
        dataSource: remoteData,
        expandAll: false,
        columns: [{ name: 'CustomerID', caption: 'Customer ID' }],
        rows: [{ name: 'ShipCountry', caption: 'Ship Country' }, { name: 'ShipCity', caption: 'Ship City' }],
        values: [{ name: 'Freight' }]
    },
    height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Mapping

One can define field information like alias name (caption), data type, aggregation type, show and hide subtotals etc. using the fieldMapping property under dataSourceSettings. The available options are,

  • name - It is to specify the appropriate field name.
  • caption - It is to set the alias name (caption) to the specific field. Instead of actual field name, the alias name (caption) will be set in the UI of the pivot table.
  • type - It is to display values in the pivot table with appropriate aggregation such as sum, product, count, average, minimum, maximum, etc. Its default value is sum. This option is applicable only for relational data source.
  • axis - It will help to display the field in specified axis such as row/column/value/filter axis of the pivot table.
  • showNoDataItems - It is to show all the members of a specific field to the pivot table, even if there are no data in the intersection of the row and column. The default value is false. This option is applicable only for relational data source.
  • baseField - For the aggregate types like “DifferenceFrom” or “PercentageOfDifferenceFrom” or “PercentageOfParentTotal”, selective field is assigned for comparison via this property.
  • baseItem - For the aggregate types like “DifferenceFrom” or “PercentageOfDifferenceFrom” or “PercentageOfParentTotal”, selective member in a field is assigned for comparison via this property.
  • expandAll - It is to expand or collapse all headers of a specific field in row and column axes of the pivot table. The default value is false.
  • showSubTotals - It is to show or hide sub-totals of a specific field in row and column axis of the pivot table. The default value is true.
  • isNamedSet - It is to set whether the specified field is named set or not. In general, the named set is a set of dimension members or a set expression (MDX query) to be created as a dimension in the SSAS OLAP cube itself. The default value is false and this option is applicable only for OLAP data source.
  • isCalculatedField - It is to set whether the specified field is a calculated field or not. In general, a calculated field is created from the bound data source or using simple formula with basic arithmetic operators in the pivot table. The default value is false and this option is applicable only for OLAP data source.
  • showFilterIcon - It is to show or hide the filter icon of a specific field which will be displayed on the button of the grouping bar and field list UI. This filter icon is used to filter the members of a specified field at runtime in the pivot table. The default value is true.
  • showSortIcon - It is to show or hide the sort icon of a specific field which will be displayed on the button of the grouping bar and field list UI. This sort icon is used to order members of a specified field either in ascending or descending at runtime. The default value is true.
  • showRemoveIcon - It is to show or hide the remove icon of a specific field which will be displayed on the button of the grouping bar and field list UI. This remove icon is used to remove the specified field during runtime. The default value is true.
  • showValueTypeIcon - It is to show or hide the value type icon of a specific field which will be displayed on the button of the grouping bar and field list UI. This value type icon helps to select the appropriate aggregation type to specified value field at runtime. The default value is true.
  • showEditIcon - It is to show or hide the edit icon of a specific field which will be displayed on the button of the grouping bar and field list UI. This edit icon is used to modify caption, formula, and format of a specified calculated field at runtime. The default value is true.
  • allowDragAndDrop - It is to restrict specific field’s button from being dragged on runtime in the grouping bar and field list UI. This will prevent from altering the current report. The default value is true.
  • dataType - It is to specify the type of the field like ‘string’, ‘number’, ‘datetime’, ‘date’, and ‘boolean’.
  • groupName - It is to display fields in the field list UI by grouping them under the desired folder name.

The main purpose of these mapping options is to configure each field that is not part of the initial pivot report. Even if any field that is part of this mapping is defined here, the value set in the initial report will have the highest preceding.

This option is applicable only for relational data source. In the below code sample, visibility of the field button icons are configured.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet, GroupingBar, FieldList } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';

PivotView.Inject(GroupingBar, FieldList);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    allowLabelFilter: true,
    allowValueFilter: true,
    columns: [{ name: 'Year', caption: 'Production Year' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }],
    rows: [{ name: 'Country' }],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    filters: [],
     fieldMapping: [
        { name: 'Quarter', showSortIcon: false },
        { name: 'Products', showFilterIcon: false, showRemoveIcon:false },
        { name: 'Amount', showValueTypeIcon: false, caption: 'Sold Amount' },
    ]
},
showGroupingBar: true,
showFieldList: true,
height: 340
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Values in row axis

By default, the value fields are plotted in column axis. To plot those fields in row axis, use the valueAxis property by setting its value as row. By default, it holds the value column.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
    dataSource: pivotData as IDataSet[],
    expandAll: true,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    filters: [],
    valueAxis: 'row'
},
height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Values at different positions

By default, the value fields are placed at the end of the row or column axis. To place those value fields in different positions, use the valueIndex property and set the value to an appropriate index position. Its default value is -1, which denotes the last position. The valueIndex property is dependent on the valueAxis property.

This support is only available for relational data sources. Also, enable the showValuesButton property in the grouping bar and field list UI to true to re-arrange the values fields at different positions via user interaction.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
    dataSource: pivotData as IDataSet[],
    expandAll: true,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    filters: [],
    valueIndex: 1
},
height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Show ‘no data’ items

By default, the pivot table only shows the field item if it has data in its row or column combination. To show all items that do not have data in row and column combination in the pivot table, use the showNoDataItems property by settings its value to true for the desired fields. In the following code sample, rows of the “Country” and “State” fields do not have data in all combination with “Date” column field.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { noData } from './datasource.ts';

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
    dataSource: noData as IDataSet[],
    expandAll: true,
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    columns: [{ name: 'Date', showNoDataItems: true}],
    values: [{ name: 'Quantity', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
    rows: [{ name: 'Country', showNoDataItems: true }, { name: 'State', showNoDataItems: true }],
    filters: []
},
height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Always shows the value headers

To show the value header always in pivot table, even if it holds a single value, use the alwaysShowValueHeader property by settings its value as true.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
    dataSource: pivotData as IDataSet[],
    expandAll: true,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    filters: [],
    alwaysShowValueHeader: true
},
height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Customize empty value cells

User can show custom string in empty value cells using the emptyCellsTextContent property in dataSourceSettings of the pivot table. Since the property is of string data type, user can fill empty value cells with any value like “0”, ”-”, ”*”, “(blank)”, etc. Its common for all value fields and can be configured through code behind.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { noData } from './datasource.ts';

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
    dataSource: noData as IDataSet[],
    expandAll: true,
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    columns: [{ name: 'Date', showNoDataItems: true}],
    values: [{ name: 'Quantity', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
    rows: [{ name: 'Country', showNoDataItems: true }, { name: 'State', showNoDataItems: true }],
    filters: [],
    emptyCellsTextContent:'**'
},
height: 350
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

Events

Load

The event load fires before initiate rendering of pivot table. In this event user can customize data source settings before initiating pivot table render module. It holds following parameters like dataSourceSettings, fieldsType and pivotView.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet, GroupingBar } from '@syncfusion/ej2-pivotview';
import { noData } from './datasource.ts';

PivotView.Inject(GroupingBar);
let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
    dataSource: noData as IDataSet[],
    expandAll: true,
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    columns: [{ name: 'Date', showNoDataItems: true}],
    values: [{ name: 'Quantity', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
    rows: [{ name: 'Country', showNoDataItems: true }, { name: 'State', showNoDataItems: true }],
    filters: [],
    emptyCellsTextContent:'**'
},
height: 350,
load(args: LoadEventArgs): void {
    args.dataSourceSettings.columns[0].caption = 'Total Population';
    args.dataSourceSettings.emptyCellsTextContent = '###'
},
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

EnginePopulated

The event enginePopulated is triggered after engine is populated. It has following parameters - dataSourceSettings, pivotFieldList and pivotValues.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet, GroupingBar, EnginePopulatedEventArgs } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';

PivotView.Inject(GroupingBar);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    enableSorting: true,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    filters: []
},
showGroupingBar: true,
height: 350,
enginePopulated(args: EnginePopulatedEventArgs): void {
    //triggers after engine is populated
},
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

EnginePopulating

The event enginePopulating triggers before the pivot engine starts to populate and allows to customize the pivot datasource settings. It has following parameter dataSourceSettings.

Source
Preview
index.ts
index.html
Copied to clipboard
import { PivotView, IDataSet, GroupingBar, EnginePopulatingEventArgs } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';

PivotView.Inject(GroupingBar);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    enableSorting: true,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    filters: []
},
showGroupingBar: true,
height: 350,
enginePopulating(args: EnginePopulatingEventArgs): void {
    args.dataSourceSettings.columns[0].caption = 'Full Year';
    args.dataSourceSettings.emptyCellsTextContent = '###'
},
});
pivotTableObj.appendTo('#PivotTable');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Pivot Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-charts/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.3.56/ej2-pivotview/styles/material.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet"/>
    <script src="systemjs.config.js"></script>
</head>

<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div>
            <div id='PivotTable'></div>
        </div>
    </div>
</body>

</html>

See Also

Previous
Next