How to Use the Wijmo OLAP Component in React

July 28 2023

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 that may normally be missed if you’re using a traditional data table.

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

In this article, we’ll be outlining how to do the following using Wijmo’s React 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

If you’d like to download the finished project to aid you in following along, you can find the source code for the application here. Now, let’s get to work!

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.

Loading Data with the Pivot Engine

Before we get into implementing Wijmo’s React pivot table, PivotGrid, we’ll need to load all of the required resources into the React application. The first thing that we’ll need to do is install the Wijmo UI component library, which we’ll do via NPM. Inside your React application, run the following command:

npm i @grapecity/wijmo.react.all

This will install the Wijmo library in your application. You may have noticed that we specify a react package in the installation path; Wijmo supports Angular, React, and Vue along with its core JavaScript library, so we’re specifying the react package so that we don’t install any unnecessary files.

Next, we’ll need to include Wijmo’s CSS file, as well as import the correct Wijmo modules into our application. Open up the App.js file and include the following:

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

import * as wjOlap from '@grapecity/wijmo.olap';
import * as wjcOlap from '@grapecity/wijmo.react.olap';

import { useState } from 'react';

function App() {...}

export default App;

Great! We’ve included all of the relevant Wijmo files we’ll need. One final thing that we’ll do is generate some data, which we’ll use to populate the PivotGrid. Inside the src folder, create a new file called data.js and add the following code to the file:

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);
}

export function getOrderList(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;
}

The data here isn’t really relevant; it's just mock data for the purposes of showing the functionality of the components, so I’m not going to spend much time on it. What’s important to know is that we’ve got a large set of data, which will be used to populate the components.

Now with the application set up, we can finally get into implementing Wijmo’s React pivot table, PivotGrid.

Open up your application’s App.js file and add the following code:

import './App.css';
import '@grapecity/wijmo.styles/wijmo.css';
import { getOrderList } from './data';

import * as wjOlap from '@grapecity/wijmo.olap';
import * as wjcOlap from '@grapecity/wijmo.react.olap';

import { useState } from 'react';

function App() {
  const [ng, setNG] = useState(new wjOlap.PivotEngine({
    itemsSource: getOrderList(1000)
  }));
  return (...);
}

export default App;

As you can see, it's only a few lines of code. But we must explain what’s going on here. In the file, we’re initializing a Wijmo component called PivotEngine. The PivotEngine handles the management of the data, and without the PivotEngine, Wijmo’s PivotGrid and PivotPanel controls wouldn’t be able to 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 that we want you to manage.” In the case of this application, we’re calling the getOrdersList() function from our service, which will return 1000 rows of data to load.

Now that we’ve got the PivotEngine loaded with data, it’s time to display it 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 PivotPanel and PivotGrid.

We’ve previously mentioned Wijmo’s PivotGrid, but we haven’t talked much 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 App.js file and add the following code:

function App() {
  const [ng, setNG] = useState(new wjOlap.PivotEngine({
    itemsSource: getOrderList(1000)
  }));
  return (
    <div className="App">
      <div className='flextable'>
        <wjcOlap.PivotPanel itemsSource={ng}></wjcOlap.PivotPanel>
        <wjcOlap.PivotGrid itemsSource={ng}></wjcOlap.PivotGrid>
      </div>
    </div>
  );
}

And that’s all it takes! It really is that easy to implement Wijmo’s React 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 that we set up in the previous section, we can assign that as the itemsSource for both components.

Before running the application, we want to add a little style to clean up the layout. Open up the App.css file and add the following:

.App {
  margin: 30px;
}

.flextable {
    display: flex;
}

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

Open up the index.css file and add the following code:

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

.wj-pivotgrid {
  height: 600px !important;
}

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 PivotGrid.

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

As you can see, nothing is loaded into the 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:

Column Fields	Row Fields	Value Fields
Platform	Region	Sales
Product	Agent	Downloads
		Revenue

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 displaying in a currency format instead of an integer format.

Creating Custom Fields

Now that we’ve implemented the PivotPanel and PivotGrid, as well as 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 App.js file and add the following:

function App() {
  const [ng, setNG] = useState(new wjOlap.PivotEngine({
    itemsSource: getOrderList(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' },
    ]
  }));
  return (
    <div className="App">
      <div className='flextable'>
        <wjcOlap.PivotPanel itemsSource={ng}></wjcOlap.PivotPanel>
        <wjcOlap.PivotGrid itemsSource={ng}></wjcOlap.PivotGrid>
      </div>
    </div>
  );
}

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 React pivot table, PivotGrid, Wijmo also offers a PivotChart control to work in conjunction with the PivotPanel and the PivotGrid. Like the 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 App.js file and modify the return() method as follows:

return (
  <div className="App">
    <div className='flextable'>
      <wjcOlap.PivotPanel itemsSource={ng}></wjcOlap.PivotPanel>
      <wjcOlap.PivotGrid itemsSource={ng}></wjcOlap.PivotGrid>
    </div>
    <wjcOlap.PivotChart itemsSource={ng} showTitle={false} showLegend={'Auto'}></wjcOlap.PivotChart>
  </div>
);

And that’s it! We pass the PivotEngine in as the itemsSource for the PivotChart. Now, if you save the application and reload it, if you 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 then 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

And with that, we’ve come to the end. In this article, we’ve gone over some of the benefits of using React pivot tables 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!