React Pivot Table with AG Grid and Cube.js 🔢

TL;DR: In this tutorial, we'll learn how to add a pivot table to a React app using AG Grid, the self-proclaimed "best JavaScript grid in the world", on the front-end and Cube.js, an analytical API platform, on the back-end. We'll build a pivot table data visualization, explore the features of AG Grid, and learn why Cube.js is a great fit for AG Grid.

What is a pivot table?

Pivot tables, also known as multi-dimensional tables or cross-tables, are tables that display the statistical summary of the data in usual, flat tables. Often, such tables come from databases, but it's not always easy to make sense of the data in large tables. Pivot tables summarize the data in a meaningful way by aggregating it with sums, averages, or other statistics.

Here's how a pivot table is explained in Wikipedia. Consider you have a flat table like this with e-commerce T-shirt inventory data: regions, ship dates, units, prices, etc.

An inventory might be overwhelmingly lengthy, but we can easily explore the data with a pivot table. Let's say we want to know how many items were shipped to each region on each date. Here's the pivot table that answers exactly to this question:

Analytics 101. Note that how many items is an aggregated, numerical value — a sum of items that were shipped. In analytical applications, such aggregated values are called "measures". Also note that each region and each date are categorial, textual values that be can enumerate. In analytical apps, such categorial values are called "dimensions".

Actually, that's everything one should know about data analytics to work with pivot tables. We'll use this knowledge later.

Why AG Grid?

AG Grid is a feature-rich implementation of a JavaScript data table. It supports React, Angular, and Vue as well as vanilla JavaScript. Honestly, it's no exaggeration to say that it contains every feature possible (for a data table):

AG Grid's authors emphasize that it's particularly useful for building enterprise applications. So, it's understandable that it comes in two versions:

• free and open-source, MIT-licensed Community version
• free-to-evaluate but paid and non-OSS Enterprise version

Almost all features are included in the Community version, but a few are available only as a part of the Enterprise version: server-side row model, Excel export, various tool panels, and — oh, my! — pivoting and grouping.

It's totally okay for the purpose of this tutorial, but make sure to purchase the license if you decide to develop a production app with an AG Grid pivot table.

Here's what our end result will look like:

Want to try it? Here's the live demo you can use right away. Also, the full source code is on GitHub.

Now we're all set, so let's pivot! 🔀

How to Create an Analytical API

Pivot tables are useless without the data, and the API is where the data comes from in a real-world app. And the more data we have, the better it is.

So, what are we going to do? We'll use Cube.js:

cube-js / cube.js

📊 Cube — Open-Source Analytics API for Building Data Apps

Cube.js is an open-source analytical API platform. It allows you to create an API over any database and use that API in any front-end app. In this tutorial, we'll connect Cube.js to a database and we'll use the API in our React app.

Cube.js provides an abstraction called a "semantic layer," or a "data schema," which encapsulates database-specific things, generates SQL queries for you, and lets you use high-level, domain-specific identifiers to work with data.

Also, Cube.js has a built-in caching layer that provides predictable, low-latency response query times. It means that, regardless of your data volume and database, an API built with Cube.js will serve data to your app in a performant way and help create a great user experience.

Let's try it in action.

The first step is to create a new Cube.js project. Here, I assume that you already have Node.js installed on your machine. Note that you can also use Docker to run Cube.js. Run in your console:

npx cubejs-cli create react-pivot-table -d postgres

Now you have your new Cube.js project in the react-pivot-table folder containing a few files. Let's navigate to this folder.

The second step is to add database credentials to the .env file. Cube.js will pick up its configuration options from this file. Let's put the credentials from a publicly available Postgres database there. Make sure your .env file looks like this, or specify your own credentials:

# Cube.js environment variables: https://cube.dev/docs/reference/environment-variables

CUBEJS_DB_TYPE=postgres
CUBEJS_DB_HOST=demo-db.cube.dev
CUBEJS_DB_PORT=5432
CUBEJS_DB_SSL=true
CUBEJS_DB_USER=cube
CUBEJS_DB_PASS=12345
CUBEJS_DB_NAME=ecom

CUBEJS_DEV_MODE=true
CUBEJS_WEB_SOCKETS=true
CUBEJS_API_SECRET=SECRET

Here's what all these options mean:

• Obviously, CUBEJS_DB_TYPE says we'll be connecting to Postgres.
• CUBEJS_DB_HOST and CUBEJS_DB_PORT specify where our Postgres instance is running, and CUBEJS_DB_SSL turns on secure communications over TLS.
• CUBEJS_DB_USER and CUBEJS_DB_PASS are used to authenticate the user to Postgres.
• CUBEJS_DB_NAME is the database name where all data schemas and data tables are kept together.
• The rest of the options configure Cube.js and have nothing to do with the database.

