mzPivotGrid: A pivot table for Ext JS

Guest Blog Post

At mzSolutions, I work on creating components for Ext JS and Sencha Touch. In this post, I will show you how to use mzPivotGrid with Sencha Ext JS.

What is a pivot grid?

The Ext JS grid panel is perfect for displaying large sets of data, and it provides many features for all kinds of data display. One type of data display that it doesn’t provide is a pivot grid. When it comes to reporting, a pivot grid is the way to go because it helps you organize and summarize data and create reports; and it enables users to make more informed decisions about their data.

What is mzPivotGrid

mzPivotGrid is a component that you can use with Ext JS to create a pivot grid. Let’s imagine the following scenario. You have some sales data with the following fields: country, salesperson, order date, order amount and order ID. If you want to edit this data, you could easily use an Ext JS GridPanel with CellEditing or RowEditing plugins.

What would you do if you had to answer the following questions?

1. What are the order amounts of each salesperson?
2. What are the order amounts of each salesperson in a specific country?
3. How did salespeople perform in a specific year?

You could use mzPivotGrid to create those reports, and this is how they would look:

Using mzPivotGrid

mzPivotGrid inherits the Ext JS Grid Panel component, so you can use most of the features you are already familiar with: column resizing, locking, cell renderers, row/cell events etc. The mzPivotGrid can be used when you want to get summation and pivot features that aren’t provided in the default GridPanel.

To break down the dataset into salespeople and years, you just have to configure the top and left axis:

[style code sample below]

leftAxis: [{
width: 80,
dataIndex: ‘salesperson’,
header: ‘Salesperson’
}],

topAxis: [{
dataIndex: ‘year’,
header: ‘Year’,
direction: ‘ASC’
}]

Multiple levels are supported, so you can just specify them in the top and left axis. Grouping the rows and/or columns could be handy here, so you can simply do that by setting “enableGrouping” to true.

Now that you broke down the dataset on the top and left axis, it’s time to aggregate the cell values. Several kinds of aggregations are available including: sum, average, min, max, count etc. If these are not enough, you can provide your own aggregation function.

[style code sample below]

aggregate: [{
measure: ‘amount’,
header: ‘Sales’,
aggregator: ‘sum’,
align: ‘right’,
width: 85,
renderer: Ext.util.Format.numberRenderer(‘0,000.00’)
},{
measure: ‘orderid’,
header: ‘Qnt’,
aggregator: function(records, measure, matrix, rowGroupKey, colGroupKey) {
// do your own algorithm
return records.length;
},
align: ‘right’,
width: 85,
renderer: Ext.util.Format.numberRenderer(‘0,000.00’)
}]

As you have probably noticed in the above example, you could aggregate multiple data fields, say “sales” and “quantity.” You can easily achieve this by configuring all required aggregations.

Showing the grand totals for rows and/or columns is pretty easy to configure (“enableRowGrandTotals: true” or “enableColGrandTotals: true”). The group totals are also available via “enableRowSummary” and “enableColSummary” configs. Styling is also easy, and you can even define cell renderers.

The pivot grid uses any kind of Store available in Ext JS, so filtering the data is pretty easy to do on the Store object. This means you can implement a plugin to handle the filtering.

The Ext JS Data Model class is awesome, so you should use its powerful “convert” function to extend your model and break down your data in custom group intervals. Check out this example:

[style code sample below]

fields: [
{name: ‘orderid’, type: ‘int’},
{name: ‘salesperson’, type: ‘string’},
{name: ‘country’, type: ‘string’},
{name: ‘orderdate’, type: ‘date’, dateFormat: ‘d/m/Y’},
{name: ‘amount’, type: ‘int’},
{
name: ‘person-range’,
convert: function(v, record){
if(/^[a-j]/i.test(record.get(‘salesperson’))) return ‘A-J’;
if(/^[k-s]/i.test(record.get(‘salesperson’))) return ‘K-S’;
if(/^[t-z]/i.test(record.get(‘salesperson’))) return ‘T-Z’;
return v;
}
},{
name: ‘year’,
convert: function(v, record){
return Ext.Date.format(record.get(‘orderdate’), “Y”);
}
}
]

… it will look like this:

A sample use case

[style code sample below]

var pivotGrid = Ext.create(‘Mz.pivot.Grid’, {
title: ‘Pivot grid’,
height: 400,
width: 600,
enableLocking: false,
enableGrouping: true,
viewConfig: {
trackOver: true,
stripeRows: false
},
store: store,
aggregate: [{
measure: ‘amount’,
header: ‘Sales’,
aggregator: ‘sum’,
align: ‘right’,
width: 85,
renderer: Ext.util.Format.numberRenderer(‘0,000.00’)
}],
caption: ‘Sales report’,
leftAxis: [{
width: 80,
dataIndex: ‘salesperson’,
header: ‘Sales Person’
}],
topAxis: [{
dataIndex: ‘year’,
header: ‘Year’,
direction: ‘ASC’
}] });

Future

Since mzPivotGrid is a commercial product, it will constantly be improved with new plugins and features. Feel free to contact us if you need something special implemented.

To learn more about mzPivotGrid licensing, click here.

Additional resources:

• Pivot grid example used in this article
• Try some more mzPivotGrid examples
• mzPivotGrid API docs

[Add to author section]

Written by Adrian Teodorescu
Adrian Teodorescu has more than 15 years experience as a software developer and has been active in the Sencha community since 2009. His main focus is creating components for Ext JS and Sencha Touch. You can contact Adrian on his website.