How to Add a JavaScript Pivot Grid to Your Web Application

Data tables and grids have become staples in modern UI design. They allow our users to easily view large data sets, compare values, and make decisions based on empirical data. However, sometimes the data sets that we’re working with aren’t easily viewable in a standard table, or there may be instances where we want certain data excluded to make it easier to see correlations between data points. Thankfully, software engineers and computer programmers have developed a more advanced form of a data table that allows us to do just that: pivot tables.

A pivot table is a data table that aids users in tasks such as data discovery; it allows users to perform complex analytical calculations on the data set that you’ve loaded, determine what data you want to display, and determine the orientation of the data. This allows users to see trends commonly missed if you’re using a traditional data table.

Luckily, Wijmo has you covered with PivotGrid, the best JavaScript pivot table control available. It's simple to implement, easy to customize, and even has an extensive list of features that allow you to integrate it into your application easily.

In this article, we’ll be outlining how to do the following using Wijmo’s JavaScript pivot table, PivotGrid:

• Loading Data with the PivotEngine
• Setting Up the PivotPanel and PivotGrid
• Creating Custom Fields
• Using PivotCharts to Display Data in Chart Form

Want to incorporate Excel-like web-based JavaScript PivotGrids? Download Wijmo Today!

If you’d like to view the finished project’s source code to aid you in following along, you can find it on StackBlitz. Now, let’s get to work!

Loading Data with the Pivot Engine

Before we implement Wijmo’s JavaScript pivot table, PivotGrid, we’ll need to load all the required resources into the JavaScript application. If you’re using StackBlitz to build your application, you’ll need to specify which packages you want to be included as dependencies; include the following packages:

@grapecity/wijmo
@grapecity/wijmo.olap
@grapecity/wijmo.styles

If you’re not using StackBlitz, you can use NPM to install Wijmo via the following command:

npm i @grapecity/wijmo.all

Now that we’ve installed the Wijmo library, we’ll need to include Wijmo’s CSS file so that the control is styled properly. Open up the styles.css file and include the following:

@import '@grapecity/wijmo.styles/wijmo.css';

Great! We’ve included all the relevant Wijmo files we’ll need. One final thing we’ll do before implementing Wijmo’s controls is generate some data, which we’ll use to populate our JavaScript pivot table. Open up the index.js file and add the following:

var products = [
  { product: 'Wijmo', platform: 'Web' },
  { product: 'ActiveReports', platform: 'Desktop' },
  { product: 'ActiveReportsJS', platform: 'Web' },
  { product: 'ComponentOne', platform: 'Desktop' },
  { product: 'Spread', platform: 'Desktop' },
  { product: 'SpreadJS', platform: 'Web' },
  { product: 'GCDocs', platform: 'Desktop' },
];

var agents = [
  { agent: 'Ashlyn Dunlop', region: 'East' },
  { agent: 'Keith Vang', region: 'East' },
  { agent: 'Bobbi Rodrigues', region: 'West' },
  { agent: 'Charli Medina', region: 'West' },
  { agent: 'Kaitlin Salt', region: 'West' },
];

function randomInt(min, max) {
  return Math.floor(Math.random() * (max - min + 1) + min);
}

function getOrdersList(count) {
  var year = new Date().getFullYear(), data = [];
  for (var i = 0; i < count; i++) {
    let productIdx = randomInt(0, 6);
    let agentIdx = randomInt(0, 4);
    data.push({
      orderId: randomInt(1, 10000),
      platform: products[productIdx].platform,
      product: products[productIdx].product,
      agent: agents[agentIdx].agent,
      region: agents[agentIdx].region,
      date: new Date(
        year - randomInt(0, 2),
        randomInt(0, 11),
        randomInt(0, 27)
      ),
      sales: randomInt(10, 50),
      downloads: randomInt(10, 200),
      revenue: randomInt(500, 3500),
    });
  }
  return data;
}

This is what we’ll be using to populate our JavaScript pivot table; what’s important to note here is that the purpose of the getOrdersList() function is to use agents, products, and randomInt() to generate a set of sales data for us to view.

