Using the Ext JS PivotGrid

Jul 29, 2010

One of the new components we just unveiled for the Ext JS 3.3 beta is PivotGrid. PivotGrid is a powerful new component that reduces and aggregates large datasets into a more understandable form.

A classic example of PivotGrid's usefulness is in analyzing sales data. Companies often keep a database containing all the sales they have made and want to glean some insight into how well they are performing. PivotGrid gives the ability to rapidly summarize this large and unwieldy dataset - for example showing sales count broken down by city and salesperson.

A simple example

We created an example of this scenario in the 3.3 beta release. Here we have a fictional dataset containing 300 rows of sales data (see the raw data). We asked PivotGrid to break the data down by Salesperson and Product, showing us how they performed over time. Each cell contains the sum of sales made by the given salesperson/product combination in the given city and year.

Let's see how we create this PivotGrid:

var SaleRecord = Ext.data.Record.create([
    {name: 'person',   type: 'string'},
    {name: 'product',  type: 'string'},
    {name: 'city',     type: 'string'},
    {name: 'state',    type: 'string'},
    {name: 'month',    type: 'int'},
    {name: 'quarter',  type: 'int'},
    {name: 'year',     type: 'int'},
    {name: 'quantity', type: 'int'},
    {name: 'value',    type: 'int'}
]);

var myStore = new Ext.data.Store({
    url: 'salesdata.json',
    autoLoad: true,
    reader: new Ext.data.JsonReader({
        root: 'rows',
        idProperty: 'id'
    }, SaleRecord)
});

var pivotGrid = new Ext.grid.PivotGrid({
    title     : 'Sales Performance',
    store     : myStore,
    aggregator: 'sum',
    measure   : 'value',
    
    leftAxis: [
        {dataIndex: 'person',  width: 80},
        {dataIndex: 'product', width: 90}
    ],
    
    topAxis: [
        {dataIndex: 'year'},
        {dataIndex: 'city'}
    ]
});

var SaleRecord = Ext.data.Record.create([
    {name: 'person',   type: 'string'},
    {name: 'product',  type: 'string'},
    {name: 'city',     type: 'string'},
    {name: 'state',    type: 'string'},
    {name: 'month',    type: 'int'},
    {name: 'quarter',  type: 'int'},
    {name: 'year',     type: 'int'},
    {name: 'quantity', type: 'int'},
    {name: 'value',    type: 'int'}
]);

var myStore = new Ext.data.Store({
    url: 'salesdata.json',
    autoLoad: true,
    reader: new Ext.data.JsonReader({
        root: 'rows',
        idProperty: 'id'
    }, SaleRecord)
});

var pivotGrid = new Ext.grid.PivotGrid({
    title     : 'Sales Performance',
    store     : myStore,
    aggregator: 'sum',
    measure   : 'value',
    
    leftAxis: [
        {dataIndex: 'person',  width: 80},
        {dataIndex: 'product', width: 90}
    ],
    
    topAxis: [
        {dataIndex: 'year'},
        {dataIndex: 'city'}
    ]
});

The first half of this ought to be very familiar - we just set up a normal Record and Store. This is all we need to load our sample data so that it's ready for pivoting. This is all exactly the same code as for our other Store-bound components like Grid and DataView so it's easy to take an existing Grid and turn it into a PivotGrid.

The second half of the code creates the PivotGrid itself. There are 5 main components to a PivotGrid - the store, the measure, the aggregator, the left axis and the top axis. Taking these in turn:

Store - the Store we created above
Measure - the field in the data that we want to aggregate (in this case the sale value)
Aggregator - the function we use to combine data into the cells. See the docs for full details
Left Axis - the fields to break data down by on the left axis
Top Axis - the fields to break data down by on the top axis

The measure and the items in the axes must all be fields from the Store. The aggregator function can usually be passed in as a string - there are 5 aggregator functions built in: sum, count, min, max and avg.

Renderers

This is all we need to create a simple PivotGrid; now it's time to look at a few more advanced options. Let's start with renderers. Once the data for each cell has been calculated, the value is passed to an optional renderer function, which takes each value in turn and returns another value. One of the PivotGrid examples shows average heights in feet and inches but the calculated data is in decimal. Here's the renderer we use in that example:

new Ext.grid.PivotGrid({
    store     : myStore,
    aggregator: 'avg',
    measure   : 'height',
    
    //turns a decimal number of feet into feet and inches
    renderer  : function(value) {
        var feet   = Math.floor(value),
            inches = Math.round((value - feet) * 12);
            
        return String.format("{0}' {1}"", feet, inches);
    },
    //the rest of the config
});

new Ext.grid.PivotGrid({
    store     : myStore,
    aggregator: 'avg',
    measure   : 'height',
    
    //turns a decimal number of feet into feet and inches
    renderer  : function(value) {
        var feet   = Math.floor(value),
            inches = Math.round((value - feet) * 12);
            
        return String.format("{0}' {1}"", feet, inches);
    },
    //the rest of the config
});

Customising cell appearance

Another one of the PivotGrid examples uses a custom cell style. As with the renderer, each cell has the opportunity to alter itself with a custom function - here's the one we use in the countries example:

new Ext.grid.PivotGrid({
    store     : myStore,
    aggregator: 'avg',
    measure   : 'height',
    
    viewConfig: {
        getCellCls: function(value) {
            if (value < 20) {
                return 'expense-low';
            } else if (value < 75) {
                return 'expense-medium';
            } else {
                return 'expense-high';
            }
        }
    },
    //the rest of the config
});

new Ext.grid.PivotGrid({
    store     : myStore,
    aggregator: 'avg',
    measure   : 'height',
    
    viewConfig: {
        getCellCls: function(value) {
            if (value < 20) {
                return 'expense-low';
            } else if (value < 75) {
                return 'expense-medium';
            } else {
                return 'expense-high';
            }
        }
    },
    //the rest of the config
});

Reconfiguring at runtime

A lot of the power of PivotGrid is that it can be used by users of your application to summarize datasets any way they want. This is made possible by PivotGrid's ability to reconfigure itself at runtime. We present one final example of a PivotGrid that can be reconfigured at runtime. Here's how we perform the reconfiguration:

//the left axis can also be changed
pivot.topAxis.setDimensions([
    {dataIndex: 'city', direction: 'DESC'},
    {dataIndex: 'year', direction: 'ASC'}
]);

pivot.setMeasure('value');
pivot.setAggregator('avg');

pivot.view.refresh(true);

//the left axis can also be changed
pivot.topAxis.setDimensions([
    {dataIndex: 'city', direction: 'DESC'},
    {dataIndex: 'year', direction: 'ASC'}
]);

pivot.setMeasure('value');
pivot.setAggregator('avg');

pivot.view.refresh(true);

It's easy to change the axes, dimension, aggregator and measure at any time and then refresh the data. The calculations are all performed client side so there is no need for another round-trip to the server when reconfiguring. The example linked above gives an example interface for updating a PivotGrid, though anything that can make the API calls above could be used.

I hope you enjoy the new components in this Ext JS 3.3 beta and look forward to comments and suggestions. Although we're only at beta stage I think the additions are already quite robust so feel free to stress-test them.

What to Read Next

To expand your knowledge on enhancing Ext JS applications, consider exploring Ext.ux.Exporter - export any Grid to Excel or CSV, which provides insights into exporting data directly from store-based components. Additionally, you might find Using the ExtJS Row Editor helpful for improving grid editing functionality by allowing multiple fields to be edited simultaneously.