The third step is to start Cube.js. Run in your console:

npm run dev

And that's it! Here's what you should see:

Great, the API is up and running. Let's move on! 🔀

How to Define a Data Schema

Before we can tinker with the data, we need to describe it with a data schema. The data schema is a high-level domain-specific description of your data. It allows you to skip writing SQL queries and rely on Cube.js to generate them for you.

As the console output suggests, please navigate to localhost:4000 — this application is Cube.js Developer Playground. It's able to generate an initial version of the data schema automatically. Go to the "Schema" tab, select all tables under "public", and click the "Generate Schema" button.

That's all. You can check that in the schema folder there's a number of files containing the data schema files: Orders.js, Products.js, Users.js, etc.

Now we have the data schema in place. Let's explore the data! 🔀

How to Explore the Data

Go to the "Build" tab, click "+ Dimension" or "+ Measure," and select any number of dimensions and measures. For example, let's select these measures and dimensions:

• Orders Count measure
• Line Items Price measure
• Line Items Quantity measure
• Products Name dimension
• Orders Status dimension
• Users City dimension

As the result, you should get a complex, lengthy table with the data about our e-commerce enterprise:

Looks interesting, right? Definitely feel free to experiment and try your own queries, measures, dimensions, time dimensions, granularities, and filters.

Take note that, at any time, you can click the "JSON Query" button and see the query being sent to Cube.js API in JSON format which, essentially, lists the measures and dimensions you were selecting in the UI.

Later, we'll use this query to fill our upcoming pivot table with data. So, let's move on and build a pivot table! 🔀

How to Build an Analytical App

Okay, I'll be honest, Cube.js Developer Playground has one more feature to be explored and used for the greater good.

Let's go to the "Dashboard App" tab where you can generate the code for a front-end application with a dashboard. There's a variety of templates for different frameworks (React and Angular included) and charting libraries but you can always choose to "create your own".

Let's choose "React", "React Antd Dynamic", "Bizcharts", and click "OK". Just in a few seconds you'll have a newly created front-end app in the dashboard-app folder. Click "Start dashboard app" to run it, or do the same by navigating to dashboard-app and running:

npm run start

Believe it or not, this dashboard app will allow you to run the same queries you've already run the Developer Playground. On the "Explore" tab, you can create a query, tailor the chart, and then click "Add to dashboard". On the "Dashboard" tab, you'll see the result.

Impressive? We'll go further than that, and replace the dashboard with the pivot table right now. 🔀

How to Add a Pivot Table

We'll need to follow a series of simple steps to add AG Grid, tune it, review the result, and understand how everything works. I should say that AG Grid has excellent documentation with versions for vanilla JavaScript, React, Angular, and Vue. However, here's an even more condensed version of the steps you need to follow to set up AG Grid.

First, let's install the AG Grid packages. Make sure to switch to the dashboard-app folder now. AG Grid can be installed via packages or modules, but the former way is simpler. Let's run in the console:

npm install --save ag-grid-enterprise ag-grid-react

Note that we're installing ag-grid-enterprise version. There's also ag-grid-community that contains a subset of the enterprise features but the pivot table feature is included in the enterprise version only. It's going to work but it will print a giant warning in the console until you obtain a license:

Second, let's create a pivot table component. Add a new file at the src/components/Grid.js location with the following contents. Basically, it sets AG Grid up, adds data from Cube.js API, and does the pivoting. It's not very lengthy, and we'll break this code down in a few minutes:

import React, { useEffect, useState } from 'react';
import { useCubeQuery } from '@cubejs-client/react';
import { Button, Space, Layout } from 'antd';
import { AgGridColumn, AgGridReact } from 'ag-grid-react';
import 'ag-grid-enterprise';
import 'ag-grid-community/dist/styles/ag-grid.css';
import 'ag-grid-community/dist/styles/ag-theme-alpine.css';

const query = {
  'order': {
    'Orders.count': 'desc',
  },
  'measures': [
    'Orders.count',
    'LineItems.price',
    'LineItems.quantity',
  ],
  'dimensions': [
    'Products.name',
    'Orders.status',
    'Users.city',
  ],
};