Now, with the application set up, we can finally implement Wijmo’s JavaScript pivot table, PivotGrid. Inside of the index.js file, add the following:

import * as wjOlap from '@grapecity/wijmo.olap';
import './styles.css';

var products = [...];
var agents = [...];

function randomInt(min, max) {...}

function getOrderList(count) {...}

var ng = new wjOlap.PivotEngine({
  itemsSource: getOrdersList(1000),
});

As you can see, it is only a few lines of code. But it's essential that we explain what’s going on here. In the file, we’re initializing a Wijmo component called PivotEngine. The PivotEngine handles data management; without the PivotEngine, Wijmo’s JavaScript PivotGrid and PivotPanel controls wouldn’t function. You can think of the PivotEngine as the foundation of our pivot table; without it, the table would have no data to display to the users.

Along with initializing the Wijmo component, we’re also setting its itemsSource property. This tells the engine, “Hey, this is the data we want you to manage.” In the case of this application, we’re calling our getOrdersList() function, which will return 1000 rows of data to load.

Now that we’ve got the PivotEngine loaded with data, it’s time to display that data on-screen.

Setting Up the PivotPanel and PivotGrid

Now that the PivotEngine has been set up, it's time to use it. As we mentioned in the last section, the engine manages our data. So, for users to interact with the data, we need to connect it to our UI elements: the JavaScript PivotPanel and JavaScript PivotGrid.

We’ve previously mentioned Wijmo’s JavaScript PivotGrid, but we haven’t said anything about the PivotPanel. The PivotPanel is how users interact directly with the data being displayed. This includes determining what data fields are column, row, filter, and value fields, as well as access to the Field Settings menu to customize the data of the selected field further.

Instead of any further explanation, it would be better to show you what you can do with the PivotPanel and PivotGrid. To add these to your application, open up the index.html file and add the following code:

<div class="flextable">
  <div id="pivotPanel"></div>
  <div id="pivotGrid"></div>
</div>

These divs will be used by the JavaScript PivotPanel and JavaScript PivotGrid components to render themselves on-screen. We give them an id, so the control knows which ones to use.

Now, inside of the index.js file, add the following:

import * as wjOlap from '@grapecity/wijmo.olap';
import './styles.css';

var products = [...];
var agents = [...];

function randomInt(min, max) {...}

function getOrderList(count) {...}

var ng = new wjOlap.PivotEngine({...});

var pivotPanel = new wjOlap.PivotPanel('#pivotPanel', {
  itemsSource : ng
});

var pivotGrid = new wjOlap.PivotGrid('#pivotGrid', {
  itemsSource: ng
});

And that’s all it takes! It really is that easy to implement Wijmo’s JavaScript pivot table. Like with the PivotEngine, the PivotGrid and PivotPanel both need to be supplied with an itemsSource. Unlike the PivotEngine, however, these components don’t take a simple array. They take a PivotEngine! So, using the ng variable we set up in the previous section, we can assign that as the itemsSource for both components.

One final thing before running the application is that we want to add a little style to clean up the layout. Open up the styles.css file and add the following:

.flextable {
    display: flex;
}

.wj-pivotpanel {
    width: 500px;
    margin: 0px 10px;
}

.wj-pivotgrid {
    height: 600px;
}

We’re setting up the application so that the PivotPanel and PivotGrid display side-by-side, as well as setting the width of the PivotPanel so that it makes more room for the JavaScript PivotGrid.

Now, if you run the application, you should see the following:

As you can see, nothing is loaded into the JavaScript PivotGrid; that’s to be expected. Nothing will display unless we set some fields in the PivotPanel. Let’s fix that and add a couple of fields:

As you can see via the PivotPanel, we’ve added the following fields to the various areas of the panel:

Now, we can see a breakdown of all sales metrics across regions, by agents, and even by product and platform.

One final thing to discuss before moving on is the Field Settings menu. This menu can be accessed by right-clicking any of the cells in the PivotGrid, or by right-clicking any of the field names in the PivotPanel:

Selecting the Field Settings… option will open up the Field Settings modal:

Here, we can change how the data is summarized, add calculations (such as % difference from the previous row), add filters, and even format how the data is displayed, which includes a preview based on the selected format.

