Become a data guru with ag-Grid and JavaScript pivot tables

Sophia Lazarova | 14th November 2017

Pivot Table

Data is not to be stored but to be understood! By adding pivot tables to your JavaScript application, you can empower your users to make the most of their data. This article gives an insight on pivot tables, their common usage and their integration with JavaScript applications.

For your users, working with big amounts of data has never been easy. Data without insight is worthless, we end up wondering what to do with our pile of rows and records. Fear not, there is an easy way to handle your data without being a data scientist or database guru.

In Theory

For most people, pivot table is a fancy word, widely used among the Excel spreadsheet lovers. And if you are like me, you have probably Googled the meaning at least once. Well, turns out pivot tables are a really powerful feature that gives the user insight into their data. Their purpose is to summarize large amounts of data by applying operations such as averaging, sorting and grouping. As a result a pile of rows and data can quicly become a well-shaped and beautifully stuctured report.

In Practice

You are still not sure what's the actual purpose of the pivot tables? No worries, an example is worth a thousand words! Let's have a look at the following example and see how we can use pivot tables.

Plot: We have a dataset with participants in different games. The participants are located in different countries and have different yearly and monthly winnings. This data is part of a bigger dataset but we will use only some of the attributes:

  • Name
  • Country
  • Total Winnings
  • Monthly Breakdown(winnings for each month)

Task: Finding the total winnings for each country.

pipeline

Pivot Tables to the Rescue

To solve our task we will use the magic power of pivoting in ag-Grid. So in order to find the total winnings for each country we need to find all participant from a country and sum their winnings. Easy, right?

First we need to enter 'Pivot Mode' or in other words - start a pivot table.

pivot mode on

Entering 'Pivot Mode' gives us an empty table and some additional sections. Important here are these two sections:

  • Row Groups - holds the attributes by which we want to group our data. We can add attributes to this section simply by dragging them.

  • Values - holds the attributes of which we want to have aggregated values. Attibutes can be included by dragging.

pipeline

Grouping

Let's start by presenting a list of all coutries with participants.

Since we want to find the winnings of each country we should group our data by 'Country'.

grouping

Piece of cake!

Aggregation

Aggregation is used for presenting sum, average, min, etc. values of attributes with numeric values. We will use aggregation to get the sum of the winnings for each country.

aggregation

Oh well, looks like we are ready!

Now we have all the countries with their corresponding total winnings. Beautiful, isn't it? In just a few clicks we have a summarized report of our complexed data.

We can also aggregate our data by different values. Have in mind that the default aggregation value is SUM.

aggregaion values

Just as easy we can get summary not only of the total winnings but of the monthly breakdown.

pipeline

Talk technie to me!

It is clear now what pivot tables are and how we can use them, but there is still one unsolved question - how do we enable them in our JavaScript application?

As you already know we don't need to build pivot table from scratch, we can simply use the capabilities of ag-Grid.

Pivoting is, no doubt, one of the top features of ag-Grid Enterprise. Make sure you are using ag-Grid Enterprise in order to use pivoting and other high profile goods. You can find ag-Grid and ag-Grid Enterprise on npm and bower. If you have any difficulties you can refer to our getting started articles.

Once you are packed with ag-Grid Enterprise, you should simply edit the column definitions and add the enablePivot property with value true. this.columnDefs = [ {headerName: "Make", field: "make", enablePivot: true}, {headerName: "Model", field: "model", cellRendererFramework: RedComponentComponent, enablePivot: true}, {headerName: "Price", field: "price", enablePivot: true} ];

This is a simple example of enabling pivoting in ag-Grid. Of course, ag-Grid has more advanced settings for pivoting in complecated scenarios. To find out more about pivoting, read the dedicated article in our documentation.

Conclusion

Congrats, you are a master of the advanced reports now!

Pivot tables are a powerful tool which is fairly simple to use once you know it's tricks.

You can try this by yourself right away, using the ag-Grid demo. If you are in a mood for writing code you can download ag-Grid and build your own grid with pivoting.

  • npm install ag-grid-community
  • bower install ag-grid-community
If you liked this article then please share
   
Sophia Lazarova

Sophia Lazarova

Sophia recently switched her carreer path from being a developer to becoming member of the ag-Grid team as a Developer Advocate. She is interested in various areas of the programming but her tech passion is the mobile world and the mobile technologies. In her "not a techie time" you can find her buying plane tickets and planning her next adventures.