const Grid = () => {
  const [ rowData, setRowData ] = useState([]);
  const { resultSet } = useCubeQuery(query);

  useEffect(() => {
    if (resultSet) {
      setRowData(resultSet
        .tablePivot()
        .map(row => Object
          .keys(row)
          .reduce((object, key) => ({
            ...object,
            [key.replace('.', '-')]: row[key],
          }), {}),
        ),
      );
    }
  }, [ resultSet ]);

  const columnDefs = [
    ...query.dimensions,
    ...query.measures,
  ].map(field => ({
    headerName: field.split('.')[1],
    field: field.replace('.', '-'),
  }));

  return (
    <Layout>
      <Layout.Header style={{ backgroundColor: '#43436B' }}>
        <Space size='large'>
          <a href='https://cube.dev' target='_blank' rel='noreferrer'>
            <img src='https://cubejs.s3-us-west-2.amazonaws.com/downloads/logo-full.svg' alt='Cube.js' />
          </a>
          <Space>
            <Button href='https://github.com/cube-js/cube.js' target='_blank' ghost>GitHub</Button>
            <Button href='https://slack.cube.dev' target='_blank' ghost>Slack</Button>
          </Space>
        </Space>
      </Layout.Header>
      <div className='ag-theme-alpine' style={{ height: 700 }}>
        <AgGridReact
          defaultColDef={{
            flex: 1,
            minWidth: 150,
            sortable: true,
            resizable: true,
          }}
          aggFuncs={{
            'min': ({ values }) => values.reduce((min, value) => Math.min(min, Number(value)), 0),
            'max': ({ values }) => values.reduce((max, value) => Math.max(max, Number(value)), 0),
            'sum': ({ values }) => values.reduce((sum, value) => sum + Number(value), 0),
            'avg': ({ values }) => (values.reduce((sum, value) => sum + Number(value), 0) / values.length).toFixed(0),
          }}
          autoGroupColumnDef={{ minWidth: 250 }}
          pivotMode={true}
          sideBar={'columns'}
          rowData={rowData}
        >
          {columnDefs.map((column, i) => {
            const name = column.field.replace('-', '.');
            const isDimension = Object.values(query.dimensions).indexOf(name) !== -1;
            const isMeasure = Object.values(query.measures).indexOf(name) !== -1;

            return (
              <AgGridColumn
                key={i}
                headerName={column.headerName}
                field={column.field}
                enablePivot={true}
                enableRowGroup={isDimension}
                enableValue={isMeasure}
                pivot={column.headerName === 'status'}
                rowGroup={column.headerName === 'name'}
                allowedAggFuncs={[ 'sum', 'max', 'avg', 'min' ]}
                aggFunc={isMeasure ? 'sum' : null}
              />
            );
          })}
        </AgGridReact>
      </div>
    </Layout>
  );
};

export default Grid;

To make everything work, now go to src/App.js and change a few lines there to add this new Grid component to the view:

+ import Grid from './components/Grid';
  import './body.css';
  import 'antd/dist/antd.css';

  // ...

  const AppLayout = ({
    children
  }) => <Layout style={{
    height: '100%'
  }}>
-   <Header />
-   <Layout.Content>{children}</Layout.Content>
+   <Grid />
  </Layout>;

  // ...

Believe it or not, we're all set! 🎉 Feel free to start your dashboard-app again with npm run start and prepare to be amused. Here's our data grid:

You can even turn "Pivot Mode" off with the knob in the top right corner, remove all measures and dimensions from "Row Groups" and "Values", and behold the raw ungrouped and unpivoted data as fetched from Cube.js API:

Amazing! Let's break the code down and review the features of AG Grid! 🔀

How Everything Works

All relevant code resides inside the src/components/Grid.js component. We'll explore it from the top to the bottom.

In the imports, you can see this React hook imported from the Cube.js client React package. We'll use it later to send a query to Cube.js API:

// Cube.js React hook
import { useCubeQuery } from '@cubejs-client/react';

Next, AG Grid imports go. It has a convenient AgGridReact component that we'll use. However, in complex scenarios, you'll need to use the onGridReady callback to get access to the Grid API and tinker with it directly. Also, note that AG Grid provides style definitions and a few themes you can import and use.

// AG Grid React components & library
import { AgGridColumn, AgGridReact } from 'ag-grid-react';
import 'ag-grid-enterprise';

// AG Grid styles
import 'ag-grid-community/dist/styles/ag-grid.css';
import 'ag-grid-community/dist/styles/ag-theme-alpine.css';

Next, meet the Cube.js query in JSON format. I hope you remember this query from Developer Playground where it was available on the "JSON Query" tab:

const query = {
  'order': {
    'Orders.count': 'desc',
  },
  'measures': [
    'Orders.count',
    'LineItems.price',
    'LineItems.quantity',
  ],
  'dimensions': [
    'Products.name',
    'Orders.status',
    'Users.city',
  ],
};

Now we jump into the functional Grid component. Time for React stuff! Here we define a state variable where we'll store the rows to be displayed in our table. Also, we use the useCubeQuery hook to send the request to Cube.js API. Then, in useEffect, we get the result, transform it into tabular format with the convenient tablePivot method, and assign it to the state. (Remapping is needed because Cube.js returns column names in the Cube.measure and Cube.dimension format but AG Grid doesn't work with dots in the names.)

