Using the Ext JS PivotGrid

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'}
    ]
});

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
});

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
});

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);

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.

9 Responses to Using the Ext JS PivotGrid

  1. Pingback: Tweets that mention A short guide on using the new #extjs 3.3 PivotGrid: -- Topsy.com

  2. Lee Rowlands says:

    Looking great, I can see the opportunity to attach DD behaviours to the cell headers and use DD to ‘reconfigure’ the grid ala Excel Pivot Tables.

  3. NS says:

    I like to see example using database i.e. Sqlite or PostgreSQL as a source of data for the Pivot Grid, Charts. etc.

  4. Filipe Nunes says:

    Hi.

    using pivot grid i have this issue with many columns, the is no horizontal scrolling .

    can you help-me ?

    Best Regards,
    Filipe Nunes

  5. Desiree says:

    Hi,

    How do we enable the horizontal scrolling?

    Thanks!

  6. John says:

    Hi, I ran into a potential problem with the pivot grid. I have a data set that does not include totals, but I have been able to ‘trick’ the grid into displaying the totals as if they were data. However, I need a way to order/change the sequence of the topAxis and leftAxis. Is there any way to make them go ‘Col1’, ‘Col3’, ‘Col2’ instead of the default ‘Col1′,’Col2′,’Col3’? Thanks for your time, and I look forward to your response.

  7. Jose Venegas says:

    Hola y como podria sumar cada grupo en una columna adicional abajo

  8. ABC says:

    Can I keep checkbixes and radios inside a pivot grid

  9. Carlos says:

    Ed, what are the differences between al the proxy types: ajax, rest, http, jsonp? Which one is the best for talking to a http backend (JBoss) running in the same server box but at a different port and using JSON for CRUD?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: