React Pivot Table with AG Grid and Cube.js 🔢
Tags: webdev, react, javascript, tutorial
Published: 2021-03-23 16:04:27

*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](https://react-pivot-table-demo.cube.dev), 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](https://en.wikipedia.org/wiki/Pivot_table), 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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/upcvyrhxaft6eb9fsyp1.png)

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:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g29u48y40h6kftyo1q99.PNG)

**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): 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rn02rqsxwcquhfuq03gw.png)

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:**

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pq0xxdnziks2copbfy3r.png)

**Want to try it? Here's the [live demo](https://react-pivot-table-demo.cube.dev) you can use right away.** Also, the full source code is on [GitHub](https://github.com/cube-js/cube.js/tree/master/examples/react-pivot-table/).

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:

{% github cube-js/cube.js no-readme %}

[Cube.js](https://cube.dev?utm_source=dev-to&utm_medium=post&utm_campaign=react-pivot-table) 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](https://nodejs.org/en/) installed on your machine. Note that you can also [use Docker](https://cube.dev/docs/getting-started-docker?utm_source=dev-to&utm_medium=post&utm_campaign=react-pivot-table) to run Cube.js. Run in your console:

```bash
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:

```ini
# 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:

```bash
npm run dev
```

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/txdf4jgqudcm5cv1t30s.png)

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](https://cube.dev/docs/getting-started-cubejs-schema?utm_source=dev-to&utm_medium=post&utm_campaign=react-pivot-table) 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](http://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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ppnwb0pr2gjmlernv2u5.png)

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:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lqwki78rj4vnr5kmrb4a.png)

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6gqza5fniv7uqaxwk9pb.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/43ljijihw21cpknz4i22.png)

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:

```bash
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](https://www.ag-grid.com/javascript-grid/), [React](https://www.ag-grid.com/react-grid/), [Angular](https://www.ag-grid.com/angular-grid/), and [Vue](https://www.ag-grid.com/vue-grid/). 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](https://www.ag-grid.com/javascript-grid/packages-modules/), but the former way is simpler. Let's run in the console:

```bash
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](https://www.ag-grid.com/react-grid/pivoting/) 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:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zbsrmkyr5oky570tey7w.png)

**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:

```js
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:

```diff
+ 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:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pq0xxdnziks2copbfy3r.png)

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:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u7bj4b8jg8r44m6w586w.png)

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:

```js
// 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](https://www.ag-grid.com/react-grid/grid-interface/#access-the-grid--column-api-1) 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](https://www.ag-grid.com/react-grid/themes-provided/) you can import and use.

```js
// 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:

```js
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.)

```js
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:

```js
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:

```js
<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 `.

```js
{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:

```js
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:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nz0lkw3h4qikgwtm363i.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pq0xxdnziks2copbfy3r.png)

I strongly encourage you to [spend some time](https://react-pivot-table-demo.cube.dev) 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](https://www.ag-grid.com/react-grid/) to learn more about these features.

**Thank you for following this tutorial, learning more about [Cube.js](https://cube.dev?utm_source=dev-to&utm_medium=post&utm_campaign=react-pivot-table), 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](https://github.com/cube-js/cube.js) or [AG Grid](https://github.com/ag-grid/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!