const [ rowData, setRowData ] = useState([]);
const { resultSet } = useCubeQuery(query);

useEffect(() => {
  if (resultSet) {
    setRowData(resultSet
      .tablePivot()
      .map(row => Object
        .keys(row)
        .reduce((object, key) => ({
          ...object,
          [key.replace('.', '-')]: row[key],
        }), {}),
      ),
    );
  }
}, [ resultSet ]);

Then we extract the column names from the dataset. We'll use them later:

const columnDefs = [
  ...query.dimensions,
  ...query.measures,
].map(field => ({
  headerName: field.split('.')[1],
  field: field.replace('.', '-'),
}));

Time for JSX! Note that the AgGridReact component is wrapped with a div.ag-theme-alpine to apply the custom Ag Grid styles. Also, note how default column styles and properties are set.

The last three lines are the most important ones because they activate the pivot table, enable a convenient sidebar you might know from Excel or similar software, and also wire the row data into the component:

<div className='ag-theme-alpine' style={{ height: 700 }}>
  <AgGridReact
    defaultColDef={{
      flex: 1,
      minWidth: 150,
      sortable: true,
      resizable: true,
    }}
    // ...
    autoGroupColumnDef={{ minWidth: 250 }}
    pivotMode={true}    // !!!
    sideBar={'columns'} // !!!
    rowData={rowData}   // !!!
  >

Here's the most complex part. To transform the row data into a pivot table, we need to specify the column or columns used on the left side and on the top side of the table. With the pivot option we specify that data is pivoted (the top side of the table) by the "status" column. With the rowGroup option we specify that the data is grouped by the "name" column.

Also, we use aggFunc to specify the default aggregation function used to queeze the pivoted values into one as sum. Then, we list all allowed aggregation functions under allowedAggFuncs.

{columnDefs.map((column, i) => {
  // ...

  return (
    <AgGridColumn
      key={i}
      headerName={column.headerName}
      field={column.field}
      enablePivot={true}
      enableRowGroup={isDimension}
      enableValue={isMeasure}
      pivot={column.headerName === 'status'}
      rowGroup={column.headerName === 'name'}
      allowedAggFuncs={[ 'sum', 'max', 'avg', 'min' ]}
      aggFunc={isMeasure ? 'sum' : null}
    />
  );
})}

Here's how these functions are implemented. Nothing fancy, just a little bit of JavaScript functional code for minimum, maximum, sum, and average:

aggFuncs={{
  'min': ({ values }) => values.reduce((min, value) => Math.min(min, Number(value)), 0),
  'max': ({ values }) => values.reduce((max, value) => Math.max(max, Number(value)), 0),
  'sum': ({ values }) => values.reduce((sum, value) => sum + Number(value), 0),
  'avg': ({ values }) => (values.reduce((sum, value) => sum + Number(value), 0) / values.length).toFixed(0),
}}

You can click on "Values" to change the aggregation function used for every column, or set it programmatically as specified above:

And that's all, folks! 🎉 Thanks to AG Grid and Cube.js, we had to write only a few tiny bits of code to create a pivot table.

I strongly encourage you to spend some time with this pivot table and explore what AG Grid is capable of. You'll find column sorting, a context menu with CSV export, drag-and-drop in the sidebar, and much more. Don't hesitate to check AG Grid docs to learn more about these features.

Thank you for following this tutorial, learning more about Cube.js, building a pivot table, and exploring how to work with AG Grid. I wholeheartedly hope that you enjoyed it 😇

Please don't hesitate to like and bookmark this post, write a comment, and give a star to Cube.js or AG Grid on GitHub. I hope that you'll try Cube.js and AG Grid in your next production gig or your next pet project.

Good luck and have fun!

Comments

[Elijah El-Haddad]

Awesome post! I wish I had this when I was first implementing AG Grid with Cube.js :-) Thanks for taking the time to put it together and explaining how the different parts work!

One small note about this part:

(Remapping is needed because Cube.js returns column names in the Cube.measure and Cube.dimension format but AG Grid doesn't work with dots in the names.)

There's actually a less well known AG Grid property called suppressFieldDotNotation, see docs here, which when enabled allows the use of dots in the field names.

[Igor Lukanin]

Wow! Thanks for the kind words, Elijah! I wish I've written it earlier too. Also, thanks for mentioning the suppressFieldDotNotation property. With it, the code would be much simpler.

So, you have a Cube.js project with AG Grid? Could you please share anything about your experience and the features of AG Grid that you especially like?