3 * Copyright(c) 2006-2010 Sencha Inc.
5 * http://www.sencha.com/license
8 * @class Ext.grid.PivotGrid
9 * @extends Ext.grid.GridPanel
10 * <p>The PivotGrid component enables rapid summarization of large data sets. It provides a way to reduce a large set of
11 * data down into a format where trends and insights become more apparent. A classic example is in sales data; a company
12 * will often have a record of all sales it makes for a given period - this will often encompass thousands of rows of
13 * data. The PivotGrid allows you to see how well each salesperson performed, which cities generate the most revenue,
14 * how products perform between cities and so on.</p>
15 * <p>A PivotGrid is composed of two axes (left and top), one {@link #measure} and one {@link #aggregator aggregation}
16 * function. Each axis can contain one or more {@link #dimension}, which are ordered into a hierarchy. Dimensions on the
17 * left axis can also specify a width. Each dimension in each axis can specify its sort ordering, defaulting to "ASC",
18 * and must specify one of the fields in the {@link Ext.data.Record Record} used by the PivotGrid's
19 * {@link Ext.data.Store Store}.</p>
21 // This is the record representing a single sale
22 var SaleRecord = Ext.data.Record.create([
23 {name: 'person', type: 'string'},
24 {name: 'product', type: 'string'},
25 {name: 'city', type: 'string'},
26 {name: 'state', type: 'string'},
27 {name: 'year', type: 'int'},
28 {name: 'value', type: 'int'}
31 // A simple store that loads SaleRecord data from a url
32 var myStore = new Ext.data.Store({
35 reader: new Ext.data.JsonReader({
41 // Create the PivotGrid itself, referencing the store
42 var pivot = new Ext.grid.PivotGrid({
66 * <p>The specified {@link #measure} is the field from SaleRecord that is extracted from each combination
67 * of product and person (on the left axis) and year on the top axis. There may be several SaleRecords in the
68 * data set that share this combination, so an array of measure fields is produced. This array is then
69 * aggregated using the {@link #aggregator} function.</p>
70 * <p>The default aggregator function is sum, which simply adds up all of the extracted measure values. Other
71 * built-in aggregator functions are count, avg, min and max. In addition, you can specify your own function.
72 * In this example we show the code used to sum the measures, but you can return any value you like. See
73 * {@link #aggregator} for more details.</p>
75 new Ext.grid.PivotGrid({
76 aggregator: function(records, measure) {
77 var length = records.length,
81 for (i = 0; i < length; i++) {
82 total += records[i].get(measure);
88 renderer: function(value) {
89 return Math.round(value);
92 //your normal config here
95 * <p><u>Renderers</u></p>
96 * <p>PivotGrid optionally accepts a {@link #renderer} function which can modify the data in each cell before it
97 * is rendered. The renderer is passed the value that would usually be placed in the cell and is expected to return
98 * the new value. For example let's imagine we had height data expressed as a decimal - here's how we might use a
99 * renderer to display the data in feet and inches notation:</p>
101 new Ext.grid.PivotGrid({
102 //in each case the value is a decimal number of feet
103 renderer : function(value) {
104 var feet = Math.floor(value),
105 inches = Math.round((value - feet) * 12);
107 return String.format("{0}' {1}\"", feet, inches);
112 * <p><u>Reconfiguring</u></p>
113 * <p>All aspects PivotGrid's configuration can be updated at runtime. It is easy to change the {@link #setMeasure measure},
114 * {@link #setAggregator aggregation function}, {@link #setLeftAxis left} and {@link #setTopAxis top} axes and refresh the grid.</p>
115 * <p>In this case we reconfigure the PivotGrid to have city and year as the top axis dimensions, rendering the average sale
116 * value into the cells:</p>
118 //the left axis can also be changed
119 pivot.topAxis.setDimensions([
120 {dataIndex: 'city', direction: 'DESC'},
121 {dataIndex: 'year', direction: 'ASC'}
124 pivot.setMeasure('value');
125 pivot.setAggregator('avg');
127 pivot.view.refresh(true);
129 * <p>See the {@link Ext.grid.PivotAxis PivotAxis} documentation for further detail on reconfiguring axes.</p>
131 Ext.grid.PivotGrid = Ext.extend(Ext.grid.GridPanel, {
134 * @cfg {String|Function} aggregator The aggregation function to use to combine the measures extracted
135 * for each dimension combination. Can be any of the built-in aggregators (sum, count, avg, min, max).
136 * Can also be a function which accepts two arguments (an array of Records to aggregate, and the measure
137 * to aggregate them on) and should return a String.
142 * @cfg {Function} renderer Optional renderer to pass values through before they are rendered to the dom. This
143 * gives an opportunity to modify cell contents after the value has been computed.
148 * @cfg {String} measure The field to extract from each Record when pivoting around the two axes. See the class
149 * introduction docs for usage
153 * @cfg {Array|Ext.grid.PivotAxis} leftAxis Either and array of {@link #dimension} to use on the left axis, or
154 * a {@link Ext.grid.PivotAxis} instance. If an array is passed, it is turned into a PivotAxis internally.
158 * @cfg {Array|Ext.grid.PivotAxis} topAxis Either and array of {@link #dimension} to use on the top axis, or
159 * a {@link Ext.grid.PivotAxis} instance. If an array is passed, it is turned into a PivotAxis internally.
163 initComponent: function() {
164 Ext.grid.PivotGrid.superclass.initComponent.apply(this, arguments);
168 //no resizing of columns is allowed yet in PivotGrid
169 this.enableColumnResize = false;
171 this.viewConfig = Ext.apply(this.viewConfig || {}, {
175 //TODO: dummy col model that is never used - GridView is too tightly integrated with ColumnModel
176 //in 3.x to remove this altogether.
177 this.colModel = new Ext.grid.ColumnModel({});
181 * Returns the function currently used to aggregate the records in each Pivot cell
182 * @return {Function} The current aggregator function
184 getAggregator: function() {
185 if (typeof this.aggregator == 'string') {
186 return Ext.grid.PivotAggregatorMgr.types[this.aggregator];
188 return this.aggregator;
193 * Sets the function to use when aggregating data for each cell.
194 * @param {String|Function} aggregator The new aggregator function or named function string
196 setAggregator: function(aggregator) {
197 this.aggregator = aggregator;
201 * Sets the field name to use as the Measure in this Pivot Grid
202 * @param {String} measure The field to make the measure
204 setMeasure: function(measure) {
205 this.measure = measure;
209 * Sets the left axis of this pivot grid. Optionally refreshes the grid afterwards.
210 * @param {Ext.grid.PivotAxis} axis The pivot axis
211 * @param {Boolean} refresh True to immediately refresh the grid and its axes (defaults to false)
213 setLeftAxis: function(axis, refresh) {
215 * The configured {@link Ext.grid.PivotAxis} used as the left Axis for this Pivot Grid
217 * @type Ext.grid.PivotAxis
219 this.leftAxis = axis;
227 * Sets the top axis of this pivot grid. Optionally refreshes the grid afterwards.
228 * @param {Ext.grid.PivotAxis} axis The pivot axis
229 * @param {Boolean} refresh True to immediately refresh the grid and its axes (defaults to false)
231 setTopAxis: function(axis, refresh) {
233 * The configured {@link Ext.grid.PivotAxis} used as the top Axis for this Pivot Grid
235 * @type Ext.grid.PivotAxis
246 * Creates the top and left axes. Should usually only need to be called once from initComponent
248 initAxes: function() {
249 var PivotAxis = Ext.grid.PivotAxis;
251 if (!(this.leftAxis instanceof PivotAxis)) {
252 this.setLeftAxis(new PivotAxis({
253 orientation: 'vertical',
254 dimensions : this.leftAxis || [],
259 if (!(this.topAxis instanceof PivotAxis)) {
260 this.setTopAxis(new PivotAxis({
261 orientation: 'horizontal',
262 dimensions : this.topAxis || [],
270 * @return {Array} 2-dimensional array of cell data
272 extractData: function() {
273 var records = this.store.data.items,
274 recCount = records.length,
282 var leftTuples = this.leftAxis.getTuples(),
283 leftCount = leftTuples.length,
284 topTuples = this.topAxis.getTuples(),
285 topCount = topTuples.length,
286 aggregator = this.getAggregator();
288 for (i = 0; i < recCount; i++) {
291 for (j = 0; j < leftCount; j++) {
292 cells[j] = cells[j] || [];
294 if (leftTuples[j].matcher(record) === true) {
295 for (k = 0; k < topCount; k++) {
296 cells[j][k] = cells[j][k] || [];
298 if (topTuples[k].matcher(record)) {
299 cells[j][k].push(record);
306 var rowCount = cells.length,
309 for (i = 0; i < rowCount; i++) {
311 colCount = row.length;
313 for (j = 0; j < colCount; j++) {
314 cells[i][j] = aggregator(cells[i][j], this.measure);
322 * Returns the grid's GridView object.
323 * @return {Ext.grid.PivotGridView} The grid view
325 getView: function() {
327 this.view = new Ext.grid.PivotGridView(this.viewConfig);
334 Ext.reg('pivotgrid', Ext.grid.PivotGrid);
337 Ext.grid.PivotAggregatorMgr = new Ext.AbstractManager();
339 Ext.grid.PivotAggregatorMgr.registerType('sum', function(records, measure) {
340 var length = records.length,
344 for (i = 0; i < length; i++) {
345 total += records[i].get(measure);
351 Ext.grid.PivotAggregatorMgr.registerType('avg', function(records, measure) {
352 var length = records.length,
356 for (i = 0; i < length; i++) {
357 total += records[i].get(measure);
360 return (total / length) || 'n/a';
363 Ext.grid.PivotAggregatorMgr.registerType('min', function(records, measure) {
365 length = records.length,
368 for (i = 0; i < length; i++) {
369 data.push(records[i].get(measure));
372 return Math.min.apply(this, data) || 'n/a';
375 Ext.grid.PivotAggregatorMgr.registerType('max', function(records, measure) {
377 length = records.length,
380 for (i = 0; i < length; i++) {
381 data.push(records[i].get(measure));
384 return Math.max.apply(this, data) || 'n/a';
387 Ext.grid.PivotAggregatorMgr.registerType('count', function(records, measure) {
388 return records.length;