The PivotGrid component enables rapid summarization of large data sets. It provides a way to reduce a large set of data down into a format where trends and insights become more apparent. A classic example is in sales data; a company will often have a record of all sales it makes for a given period - this will often encompass thousands of rows of data. The PivotGrid allows you to see how well each salesperson performed, which cities generate the most revenue, how products perform between cities and so on.
A PivotGrid is composed of two axes (left and top), one measure and one aggregation function. Each axis can contain one or more dimension, which are ordered into a hierarchy. Dimensions on the left axis can also specify a width. Each dimension in each axis can specify its sort ordering, defaulting to "ASC", and must specify one of the fields in the Record used by the PivotGrid's Store.
// This is the record representing a single sale
var SaleRecord = Ext.data.Record.create([
{name: 'person', type: 'string'},
{name: 'product', type: 'string'},
{name: 'city', type: 'string'},
{name: 'state', type: 'string'},
{name: 'year', type: 'int'},
{name: 'value', type: 'int'}
]);
// A simple store that loads SaleRecord data from a url
var myStore = new Ext.data.Store({
url: 'data.json',
autoLoad: true,
reader: new Ext.data.JsonReader({
root: 'rows',
idProperty: 'id'
}, SaleRecord)
});
// Create the PivotGrid itself, referencing the store
var pivot = new Ext.grid.PivotGrid({
store : myStore,
aggregator: 'sum',
measure : 'value',
leftAxis: [
{
width: 60,
dataIndex: 'product'
},
{
width: 120,
dataIndex: 'person',
direction: 'DESC'
}
],
topAxis: [
{
dataIndex: 'year'
}
]
});The specified measure is the field from SaleRecord that is extracted from each combination of product and person (on the left axis) and year on the top axis. There may be several SaleRecords in the data set that share this combination, so an array of measure fields is produced. This array is then aggregated using the aggregator function.
The default aggregator function is sum, which simply adds up all of the extracted measure values. Other built-in aggregator functions are count, avg, min and max. In addition, you can specify your own function. In this example we show the code used to sum the measures, but you can return any value you like. See aggregator for more details.
new Ext.grid.PivotGrid({
aggregator: function(records, measure) {
var length = records.length,
total = 0,
i;
for (i = 0; i < length; i++) {
total += records[i].get(measure);
}
return total;
},
renderer: function(value) {
return Math.round(value);
},
//your normal config here
});Renderers
PivotGrid optionally accepts a renderer function which can modify the data in each cell before it is rendered. The renderer is passed the value that would usually be placed in the cell and is expected to return the new value. For example let's imagine we had height data expressed as a decimal - here's how we might use a renderer to display the data in feet and inches notation:
new Ext.grid.PivotGrid({
//in each case the value is a decimal number of feet
renderer : function(value) {
var feet = Math.floor(value),
inches = Math.round((value - feet) * 12);
return Ext.util.Format.format("{0}' {1}\"", feet, inches);
},
//normal config here
});Reconfiguring
All aspects PivotGrid's configuration can be updated at runtime. It is easy to change the measure, aggregation function, left and top axes and refresh the grid.
In this case we reconfigure the PivotGrid to have city and year as the top axis dimensions, rendering the average sale value into the cells:
//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);See the PivotAxis documentation for further detail on reconfiguring axes.
true to animate the transition when the panel is collapsed, false to skip the
animation (defaults to true if the Ext.Fx class is available, otherwise false).
May also be specified as the animation duration in milliseconds.The id of a column in this grid that should expand to fill unused space. This value specified here can not be 0.
Note: If the Grid's view is configured with forceFit=true the autoExpandColumn is ignored. See Ext.grid.Column.width for additional details.
See autoExpandMax and autoExpandMin also.
'x-panel').'x-panel').An array of events that, when fired, should be bubbled to any parent container. See Ext.util.Observable.enableBubble. Defaults to [].
True to display the 'close' tool button and allow the user to close the window, false to
hide the button and disallow closing the window (defaults to false).
By default, when close is requested by clicking the close button in the header, the close method will be called. This will destroy the Panel and its content meaning that it may not be reused.
To make closing a Panel hide the Panel so that it may be reused, set closeAction to 'hide'.
The action to take when the close header tool is clicked:
Note: This behavior has changed! setting *does* affect the close method which will invoke the approriate closeAction.
The direction to collapse the Panel when the toggle button is clicked.
Defaults to the headerPosition
Important: This config is ignored for collapsible Panels which are direct child items of a border layout.
Specify as 'top', 'bottom', 'left' or 'right'.
true to make sure the collapse/expand toggle button always renders first (to the left of)
any other tools in the panel's title bar, false to render it last (defaults to true).Important: this config is only effective for collapsible Panels which are direct child items of a border layout.
When not a direct child item of a border layout, then the Panel's header remains visible, and the body is collapsed to zero dimensions. If the Panel has no header, then a new header (orientated correctly depending on the collapseDirection) will be inserted to show a the title and a re-expand tool.
When a child item of a border layout, this config has two options:
alt : Default.header : true to render the panel collapsed, false to render it expanded (defaults to
false).true to render the panel collapsed, false to render it expanded (defaults to
false).'x-panel-collapsed').'x-panel-collapsed').True to make the panel collapsible and have an expand/collapse toggle Tool added into the header tool button area. False to keep the panel sized either statically, or by an owning layout manager, with no toggle Tool (defaults to false).
See collapseMode and collapseDirectionddText : '{0} selected row{1}'Defaults to true to enable deferred row rendering.
This allows the GridPanel to be initially rendered empty, with the expensive update of the row structure deferred so that layouts with GridPanels appear more quickly.
true to disable selections in the grid. Defaults to false. This configuration will lock the selection model that the GridPanel uses.
var panel = new Ext.Panel({
fullscreen: true,
dockedItems: [{
xtype: 'toolbar',
dock: 'top',
items: [{
text: 'Docked to the bottom'
}]
}]
});Enables dragging of the selected rows of the GridPanel. Defaults to false.
Setting this to true causes this GridPanel's GridView to create an instance of Ext.grid.GridDragZone. Note: this is available only after the Grid has been rendered as the GridView's dragZone property.
A cooperating DropZone must be created who's implementations of onNodeEnter, onNodeOver, onNodeOut and onNodeDrop are able to process the data which is provided.
Important: This config is only effective for collapsible Panels which are direct child items of a border layout.
true to allow clicking a collapsed Panel's placeHolder to display the Panel floated above the layout, false to force the user to fully expand a collapsed region by clicking the expand button to see it again (defaults to true).'top', 'bottom', 'left' or 'right'. Defaults to 'top'.'top', 'bottom', 'left' or 'right'. Defaults to 'top'.true to hide the expand/collapse toggle button when collapsible == true,
false to display it (defaults to false).true to hide the expand/collapse toggle button when collapsible == true,
false to display it (defaults to false).false.false.false.false.Important: This config is only effective for collapsible Panels which are direct child items of a border layout
when using the default 'alt' collapseMode.
b>Optional. A Component (or config object for a Component) to show in place of this Panel when this Panel is collapsed by a border layout. Defaults to a generated Header containing a Tool to re-expand the Panel.
stateEvents: ['columnmove', 'columnresize', 'sortchange', 'groupchange']These can be any types of events supported by this component, including browser or custom events (e.g., ['click', 'customerchange']).
See Ext.Component.stateful for an explanation of saving and restoring Component state.
This causes the CSS class x-grid3-row-alt to be added to alternate rows of the grid. A default CSS rule is provided which sets a background colour, but you can override this with a rule which either overrides the background-color style using the '!important' modifier, or which uses a CSS selector of higher specificity.
true to allow expanding and collapsing the panel (when collapsible = true)
by clicking anywhere in the header bar, false) to allow it only by clicking to tool button
(defaults to false)).If this Panel is configured draggable, this property will contain an instance of Ext.dd.DragSource which handles dragging the Panel.
The developer must provide implementations of the abstract methods of Ext.dd.DragSource in order to supply behaviour for each stage of the drag/drop process. See draggable.Closes the Panel. By default, this method, removes it from the DOM, destroys the Panel object and all its descendant Components. The beforeclose event is fired before the close happens and will cancel the close action if it returns false.
Note: This method is not affected by the closeAction setting which only affects the action triggered when clicking the 'close' tool in the header. To hide the Panel without destroying it, call hide.
selModel
configuration option. If no selection model was configured, this will create
and return a RowSelectionModel.Reconfigures the grid to use a different Store and Column Model and fires the 'reconfigure' event. The View will be bound to the new objects and refreshed.
Be aware that upon reconfiguring a GridPanel, certain existing settings may become invalidated. For example the configured autoExpandColumn may no longer exist in the new ColumnModel. Also, an existing PagingToolbar will still be bound to the old Store, and will need rebinding. Any plugins might also need reconfiguring with the new data.