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 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
andCUBEJS_DB_PORT
specify where our Postgres instance is running, andCUBEJS_DB_SSL
turns on secure communications over TLS. -
CUBEJS_DB_USER
andCUBEJS_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!
Top comments (3)
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:
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.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?
Is there a way to use grid in pivot mode, but have cube do the aggregations? It seems like from this demo, the grid is pulling the full data and running its own aggregations. Am I correct in thinking that?