Since this is the field settings menu for the Sales field, we’re just going to change the format to display in a currency format instead of a basic integer format:

As you can see, it also shows us a preview of the updated format. If we click the OK button, we should see the following:

As you can see, the Sales columns are now all displayed in a currency format instead of an integer format.

Creating Custom Fields

Now that we’ve implemented the JavaScript PivotPanel and JavaScript PivotGrid and gone over the basics of using them, it’s time to customize our data using the PivotEngine. We can do this by setting up properties manually via the PivotEngine, instead of allowing it to generate them automatically.

Open up the index.js file and add the following:

var ng = new wjOlap.PivotEngine({
  itemsSource: dataService.getOrdersList(1000),
  fields:[
    { binding: 'date', header: 'Quarter', format: '\"Q\"q yyyy' },
    { binding: 'date', header: 'Month', format: 'MMMM' },
    { binding: 'agent', header: 'Agent' },
    { binding: 'region', header: 'Region' },
    { binding: 'platform', header: 'Platform' },
    { binding: 'product', header: 'Product' },
    { binding: 'sales', header: 'Sales', format: 'c2' },
    { binding: 'downloads', header: 'Downloads', format: 'n0' },
    { binding: 'revenue', header: 'Revenue', format: 'c2' },
  ]
});

As you can see, the fields property takes an array of objects, which is used to create the various fields that users can pick from in the PivotPanel. By default, these objects only need to have two properties: a binding and a header property. The binding property tells the engine which property name this field will be bound to. For Agent, Region, Platform, and Product, these are the only properties that we’re setting.

However, there’s another property that we can set: the format property. Like how users can use the Field Settings menu to change the formatting, developers can set the default format in which we want to display the field.

Along with setting the format, we can actually create multiple fields from the same data point and set different formats for both. In this application, we’re setting up quarterly and monthly date fields. Now, if we save this and open up the application, we’ll select a few fields, and you should see the following:

As you can see, we’ve created a quarterly and monthly field using a single data point. Now, users can view an even greater breakdown, seeing sales data breakdowns by quarterly and monthly views.

Using PivotCharts to Display Data in Chart Form

Along with Wijmo’s JavaScript PivotGrid, Wijmo also offers a PivotChart control to work in conjunction with the PivotPanel and the PivotGrid. Like the JavaScript PivotGrid, the PivotChart will automatically update to reflect changes in areas of the PivotPanel.

Adding a PivotChart is just as simple as adding the PivotGrid and PivotPanel. Open up your index.html file and modify it as follows:

<div class="flextable">
  <div id="pivotPanel"></div>
  <div id="pivotGrid"></div>
</div>
<div id="pivotChart"></div>

Now, inside of the index.js file, add the following:

import * as wjOlap from '@grapecity/wijmo.olap';
import './styles.css';

var products = [...];
var agents = [...];

function randomInt(min, max) {...}

function getOrderList(count) {...}

var ng = new wjOlap.PivotEngine({...});

var pivotPanel = new wjOlap.PivotPanel('#pivotPanel', {});

var pivotGrid = new wjOlap.PivotGrid('#pivotGrid', {});

var pivotChart = new wjOlap.PivotChart('#pivotChart', {
  itemsSource: ng,
  showTitle: true,
  showLegend: 'Auto'
});

And that’s it! We simply pass the PivotEngine in as the itemsSource for the PivotChart. Now, if you save the application, reload it, and add any fields to the Value area, you’ll see them reflected in the PivotChart. Take a look at the image below:

As you can see, we’ve set the Quarter and Platform fields as row fields and the Sales and Downloads fields as value fields. The PivotChart then shows a breakdown, similar to how the PivotGrid does, of the data we’ve selected inside the PivotPanel.

Conclusion

In this article, we’ve gone over some of the benefits of using JavaScript PivotGrid and how you can use Wijmo to implement your own pivot table, pivot chart, and pivot panel to manage the data.

If you’d like to try out Wijmo, you can download the entire UI component library, available in pure JavaScript, Angular, React, and Vue, here.

Happy coding!

Want to incorporate Excel-like web-based JavaScript PivotGrids? Download Wijmo Today!