Virtual Scrolling in JavaScript (ES5) Pivot Table control

06 Jun 2023 / 6 minutes to read

Virtual scrolling

Allows to load the large amounts of data without any performance degradation by rendering rows and columns only in the current content viewport. Rest of the aggregated data will be brought into viewport dynamically based on vertical or horizontal scroll position. This feature can be enabled by setting the enableVirtualization property in pivot table to true..

To use the virtual scrolling feature, inject the VirtualScroll module in to the pivot table.

Source

Preview

index.js

index.html

var customername = ['TOM', 'Hawk', 'Jon', 'Chandler', 'Monica', 'Rachel', 'Phoebe', 'Gunther',
        'Ross', 'Geller', 'Joey', 'Bing', 'Tribbiani', 'Janice', 'Bong', 'Perk', 'Green', 'Ken', 'Adams'];
    var city = ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Philadelphia', 'Phoenix', 'San Antonio', 'Austin',
        'San Francisco', 'Columbus', 'Washington', 'Portland', 'Oklahoma', 'Las Vegas', 'Virginia', 'St. Louis', 'Birmingham'];
    var data = function (count) {
        var result = [];
        var dt = 0;
        for (var i = 1; i < (count + 1); i++) {
            dt++;
            var round = void 0;
            var toString_1 = i.toString();
            if (toString_1.length === 1) {
                round = '0000' + (i);
            }
            else if (toString_1.length === 2) {
                round = '000' + i;
            }
            else if (toString_1.length === 3) {
                round = '00' + i;
            }
            else if (toString_1.length === 4) {
                round = '0' + i;
            }
            else {
                round = toString_1;
            }
            result.push({
                ProductID: 'PRO-' + round,
                City: city[Math.round(Math.random() * city.length)] || city[0],
                Year: "FY " + (dt + 2013),
                CustomerName: customername[Math.round(Math.random() * customername.length)] || customername[0],
                Price: Math.round(Math.random() * 5000) + 5000,
                Sold: Math.round(Math.random() * 80) + 10,
            });
            if (dt / 4 == 1) {
                dt = 0;
            }
        }
        return result;
    };    
    var pivotTableObj = new ej.pivotview.PivotView({
        dataSourceSettings: {
            dataSource: data(1000),
            enableSorting: false,
            expandAll: true,
            formatSettings: [{ name: 'Price', format: 'C0' }],
            rows: [{ name: 'ProductID' }],
            columns: [{ name: 'Year' }],
            values: [{ name: 'Price', caption: 'Unit Price' }, { name: 'Sold', caption: 'Unit Sold' }]
        },
        height: 350,
        enableVirtualization: true
    });
    pivotTableObj.appendTo('#PivotTable');

<!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/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-charts/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div id="container">
        <div>
            <div id="PivotTable"></div>
        </div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Limitations for virtual scrolling

In virtual scrolling, the columnWidth property in gridSettings should be in pixel and percentage values are not accepted.
Resizing columns, setting width to individual columns which affects the calculation used to pick the correct page on scrolling.
Grouping, which takes additional time to splitting the raw items into the provided format.
Date Formatting, which takes additional time to convert date format.
Date Formatting with sorting, here additionally full date time format should be framed to perform sorting along with the provided date format which lags the performance.
Even if virtual scrolling is enabled, not only is the current view port data retrieved, but also the data for the immediate previous page and the immediate next page. As a result, when the end user scrolls slightly ahead or behind, the next or previous page data is displayed immediately without requiring a refresh. Note: If the pivot table’s width and height are large, the loading data count in the current, previous, and next view ports (pages) will also increase, affecting performance.

Data Compression

This property is applicable only for relational data source.

When we bind one million raw data, the pivot table will process all raw data to generate aggregated data during initial rendering and report manipulation. But in data compression, the data will be compressed based on the uniqueness of the raw data, and unique records will be provided as input for the Pivot Table. The compressed data will be used for further operations at all times, reducing the looping complexity and improving the performance of the pivot table. For example, if the pivot table is connected to one million raw data aggregated to 1,000 unique data means, it will be rendered within 3 seconds rather than 10 seconds. You can enable this option by using the allowDataCompression property along with enableVirtualization property.

This options will only function when the virtual scrolling is enabled.

Source

Preview

index.js

index.html

var customername = ['TOM', 'Hawk', 'Jon', 'Chandler', 'Monica', 'Rachel', 'Phoebe', 'Gunther',
        'Ross', 'Geller', 'Joey', 'Bing', 'Tribbiani', 'Janice', 'Bong', 'Perk', 'Green', 'Ken', 'Adams'];
    var city = ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Philadelphia', 'Phoenix', 'San Antonio', 'Austin',
        'San Francisco', 'Columbus', 'Washington', 'Portland', 'Oklahoma', 'Las Vegas', 'Virginia', 'St. Louis', 'Birmingham'];
    var data = function (count) {
        var result = [];
        var dt = 0;
        for (var i = 1; i < (count + 1); i++) {
            dt++;
            var round = void 0;
            var toString_1 = i.toString();
            if (toString_1.length === 1) {
                round = '0000' + (i);
            }
            else if (toString_1.length === 2) {
                round = '000' + i;
            }
            else if (toString_1.length === 3) {
                round = '00' + i;
            }
            else if (toString_1.length === 4) {
                round = '0' + i;
            }
            else {
                round = toString_1;
            }
            result.push({
                ProductID: 'PRO-' + round,
                City: city[Math.round(Math.random() * city.length)] || city[0],
                Year: "FY " + (dt + 2013),
                CustomerName: customername[Math.round(Math.random() * customername.length)] || customername[0],
                Price: Math.round(Math.random() * 5000) + 5000,
                Sold: Math.round(Math.random() * 80) + 10,
            });
            if (dt / 4 == 1) {
                dt = 0;
            }
        }
        return result;
    };    
    var pivotTableObj = new ej.pivotview.PivotView({
        dataSourceSettings: {
            dataSource: data(1000),
            enableSorting: false,
            expandAll: true,
            formatSettings: [{ name: 'Price', format: 'C0' }],
            rows: [{ name: 'ProductID' }],
            columns: [{ name: 'Year' }],
            values: [{ name: 'Price', caption: 'Unit Price' }, { name: 'Sold', caption: 'Unit Sold' }]
        },
        height: 350,
        enableVirtualization: true
    });
    pivotTableObj.appendTo('#PivotTable');

<!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/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-charts/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div id="container">
        <div>
            <div id="PivotTable"></div>
        </div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Limitations during data compression

The following aggregation types will not be supported.
Average
Populationstdev
Samplestdev
Populationvar
Samplevar
If you use any of the aggregations above, it will result in an aggregation type “Sum”.
Distinctcount will act as “Count” aggregation type.
In the calculated field, an existing field can be inserted without altering its default aggregation type Even if we change it, it would use the default aggregation type back for calculation.

Virtual scrolling for static field list

Virtual scrolling automatically works with “Popup” field list on setting the enableVirtualization property in the Pivot Table to true. Incase of static field list, which act as a separate component, user need to enable enableVirtualization property in the Pivot Table and also pass the report information to pivot table instance via the load event of the field list.

Source

Preview

index.js

index.html

var data = function (count) {
        var result = [];
        var dt = 0;
        for (var i = 1; i < (count + 1); i++) {
            dt++;
            var round = void 0;
            var toString_1 = i.toString();
            if (toString_1.length === 1) {
                round = '0000' + (i);
            }
            else if (toString_1.length === 2) {
                round = '000' + i;
            }
            else if (toString_1.length === 3) {
                round = '00' + i;
            }
            else if (toString_1.length === 4) {
                round = '0' + i;
            }
            else {
                round = toString_1;
            }
            result.push({
                ProductID: 'PRO-' + round,
                Year: "FY " + (dt + 2013),
                Price: Math.round(Math.random() * 5000) + 5000,
                Sold: Math.round(Math.random() * 80) + 10,
            });
            if (dt / 4 == 1) {
                dt = 0;
            }
        }
        return result;
    };    
    var pivotObj = new ej.pivotview.PivotView({
        height: 300,
        enableVirtualization: true,
        enginePopulated: function () {
            if (fieldListObj) {
                fieldListObj.update(pivotObj);
            }
        },
    });
    pivotObj.appendTo('#PivotTable');

    var fieldListObj = new ej.pivotview.PivotFieldList({
        dataSourceSettings: {
            dataSource: data(1000),
            rows: [{ name: 'ProductID' }],
            columns: [{ name: 'Year' }],
            values: [{ name: 'Price', caption: 'Unit Price' }, { name: 'Sold', caption: 'Unit Sold' }]
        },
        renderMode: 'Fixed',
        enginePopulated: function () {
            fieldListObj.updateView(pivotObj);
        },
        load: function () {
            this.pivotGridModule = pivotObj;
            //Assigning report to pivot table component.
            pivotObj.dataSourceSettings = fieldListObj.dataSourceSettings;
            //Generating page settings based on pivot table component’s size.
            pivotObj.updatePageSettings(true);
            //Assigning page settings to field list component.
            fieldListObj.pageSettings = pivotObj.pageSettings;
        },
    });
    fieldListObj.appendTo('#Static_FieldList');

<!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/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div id="container">
        <div id="PivotTable"></div>
        <br>
        <div id="Static_FieldList"></div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

You can also explore our Virtual Scrolling in JavaScript PivotTable that showing a large number of records.

Contents

Virtual scrolling
Data Compression
Virtual scrolling for static field list

Contents

Virtual scrolling
Data Compression
Virtual scrolling for static field list

Please provide additional information

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