How to Bind Remote Data and Enable Virtual Scrolling in JavaScript Pivot Table

Syncfusion JavaScript Pivot Table is a powerful control used to organize and summarize business data and display the result in a cross-table format. Its functionalities include data binding, drilling up and down, Excel-like filtering and sorting, editing, Excel and PDF exporting, built-in aggregations, pivot table field lists, and calculated fields. A high volume of pivot data can be loaded without any performance degradation using row and column virtualization.

It accepts input data either through local or remote data binding options:

• Local data binding: Binds data through the declared variable or a local file holding the input data.
• Remote data binding: Binds data through a service URL or dynamically downloading a file from a remote server to fetch the input data.

For remote data binding, the data received should be in one of the following formats:

• JSON (JavaScript Object Notation)
• CSV (Comma-Separated Values)

In this blog we are going to see, in detail, remote data binding used in the JavaScript Pivot Table control to fetch input data, and also examine the procedure to enable virtual scrolling for better performance.

Let’s get started!

Remote data binding

Connecting to a remote service URL

The remote service that returns the input data can be connected with the Pivot Table by assigning the endpoint URL of the service to the url property in the dataSourceSettings. Also, the user should mention the type of the received data, which should be either CSV or JSON.

The following code example illustrates this.

let pivotObj: PivotView = new PivotView({
   dataSourceSettings: {
      url: 'https://bi.syncfusion.com/productservice/api/sales',
      type: 'CSV',
      rows: [{ name: 'Country' }],
      columns: [{ name: 'Sales Channel' }],
      values: [{ name: 'Total Cost' }]
   }
});

In the above code example, we connected the Pivot Table to the online service that returns the input data in CSV format. Similarly, we can connect to a local service by providing the localhost URL , as shown in the code below.

let pivotObj: PivotView = new PivotView({
  dataSourceSettings: {
     url: 'https://localhost:5001/productservice/api/sales',
     type: 'CSV',
     rows: [{ name: 'Country' }],
     columns: [{ name: 'Sales Channel' }],
     values: [{ name: 'Total Cost' }]
   }
});

Connecting input data to a downloadable URL

As stated previously, we can connect the Pivot Table to a downloadable file (*. json or *.csv) from a service URL. To do this, assign the downloadable file URL to the url property in the dataSourceSettings.

The following code example illustrates this.

let pivotObj: PivotView = new PivotView({
  dataSourceSettings: {
     url: 'https://cdn.syncfusion.com/data/sales-analysis.json',
     type: 'JSON', //In this enum, "JSON" is default value. So, no need to mention.
     rows: [{ name: 'EnerType', caption: 'Energy Type' }],
     columns: [{ name: 'EneSource', caption: 'Energy Source' }],
     values: [{ name: 'ProCost', caption: 'Cost (MM)' }],
   }
});

Note: In order to communicate to the remote server and fetch the input data, we have used predefined adaptors available in our core library which does all the necessary operations internally. The supported adaptors for the Pivot Table are:

• OData Services
• OData V4 Services
• Web API

For more details, please refer to adaptors in JavaScript DataManager control documentation.

Virtual scrolling

Virtual scrolling is a significant feature of our JavaScript Pivot Table. It helps us easily view a huge volume of data without any performance lag. It provides only the aggregated data in the current content viewport, and the rest of the data will be brought into the viewport dynamically when scrolling. We can enable the virtual scrolling feature by setting the enableVirtualization property to true.

The following code example illustrates this.

let pivotObj: PivotView = new PivotView({
  dataSourceSettings: {
     url: 'https://bi.syncfusion.com/productservice/api/sales',
     type: 'CSV'
     rows: [{ name: 'Country' }],
     columns: [{ name: 'Sales Channel' }],
     values: [{ name: 'Total Cost' }]
   }
   enableVirtualization: true
});

Note: All the remote data source types mentioned in this blog support virtual scrolling.

Data compression

This is an important add-on to virtual scrolling that will improve its performance further. Here, all the raw data will be compressed based on its uniqueness, and the compressed data will be provided as input to the Pivot Table. The compressed (unique) data will be used for further operations at all times. This will reduce the number of iterations and thereby improve the performance of the Pivot Table.

For example, if the pivot table is connected to 1 million records of raw data which is then aggregated to 1,000 unique records, the control will be rendered faster with the aggregated data. Users can enable this option by setting the allowDataCompression property to true along with the enableVirtualization property.

The following code example illustrates this.

let pivotObj: PivotView = new PivotView({
  dataSourceSettings: {
     url: 'https://bi.syncfusion.com/productservice/api/sales',
     type: 'CSV'
     rows: [{ name: 'Country' }],
     columns: [{ name: 'Sales Channel' }],
     values: [{ name: 'Total Cost' }]
   }
   enableVirtualization: true,
   allowDataCompression: true
});

For more details, please refer to data compression in the virtual scrolling in JavaScript Pivot table documentation.

Virtual Scrolling in JavaScript Pivot Table

Limitations of using virtual scrolling with remote data source

As mentioned earlier, the purpose of virtual scrolling is to render huge amounts of data faster. But when binding a remote data source, if the server returns a large amount of input data, the user will experience performance lag. This is because of the download time of loading all the input data in the browser in order to generate aggregated data and for further processing.

Note: The delay depends on the input data size, network speed, and browser.

To overcome this limitation, you should use one of the following options:

• CSV Format
• Server-Side Engine

CSV format

CSV is the most compact format when compared to JSON. Since it is half the size of the JSON it helps to reduce the bandwidth used while transferring the data to the browser.

For more details, please refer to binding pivot table to remote CSV data documentation.

Server-side engine

Introduce a server-side engine where all the pivot calculations, filtering, sorting, and other operations are done at the server. Then, pass only the information to be displayed in the client. This will prevent transferring the entire data source to the browser, thereby reducing the network traffic and increasing the rendering performance of the Pivot Table.

You can download the server-side engine from the following NuGet package: https://www.nuget.org/packages/Syncfusion.EJ2.Pivot/.

Also, the following GitHub repository has a sample that uses the server-side engine: https://github.com/SyncfusionExamples/server-side-pivot-engine-for-pivot-table.

Conclusion

In this blog post, we’ve learned the procedure to bind remote data and enable virtual scrolling in the Syncfusion JavaScript Pivot Table and seen code examples for these features. By enabling virtual scrolling, we can optimize the loading time and bandwidth to enhance the Pivot Table performance while fetching data from web services. So, try out the methods provided in this blog post and leave your feedback in the comments section below.

Our Pivot Table is also available in our Blazor, ASP.NET (Core, MVC), Angular, React, and Vue component suites. Use them to elegantly organize and summarize business data!

For existing customers, the newest version of our JavaScript control package is available for download from the License and Downloads page. If you are not yet a Syncfusion customer, you can try our 30-day free trial to check out the available features. Also, you can try our samples at this GitHub location.

You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!

If you like this blog post, we think you’ll also like the following:

• Salient Features of Pivot Chart View in Essential JS 2 Pivot Table [Blog]
• Filtering Reports in JavaScript Pivot Table Using Advanced Excel-like Filters [Blog]
• How to create Pivot Table with OLAP Data in JavaScript [Blog]
• JavaScript Succinctly [Ebook]

References

• Steps to quickly render Pivot Table component while binding huge amount of data [Blog]
• Data Binding Documentation
• Virtual Scrolling Documentation