An Epic JavaScript Pivot Table Plugin – PivotTable.js

Recently I ran into the need to slice and dice data in a way that was extremely long-winded and overly complicated with code. This is the moment I discovered PivotTable.js, the answer to all my data reporting problems. In this article, I want to run through why using an online-pivot-table is incredible. Not only can you slice and dice data with an unlimited array of variations, but it saves a vast amount of time raw coding for end-user reporting.

First and foremost, I must credit the author of this excellent JS library, Nicolas Kruchten, this is the brains behind the library, and you can follow him on Twitter here!

So, if you know what a Pivot is, you know, but if you’re sat thinking WTF is a pivot, it looks like this demo below!

PivotJS Demo
PivotJS Demo [Credit: https://github.com/nicolaskruchten/pivottable]

It may not get you excited straight away, but by the end of this article, I think you will undoubtedly be thinking of giving it a go.

I set up a CodePen earlier loaded with around 500 records of dummy sales data so we can get straight into the nitty-gritty of this library. I’ll show how I set it up later on in the article. You can see it here

And just for reference, this is part of the dummy data, so that we are all aware of the fields present

{
            "Region": "Europe",
            "Country": "Portugal",
            "Item Type": "Cereal",
            "Sales Channel": "Offline",
            "Order Priority": "C",
            "Order Date": "4/10/2014",
            "Order ID": 811546599,
            "Ship Date": "5/8/2014",
            "Units Sold": 3528,
            "Unit Price": 205.7,
            "Unit Cost": 117.11,
            "Total Revenue": 725709.6,
            "Total Cost": 413164.08,
            "Total Profit": 312545.52
        }

PivotJS Features

Basic Initialization

So, if we take a look at the PivotJS below, it has been initialized with the basic of settings, the only option I’ve defined is the row option, let’s look at the following points

  • When initially loaded, we get all the fields from the object in the left-hand pane plus any fields in the middle that we have defined as ‘rows.’ Each of these fields is from the object we pass in when setting up the PivotUI instance.
  • Each of these fields that little arrows to the right of them, these are filters, and if you click them, you will be presented with every single value that the field has in its data set.
  • On the far right, because I’ve defined ‘Country’ as the row, we can see the list of countries in the data and a ‘Total’ which is the count of records for this country.
  • You will also see two drop-down, select fields, ‘Table’ & ‘Count’ – We will go into these later.

pivotjs basic settings

 

Sorting & Filtering

So, if we look at the image above, the field that we have selected to be in the rows pane is Country. What if we only want to list a few of these countries? Well, it’s effortless, no need for any special tricks. Just click on the arrow drop down and either deselect all countries you don’t want to show or click the ‘Select None’ button and then tick the countries you want to show.

Check out the quick video below.

 

What if we want to sort this data by the count of records for each country? Well, we would click the little double arrow icon until it changes to the up-arrow or down-arrow, of course representing descending and ascending, respectively. Here’s another quick video showing how to sort the data by the total counts.

Check out the quick video below.

 

Slicing & Dicing Data

Now, with all these fields available in the very left-hand pane, we can slice and dice the data in any way we feel necessary. Ok, so let’s say we want to group all these countries into the Region that they fit into. No need for any third-party libraries to group data or even inside an API SQL call. All we do is drag in the region above the country field for it to automatically snap into groups. See the demo below

This method of grouping can be done in an unlimited amount of different variations, it completely depends on what you want to see and how you want to see it.

In addition to the above, what about if we want to see the type of units sold to each region and how many sales there have been? And what if we want to plot this horizontally too? Well, check out the demonstration below.

Aggregating Data

So in many cases, just the count of records isn’t the most helpful for reporting. In the basic pivot, I’ve demonstrated, the total columns are just counts of records. But we have more information within the data that we may want to total up, in this example specifically, fields like unit cost, units sold, total revenue, etc.

PivotJS has an excellent aggregator drop-down option, which you can select an array of options to be applied to specific fields.

Options such as

  • Sum
  • Median
  • Average
  • Min
  • Max
  • List unique values
  • Sum over sum

And many more to choose from too.

So let’s see one of these in action. Let’s say we want to sum the total unit cost that have been sold to each region grouped by the item type. Then we can construct this view as shown in the demo below –

 

 

With the data grouped this way and the sum applied, we can quickly see the cost per item, per region and grand totals for each region and even grand totals of the item type overall. Now, that is a lot of useful information for the few drag-and-drops and clicks of a button!

Visualizing Data

PivotJS has another hidden gem that can be utilized and the drop of a hat. The Pivot has interchangeable ‘renderers’, which are basically different types of visuals. E.g., a table, a heatmap, a bar chart, etc. One of these renderers, in particular, is extremely useful, it’s the Heatmap renderer. After activating this option, you can dissect the data from smallest to largest by color.

Here is the Pivot from the Aggregating demo with a Heatmap applied.

pivotjs heatmap

As you can see, instantly, the smaller values are a light shade of red, and the higher values are a darker shade. This is excellent color management and great for report analysis.

Another one of the renderers, which is unique if I must say, is a ‘Table Bar Chart’, this option puts an appropriately skewed bar in each of the table cells, representing the value. Have a look at the screenshot below

pivot table bar chart

 

Additional Visualization Plugins

You can plugin some other renderers with pivot, things like bar, line, area, stacked bar charts, and more. It takes a little extra configuration, but in the grand scheme of things, you get a whopping bang for the time spent! (I’ll show how to set this up later)

Another one of these renderers is a Bar Chart, but there will need to be a little adjustment to the current pivot settings. Check out the video below on how to graph out the unit costs by item type.

Check out the video below with the additional ‘plotly’ renderers –

 

As you can see demonstrated in the video, switching between visualizations is a breeze, and they are also rendered extremely quickly too. No messing about, no code change, just pure ease.

Plotly isn’t the only ‘renderer’ that works with PivotJS either, and you can plugin d3, c3 and Google Charts which can all be seen in action in the following demo links –

 

So how can I use it?

If you want to set this up on your own machine or personal website, you can quickly get set up by using all of the CDNJS references.

A quick side note, PivotJS depends on JQuery & JQuery UI, so these must be included too if you haven’t already got them.

Sample Data

First up, we need some data that we want plot inside the pivot, for this I grabbed some dummy sales data that looks along the lines of below (I’ve cut this down to two records of data so that it can actually be shown) –

var salesPivotData = [
        {
            "Region": "Australia and Oceania",
            "Country": "Kiribati",
            "Item Type": "Cereal",
            "Sales Channel": "Offline",
            "Order Priority": "L",
            "Order Date": "7/24/2012",
            "Order ID": 905392587,
            "Ship Date": "8/16/2012",
            "Units Sold": 4641,
            "Unit Price": 205.7,
            "Unit Cost": 117.11,
            "Total Revenue": 954653.7,
            "Total Cost": 543507.51,
            "Total Profit": 411146.19 },

        {
            "Region": "Asia",
            "Country": "Cambodia",
            "Item Type": "Snacks",
            "Sales Channel": "Online",
            "Order Priority": "C",
            "Order Date": "3/25/2012",
            "Order ID": 990708720,
            "Ship Date": "5/4/2012",
            "Units Sold": 1581,
            "Unit Price": 152.58,
            "Unit Cost": 97.44,
            "Total Revenue": 241228.98,
            "Total Cost": 154052.64,
            "Total Profit": 87176.34 },
    ];

HTML & JavaScript

Next, we need to reference all the scripts and CSS files that are required to run the pivot library.

Add the following into your HTML <head>

<link rel='stylesheet' href='https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.23.0/pivot.min.css'>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.23.0/pivot.min.js'></script>
    <script src='https://cdn.plot.ly/plotly-basic-latest.min.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.22.0/plotly_renderers.min.js'></script>

Then add the following HTML to the body of your page.

<div style="margin: 1em;" id="output"></div>

And finally, we define the pivot options and call the pivotUI!

$("#output").pivotUI(
        salesPivotData, {
            rows: ["Country"],
            cols: [],
               renderers: $.extend(
                   $.pivotUtilities.renderers,
                   $.pivotUtilities.plotly_renderers,

               )
        });

 

And that is it, barely any code, simple markup, and the pivot will render.

To finish this up, here is the full source code, feel free to copy and paste.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>JS Pivot</title>
    <link rel='stylesheet' href='https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.23.0/pivot.min.css'>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.23.0/pivot.min.js'></script>
    <script src='https://cdn.plot.ly/plotly-basic-latest.min.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.22.0/plotly_renderers.min.js'></script>

</head>
<body>
<div style="margin: 1em;" id="output"></div>

<script>
    var salesPivotData = [
        {
            "Region": "Australia and Oceania",
            "Country": "Kiribati",
            "Item Type": "Cereal",
            "Sales Channel": "Offline",
            "Order Priority": "L",
            "Order Date": "7/24/2012",
            "Order ID": 905392587,
            "Ship Date": "8/16/2012",
            "Units Sold": 4641,
            "Unit Price": 205.7,
            "Unit Cost": 117.11,
            "Total Revenue": 954653.7,
            "Total Cost": 543507.51,
            "Total Profit": 411146.19 },

        {
            "Region": "Asia",
            "Country": "Cambodia",
            "Item Type": "Snacks",
            "Sales Channel": "Online",
            "Order Priority": "C",
            "Order Date": "3/25/2012",
            "Order ID": 990708720,
            "Ship Date": "5/4/2012",
            "Units Sold": 1581,
            "Unit Price": 152.58,
            "Unit Cost": 97.44,
            "Total Revenue": 241228.98,
            "Total Cost": 154052.64,
            "Total Profit": 87176.34 },
    ];


    $("#output").pivotUI(
        salesPivotData, {
            rows: ["Country"],
            cols: [],
               renderers: $.extend(
                   $.pivotUtilities.renderers,
                   $.pivotUtilities.plotly_renderers,

               )
        });
</script>
</body>
</html>

Summary

I could go on and on about further features with this library, but I think there is a lot to take in here. The examples page of PivotJS provides lots of various examples, all with different characteristics, showing it’s capability. From the demos on this page, you can quickly understand why using this type of tool can maximize reporting on so many levels. Not only is it useful for developers but the end-user too.

Whether your data is in an API or you load it directly from a database, that is pretty much all you need to do, the end-user then has the full flexibility to build their reports for analysis. It virtually saves so much time on both ends of the spectrum.

The initial configuration of this library is super-straight forward, with the vast amount of demos available on the examples page and the informative documentation that will help you customize the pivots even further.

All in all, this library is excellent. Have fun using it!

Leave a Reply