Sencha Inc. | HTML5 Apps

Blog

mzPivotGrid: A pivot table for Ext JS

March 29, 2013 | Adrian Teodorescu

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.

Pivot Grid

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:

Pivot Grid Pivot Grid Pivot Grid

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:

 
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.

 
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.

Pivot Grid

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:

 
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:

Pivot Grid 1

A sample use case

 
 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:

There are 8 responses. Add yours.

cblin

1 year ago

Why should I spend money on this when the builtin pivot grid of extjs is nearly the same :
http://dev.sencha.com/deploy/ext-3.3.0/examples/pivotgrid/simple.html

H.W.

1 year ago

> Why should I spend money on this when the builtin pivot grid of extjs is nearly the same :
> http://dev.sencha.com/deploy/ext-3.3.0/examples/pivotgrid/simple.html
*was* the same, as 3.x is abandoned/replaced with 4.x that does not contain a pivot grid at all ! It’s pretty hard to do an upgrade from 3.x to 4.x without this component.

Ira Klotzko

1 year ago

Shameful… Anyone who put in hard work for a pivot grid in 3.x can’t upgrade and now the solution seems to be to pay someone for a different implementation.

Backwards compatible fail.

suzuki1100nz

1 year ago

Sencha team
Any plans to pick up a framework implementation of the pivot grid again?
I take it the answer is No otherwise you wouldn’t have allowed this blog post?

Kazuhiro Kotsutsumi

1 year ago

I translated it into Japanese.

http://www.xenophy.com/extjsblog/6525

Provision: Japan Sencha User Group
http://www.meetup.com/Japan-Sencha-User-Group/about/

John

1 year ago

What is this ?
Now Charging for components ?

So should be expect Ext 4.3 to be full of commercial independent components ?

This makes me think that all the new good stuff on 4.3 will be commercial, and only the basic updates will be part of the open source version.

John

1 year ago

Sorry for my latest post,
I found out this is a third party component, and not Ext’s official.

suman

12 months ago

hi i want Ext JS 4.x sample project code for Eclipse

Comments are Gravatar enabled. Your email address will not be shown.

Commenting is not available in this channel entry.