Properties Methods Events Config Options Direct Link
Observable
  Component
    BoxComponent
      Container
        Panel
          GridPanel
            PivotGrid

Class CQ.Ext.grid.PivotGrid

Package:CQ.Ext.grid
Class:PivotGrid
Extends:CQ.Ext.grid.GridPanel
Clientlib:cq.widgets
xtype:pivotgrid

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 = CQ.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 CQ.Ext.data.Store({
    url: 'data.json',
    autoLoad: true,
    reader: new CQ.Ext.data.JsonReader({
        root: 'rows',
        idProperty: 'id'
    }, SaleRecord)
});

// Create the PivotGrid itself, referencing the store
var pivot = new CQ.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 CQ.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 CQ.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 String.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.

Config Options

Config Options Defined By
  allowDomMove : Boolean
Whether the component can move the Dom node when rendering (defaults to true).
Component
  disabled : Boolean
Render this component disabled (default is false).
Component
  disabledClass : String
CSS class added to the component when it is disabled (defaults to 'x-item-disabled').
Component
  measure : String
The field to extract from each Record when pivoting around the two axes. See the class introduction docs for usage
PivotGrid
  pageX : Number
The page level x coordinate for this component if contained within a positioning container.
BoxComponent
  pageY : Number
The page level y coordinate for this component if contained within a positioning container.
BoxComponent
  x : Number
The local x (left) coordinate for this component if contained within a positioning container.
BoxComponent
  y : Number
The local y (top) coordinate for this component if contained within a positioning container.
BoxComponent

Public Properties

Property Defined By
  buttons : Array
This Panel's Array of buttons as created from the buttons config property. Read only.
Panel
  bwrap : CQ.Ext.Element
The Panel's bwrap Element used to contain other Panel elements (tbar, body, bbar, footer). See bodyCfg. Read-only.
Panel
  collapsed : Boolean
True if this panel is collapsed. Read-only.
Panel
  disabled : Boolean
True if this component is disabled. Read-only.
Component
  header : CQ.Ext.Element
The Panel's header Element. Read-only.

This Element is used to house the title and tools


Note: see the Note for el also.

Panel
  hidden : Boolean
True if this component is hidden. Read-only.
Component
  initialConfig : Object
This Component's initial configuration specification. Read-only.
Component
  items : MixedCollection
The collection of components in this container as a CQ.Ext.util.MixedCollection
Container
  leftAxis : CQ.Ext.grid.PivotAxis
The configured CQ.Ext.grid.PivotAxis used as the left Axis for this Pivot Grid
PivotGrid
  refOwner : CQ.Ext.Container The
Component
  rendered : Boolean
True if this component has been rendered. Read-only.
Component
  topAxis : CQ.Ext.grid.PivotAxis
The configured CQ.Ext.grid.PivotAxis used as the top Axis for this Pivot Grid
PivotGrid

Public Methods

Method Defined By

Public Events

Event Defined By