5 Powerful Ext JS Pivot Grid Features

The Ext JS Pivot Grid provides a simple way to condense many data points into a format to quickly draw insights and trends from data. In this post we cover 5 powerful Ext JS Pivot Grid features.

Table of Contents

1. Axis and Aggregation

The Pivot Grid relies on two pieces that extend the native grid panel:

Axis: allows you to determine row and column placement
Aggregation: manages the grouping calculations

These pieces need to be defined on the pivot matrix object used by the Pivot Grid.

How to:
1. Create a Pivot Grid with “matrix” object

Ext.define('Fiddle.view.pivot.Grid', {
	extend: 'Ext.pivot.Grid',
	xtype: 'configurable-pivot-grid',
	controller: 'pivotgrid',
 
	title: 'Pivot Grid',
 
	matrix: {
    	store: {
            type: 'sales'
    	}
	}
});

2. Add axis configuration

matrix: {
	store: {
    	type: 'sales'
	},
	leftAxis: [{
    	dataIndex: 'person',
    	header: 'Person'
	},{
    	dataIndex: 'year',
    	header: 'Year'
	}],
	topAxis: [{
    	dataIndex: 'continent',
    	header: 'Continent'
	}, {
    	dataIndex: 'country',
    	header: 'Country'
	}]
}

3. Add aggregation configuration

matrix: {
	store: {
    	type: 'sales'
	},
	leftAxis: [{
    	dataIndex: 'person',
    	header: 'Person'
	},{
    	dataIndex: 'year',
    	header: 'Year'
	}],
	topAxis: [{
    	dataIndex: 'continent',
    	header: 'Continent'
	}, {
    	dataIndex: 'country',
    	header: 'Country'
	}],
	aggregate: [{
    	dataIndex: 'value',
    	header: 'Total',
    	aggregator: 'sum'
	}]
}

Sencha Fiddle:

Many in-built methods are available such as sum, average, min and so on but it is possible to define a custom method to aggregate data:

aggregator: 'myOwnFn'

2. Filtering

Filtering by label

Filtering to display the year 2016.

leftAxis: [{
    dataIndex: 'person',
    header: 'Person'
}, {
    dataIndex: 'year',
    header: 'Year',
    filter: {
        type: 'label',
        operator: '=',
        value: 2016
    }
}]

Sencha Fiddle:

Filtering by value

Filtering to display the 3-best year

matrix: {
	store: {
    	type: 'sales'
	},
	aggregate: [{
    	dataIndex: 'value',
    	header: 'Total',
    	aggregator: 'sum',
    	id: 'agg',
	}],
	leftAxis: [{
    	dataIndex: 'person',
    	header: 'Person'
	}],
	topAxis: [{
    	dataIndex: 'year',
    	header: 'Year',
    	filter: {
            type: 'value',
            operator: 'top10',
            topType: 'items',
            topOrder: 'top',
            dimensionId: 'agg',
            value: 3
     	}
	}]
}

Sencha Fiddle:

3. Multiple Grand Totals

With a Pivot Grid it’s possible to add multiple grand totals.
How to:

listeners: {
	pivotbuildtotals: function (matrix, totals) {
    	var dataAvg = {},
            dataMax = {};
        Ext.Array.each(matrix.model, function (field) {
            var result,
                agg;
            if (field.col &amp;&amp; field.agg) {
                agg = matrix.aggregate.getByKey(field.agg);
                result = matrix.results.get(matrix.grandTotalKey, field.col);
                if (result &amp;&amp; agg) {
                    dataAvg[field.name] = result.calculateByFn(
                        'totalavg',
                        agg.dataIndex,
                        Ext.pivot.Aggregators.avg);
                    dataMax[field.name] = result.calculateByFn(
                        'totalmax',
                        agg.dataIndex,
                        Ext.pivot.Aggregators.max);
                }
            }
    	});
    	totals.push({
            title: 'Grand total (avg)',
            values: dataAvg
    	}, {
            title: 'Grand total (max)',
            values: dataMax
    	});
	}
}

Sencha Fiddle:

4. Range Grouping

How to:

{
	dataIndex: 'year',
	header: 'Year',
	grouperFn: function (record) {
 
    	var dataIndex = this.dataIndex,
 
            recIndex = record.get(dataIndex);
 
    	if (recIndex &gt;= 1980 &amp;&amp; recIndex &lt; 1990) return "80'"; if (recIndex &gt;= 1990 &amp;&amp; recIndex &lt; 2000) return "90'"; if (recIndex &gt;= 2000 &amp;&amp; recIndex &lt; 2010) return "00'";
 
    	return 'Rest';
	}
}

Sencha Fiddle:

5. Drill Down

How to:

Ext.define('Fiddle.view.pivot.Grid', {
    extend: 'Ext.pivot.Grid',
    xtype: 'configurable-pivot-grid',
    controller: 'pivotgrid',
    title: 'Pivot Grid',
    //Add drill down plugin
    plugins: {
        pivotdrilldown: true
    },
    matrix: {
        /* Matrix configuration */
    }
});

Sencha Fiddle:

Build Your Pivot Grid with Ext JS 7.2

The free 30-day trial of Ext JS 7.2 provides full access to the product features. Get started today and see how you can build a powerful pivot grid to make insights from data more apparent.