Documentation

Introduction

The jQWidgets Pivot Grid for jQuery is a lightweight and powerful data visualization widget written entirely in JavaScript. It offers many advanced features and is higly customizable to different needs.

The jquery pivot grid allows visualization of multi-dimensional data and hierarchical data structures. It supports multiple different aggregation functions and developers can also provide user-defined aggregations. The pivot grid for jQuery also ships with a pivot table designer widget. The pivot table designer allows you to visually configure the pivot columns, rows, aggregated values and multiple different settings. You can also configure pivot rows and columns text alignment settings and various text formatting options on the pivot cells.

The jQuery pivot grid and the pivot designer also allow the user to configure pivot table filters both programmatically and visually using drag and drop operations and filter selection options.

You can use the PivotGrid widget to add interactive pivot tables to your website, build custom dashboards, or use it in your mobile applications. The pivot grid widget offers excellent cross-browser compatibility and works well with both desktop and mobile browsers.

Basic concepts

Before you start with the Pivot Grid widget, you should be familiar with pivot tables and understand when to use regular data grids to display table data, and when to switch to the pivot grid and pivot tables. The following article is a good entry-level introduction to pivot tables: Wikipedia: Pivot tables

Generally the pivot grid will contain the following elements:
  • Pivot Rows - the unique values of the source columns which form the rows hierarchy.
  • Pivot Columns - the unique values of the source columns which form the rows hierarchy
  • Pivot Values - the aggregation funciton headers. They can be displayed below the columns or on the right side of the rows.
  • Pivot Cells - each pivot cell contains the aggregated value for the respective row, column and aggregation function.

Getting started

The pivot grid is entirely agnostic to the data source. You can use any data source to retrieve data using the jqxDataAdapter component and then pass it to the jqxPivot adapter which handles all aggregations. Finally, the pivot grid displays the aggregated data prepared by the jqxPivot adapter. Below is the step by step process:

Step 1 - Create a HTML file and include the required scripts/format

<html>
   <head>
   <title>JavaScript PivotGrid - Getting started</title>
   <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
   <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
   <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script> 
   <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
   <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
   <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
   <script type="text/javascript" src="../../jqwidgets/jqxpivot.js"></script> 
   <script type="text/javascript" src="../../jqwidgets/jqxpivotgrid.js"></script>
   <script type="text/javascript">
      $(document).ready(function(){
         // your script code will go here
      });
   </script>
   <script async src="https://www.googletagmanager.com/gtag/js?id=G-2FX5PV9DNT"></script><script>window.dataLayer = window.dataLayer || [];function gtag(){dataLayer.push(arguments);}gtag('js', new Date());gtag('config', 'G-2FX5PV9DNT');</script></head>
   <body>
      <div id="myPivotGrid" style="height: 300px; width: 500px;">
      </div>
   </body>
</html>

Step 2 - Create some sample data or use an existing data set

var data = new Array();
var lib =
[
   "Angular", "React", "VUE", "jQuery"
];
var team =
[
   "Team 1", "Team 2", "Team 3", "Team 4"
];
var month =
[
   "January", "February", "March", "April", "May", "June"
];
for (var i = 0; i < 100; i++) {
   var row = {};
   
   row["lib"] = lib[i < lib.length ? i : Math.floor(Math.random() * lib.length)];		   
   row["team"] = team[i < team.length ? i : Math.floor(Math.random() * team.length)];
   row["month"] = month[i < month.length ? i : Math.floor(Math.random() * month.length)];		   
   row["bugs"] = Math.round(Math.random() * 10);
   row["fixed"] = Math.round(Math.random() * 10);
   
   data.push(row);
}

Step 3 - Create a data adapter to load the data from your data set

// create a data source and data adapter
var source =
{
   localdata: data,
   datatype: "array",
   datafields:
   [
      { name: 'lib', type: 'string' },
      { name: 'team', type: 'string' },
      { name: 'month', type: 'string' },
      { name: 'bugs', type: 'number' },
      { name: 'fixed', type: 'number' }
   ]
};
var dataAdapter = new $.jqx.dataAdapter(source);
dataAdapter.dataBind();

Step 4 - Create a pivot table data source with the desired pivot columns, pivot rows and pivot values

// create a pivot data source from the dataAdapter
var pivotDataSource = new $.jqx.pivot(
   dataAdapter,
   {
     pivotValuesOnRows: false,
     rows: [{ dataField: 'month' }, { dataField: 'team'}],
     columns: [{ dataField: 'lib'}],
     values: [
       { dataField: 'bugs', 'function': 'sum', text: 'New bugs'},
       { dataField: 'fixed', 'function': 'sum', text: 'Fixed bugs' }
     ]
   }
);

Step 5 - Inititialize the pivot grid and pass the pivot adapter as a source

$('#myPivotGrid').jqxPivotGrid(
   {
      source: pivotDataSource,
      treeStyleRows: true,
      multipleSelectionEnabled: true
   }
);

Below is the pivot grid created with the sample data and settings


Pivot Grid Features

The jQuery PivotGrid Widget supports the following features:
  • Binding to any tabular data source via the jqxDataAdapter
  • Expandable rows and columns
  • Pivot table filters
  • Sorting rows and columns by the aggregated values in the pivot grid cells
  • Visual pivot table designer
  • Columns resize and rows resize
  • OLAP and Compact/Tree style rows
  • Automatic Sub-Totals and Grand-Totals
  • Built-in data aggregation functions: count, sum, min, max, product, average
  • Support for custom/user-defined aggregation functions
  • Selection of pivot rows, columns and individual cells
  • Multiple pivot rows, columns and cells selection
  • Keyboard navigation
  • Built-in themes
  • Custom styles of rows, columns and cells using standard CSS
  • Custom renderer functions for pivot rows, columns and cells