Using the Ext JS PivotGrid

One of the new components we just unveiled for the Ext JS 3.3 beta is PivotGrid. PivotGrid is a powerful new component that reduces and aggregates large datasets into a more understandable form.

A classic example of PivotGrid’s usefulness is in analyzing sales data. Companies often keep a database containing all the sales they have made and want to glean some insight into how well they are performing. PivotGrid gives the ability to rapidly summarize this large and unwieldy dataset – for example showing sales count broken down by city and salesperson.

A simple example

We created an example of this scenario in the 3.3 beta release. Here we have a fictional dataset containing 300 rows of sales data (see the raw data). We asked PivotGrid to break the data down by Salesperson and Product, showing us how they performed over time. Each cell contains the sum of sales made by the given salesperson/product combination in the given city and year.

Let’s see how we create this PivotGrid:

var SaleRecord = Ext.data.Record.create([
    {name: 'person',   type: 'string'},
    {name: 'product',  type: 'string'},
    {name: 'city',     type: 'string'},
    {name: 'state',    type: 'string'},
    {name: 'month',    type: 'int'},
    {name: 'quarter',  type: 'int'},
    {name: 'year',     type: 'int'},
    {name: 'quantity', type: 'int'},
    {name: 'value',    type: 'int'}
]);

var myStore = new Ext.data.Store({
    url: 'salesdata.json',
    autoLoad: true,
    reader: new Ext.data.JsonReader({
        root: 'rows',
        idProperty: 'id'
    }, SaleRecord)
});

var pivotGrid = new Ext.grid.PivotGrid({
    title     : 'Sales Performance',
    store     : myStore,
    aggregator: 'sum',
    measure   : 'value',
    
    leftAxis: [
        {dataIndex: 'person',  width: 80},
        {dataIndex: 'product', width: 90}
    ],
    
    topAxis: [
        {dataIndex: 'year'},
        {dataIndex: 'city'}
    ]
});

The first half of this ought to be very familiar – we just set up a normal Record and Store. This is all we need to load our sample data so that it’s ready for pivoting. This is all exactly the same code as for our other Store-bound components like Grid and DataView so it’s easy to take an existing Grid and turn it into a PivotGrid.

The second half of the code creates the PivotGrid itself. There are 5 main components to a PivotGrid – the store, the measure, the aggregator, the left axis and the top axis. Taking these in turn:

  • Store – the Store we created above
  • Measure – the field in the data that we want to aggregate (in this case the sale value)
  • Aggregator – the function we use to combine data into the cells. See the docs for full details
  • Left Axis – the fields to break data down by on the left axis
  • Top Axis – the fields to break data down by on the top axis

The measure and the items in the axes must all be fields from the Store. The aggregator function can usually be passed in as a string – there are 5 aggregator functions built in: sum, count, min, max and avg.

Renderers

This is all we need to create a simple PivotGrid; now it’s time to look at a few more advanced options. Let’s start with renderers. Once the data for each cell has been calculated, the value is passed to an optional renderer function, which takes each value in turn and returns another value. One of the PivotGrid examples shows average heights in feet and inches but the calculated data is in decimal. Here’s the renderer we use in that example:

new Ext.grid.PivotGrid({
    store     : myStore,
    aggregator: 'avg',
    measure   : 'height',
    
    //turns a decimal number of feet into feet and inches
    renderer  : function(value) {
        var feet   = Math.floor(value),
            inches = Math.round((value - feet) * 12);
            
        return String.format("{0}' {1}"", feet, inches);
    },
    //the rest of the config
});

Customising cell appearance

Another one of the PivotGrid examples uses a custom cell style. As with the renderer, each cell has the opportunity to alter itself with a custom function – here’s the one we use in the countries example:

new Ext.grid.PivotGrid({
    store     : myStore,
    aggregator: 'avg',
    measure   : 'height',
    
    viewConfig: {
        getCellCls: function(value) {
            if (value < 20) {
                return 'expense-low';
            } else if (value < 75) {
                return 'expense-medium';
            } else {
                return 'expense-high';
            }
        }
    },
    //the rest of the config
});

Reconfiguring at runtime

A lot of the power of PivotGrid is that it can be used by users of your application to summarize datasets any way they want. This is made possible by PivotGrid’s ability to reconfigure itself at runtime. We present one final example of a PivotGrid that can be reconfigured at runtime. Here’s how we perform the reconfiguration:

//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);

It’s easy to change the axes, dimension, aggregator and measure at any time and then refresh the data. The calculations are all performed client side so there is no need for another round-trip to the server when reconfiguring. The example linked above gives an example interface for updating a PivotGrid, though anything that can make the API calls above could be used.

I hope you enjoy the new components in this Ext JS 3.3 beta and look forward to comments and suggestions. Although we’re only at beta stage I think the additions are already quite robust so feel free to stress-test them.

Ext.ux.Exporter – export any Grid to Excel or CSV

Sometimes we want to print things, like grids or trees. The Ext JS printing plugin is pretty good for that. But what if we want to export them instead? Enter Ext.ux.Exporter.

Ext.ux.Exporter allows any store-based component (such as grids) to be exported, locally, to Excel or any other format. It does not require any server side programming – the export document is generated on the fly, entirely in JavaScript.

The extension serves as a base for exporting any kind of data, but comes bundled with a .xls export formatter suitable for exporting any Grid straight to Excel. Here’s how to do that:

var grid = new Ext.grid.GridPanel({
  store: someStore,
  tbar : [
    {
      xtype: 'exportbutton',
      store: someStore
    }
  ],
  //your normal grid config goes here
});

Clicking the Download button in the top toolbar iterates over the data in the store and creates an Excel file locally, before Base64 encoding it and redirecting the browser via a data url. If you have Excel or a similar program installed your browser should ask you to save the file or open it with Excel.

I put together a quick example of the plugin in action inside the repository, just clone or download the code and drag the examples/index.html file into your browser to run it.

The Exporter will work with any store or store-based component. It also allows export to any format – for example CSV or PDF. Although the Excel Formatter is probably the most useful, implementing a CSV or other Formatter should be trivial – check out the Excel Formatter example in the ExcelFormatter directory.

Using the ExtJS Row Editor

The RowEditor plugin was recently added to the ExtJS examples page. It works a lot like a normal Grid Editor, except you can edit several fields on a given row at once before saving.

This neatly solves the problem of adding a new row to an editor grid, entering data into the first field and finding it save itself straight away, which is rarely desired. In this fashion we can provide full CRUD for simple models in a single page.

Installation

You’ll need to get a copy of the javascript, css and images from the server. This is a bit of a pain. If you still have the ExtJS SDK around you can find these in the examples folder, if not you can get each file as follows:

Grab the plugin JS file below and put it where you usually put your .js files:
http://www.extjs.com/deploy/dev/examples/ux/RowEditor.js

This needs to go with your other stylesheets, usually in a directory called ‘css’:
http://www.extjs.com/deploy/dev/examples/ux/css/RowEditor.css

Download these two images and put them into your existing ‘images’ folder (the same place the other ExtJS images live):
http://www.extjs.com/deploy/dev/examples/ux/images/row-editor-bg.gif
http://www.extjs.com/deploy/dev/examples/ux/images/row-editor-btns.gif

Include the .js and .css files on your page and you should be ready to go.

Usage

RowEditor is a normal grid plugin, so you’ll need to instantiate it and add to your grid’s ‘plugins’ property. You also need to define what type of Editor is available (if any) on each column:

var editor = new Ext.ux.grid.RowEditor();

var grid = new Ext.grid.GridPanel({
  plugins: [editor],
  columns: [
    {
      header   : 'User Name',
      dataIndex: 'name',
      editor   : new Ext.form.TextField()
    },
    {
      header   : 'Email',
      dataIndex: 'email',
      editor   : new Ext.form.TextField()
    }
  ]
  ... the rest of your grid config here
});

RowEditor defines a few events, the most useful one being ‘afteredit’. Its signature looks like this:

/**
 * @event afteredit
 * Fired after a row is edited and passes validation.  This event is fired
 * after the store's update event is fired with this edit.
 * @param {Ext.ux.grid.RowEditor} roweditor This object
 * @param {Object} changes Object with changes made to the record.
 * @param {Ext.data.Record} r The Record that was edited.
 * @param {Number} rowIndex The rowIndex of the row just edited
 */
'afteredit'

All you need to do is listen to that event on your RowEditor and save your model object appropriately. First though, we’ll define the Ext.data.Record that we’re using in this grid’s store:

var User = Ext.data.Record.create([
  {name: 'user_id', type: 'int'},
  {name: 'name',    type: 'string'},
  {name: 'email',   type: 'string'}
]);

And now the afteredit listener itself

editor.on({
  scope: this,
  afteredit: function(roweditor, changes, record, rowIndex) {
    //your save logic here - might look something like this:
    Ext.Ajax.request({
      url   : record.phantom ? '/users' : '/users/' + record.get('user_id'),
      method: record.phantom ? 'POST'   : 'PUT',
      params: changes,
      success: function() {
        //post-processing here - this might include reloading the grid if there are calculated fields
      }
    });
  }
});

The code above simply takes the changes object (which is just key: value object with all the changed fields) and issues a request to your server backend. ‘record.phantom’ returns true if this record does not yet exist on the server – we use this information above to specify whether we’re POSTing to /users or PUTing to /users/123, in line with normal RESTful practices.

Adding a new record

The example above allows for editing an existing record, but how do we add a new one? Like this:

var grid = new Ext.grid.GridPanel({
  //... the same config from above goes here,
  tbar: [
    {
      text   : &quot;Add User&quot;,
      handler: function() {
        //make a new empty User and stop any current editing
        var newUser = new User({});
        rowEditor.stopEditing();
        
        //add our new record as the first row, select it
        grid.store.insert(0, newUser);
        grid.getView().refresh();
        grid.getSelectionModel().selectRow(0);
        
        //start editing our new User
        rowEditor.startEditing(0);
      }
    }
  ]
});

Pretty simple stuff – we’ve just added a toolbar with a button which, when clicked, creates a new User record, inserts it at the top of the grid and focusses the RowEditor on it.

Configuration Options

Although not documented, the plugin has a few configuration options:

var editor = new Ext.ux.grid.RowEditor({
  saveText  : &quot;My Save Button Text&quot;,
  cancelText: &quot;My Cancel Button Text&quot;,
  clicksToEdit: 1, //this changes from the default double-click activation to single click activation
  errorSummary: false //disables display of validation messages if the row is invalid
});

If you want to customise other elements of the RowEditor you probably can, but you’ll need to take a look at the source (it’s not scary).

Final Thought

RowEditor is a really nice component which can provide an intuitive interface and save you writing a lot of CRUD code. It is best employed on grids with only a few columns – for models with lots of data fields you’re better off with a full FormPanel.

I’d be pretty happy to see this included in the default ExtJS distribution, as I find myself returning to it frequently.

Ext.ux.Printer – printing for any ExtJS Component

After my recent foray into printing grids with ExtJS, I realised I needed to print some trees too. Seeing as some of the work was already done for the Grid example, it made sense to create a common API for printing any Ext.Component. And thus Ext.ux.Printer was born:

var grid = new Ext.grid.GridPanel({ // just a normal grid });
var tree = new Ext.tree.ColumnTree({ // just a normal column tree });

Ext.ux.Printer.print(grid);
Ext.ux.Printer.print(tree);

Each of the above opens a new window, renders some HTML (just a big table really), prints it and closes the window – all client side with no server side code required. Although trees and grids represent data quite differently internally, we can use the same API on Ext.ux.Printer to print them both.

Ext.ux.Printer uses Renderer classes to cope with a specific xtype, and adding Renderers for other components is easy. At the moment Ext.grid.GridPanel and Ext.tree.ColumnTree are supported out of the box, but let’s see how we’d add support for printing the contents of an Ext.Panel:

/**
 * Prints the contents of an Ext.Panel
 */
Ext.ux.Printer.PanelRenderer = Ext.extend(Ext.ux.Printer.BaseRenderer, {

 /**
  * Generates the HTML fragment that will be rendered inside the &lt;html&gt; element of the printing window
  */
 generateBody: function(panel) {
   return String.format(&quot;&lt;div class='x-panel-print'&gt;{0}&lt;/div&gt;&quot;, panel.body.dom.innerHTML);
 }
});

Ext.ux.Printer.registerRenderer(&quot;panel&quot;, Ext.ux.Printer.PanelRenderer);

This is probably the simplest print renderer of all – we’re simply grabbing the HTML from inside a the panel’s body and returning it inside our own div. We subclassed Ext.ux.Printer.BaseRenderer, and in this case all we needed to do was provide an implementation for generateBody. Whatever this function returns is rendered inside the <body> tag of the newly-opened printing window.

Notice that we registered this renderer for all components with the xtype of ‘panel’. Internally, Ext.ux.Printer examines the xtype chain of the component you pass it to print, and uses the first renderer that matches. As many Ext components inherit from Ext.Panel this can function as a catch-all renderer.

Here’s how we’d use our new renderer:

var panel = new Ext.Panel({
  html: {
    tag: 'ul',
    chidren: [
      {tag: 'li', text: 'Item 1'},
      {tag: 'li', text: 'Item 2'},
      {tag: 'li', text: 'Item 3'}
    ]
  }
});

Ext.ux.Printer.print(panel);

Pretty straightforward. You can now print Ext.Panels the same way you’d print a Grid or a Tree. Take a look at the Grid Renderer and the ColumnTree Renderer for examples of rendering more advanced components.

As usual, all of the Ext.ux.Printer source is available on Github, and the README file there contains instructions for installation and usage.

Finally, when the printing window is opened it includes a stylesheet that it expects to find at “/stylesheets/print.css”. There is a default print.css stylesheet included with the extension to get you started, and you can specify where to find this stylesheet like this:

Ext.ux.Printer.BaseRenderer.prototype.stylesheetPath = '/path/to/print/stylesheet.css';

ExtJS grid page size – letting the user decide

Sometimes you’ll be using a Paging Toolbar on a grid and need to give the user the ability to change the number of records per page. One way of doing this is by adding a combobox to the toolbar:

var combo = new Ext.form.ComboBox({
  name : 'perpage',
  width: 40,
  store: new Ext.data.ArrayStore({
    fields: ['id'],
    data  : [
      ['15'], 
      ['25'],
      ['50']
    ]
  }),
  mode : 'local',
  value: '15',

  listWidth     : 40,
  triggerAction : 'all',
  displayField  : 'id',
  valueField    : 'id',
  editable      : false,
  forceSelection: true
});

We’ve set up a simple combo box which allows the user to choose between 15, 25 and 50 records per page. Now let’s set up a Paging Toolbar, and a listener to take action when the user changes the selection in the combo box:

var bbar = new Ext.PagingToolbar({
  store:       store, //the store you use in your grid
  displayInfo: true,
  items   :    [
    '-',
    'Per Page: ',
    combo
  ]
});

combo.on('select', function(combo, record) {
  bbar.pageSize = parseInt(record.get('id'), 10);
  bbar.doLoad(bbar.cursor);
}, this);

Finally we’ll roll it all together into a Grid:

var grid = new Ext.grid.GridPanel({
  //your grid setup here...

  bbar: bbar
});

If the user needs to be able to enter her own page size, replace the ComboBox with an Ext.form.NumberField, and attach the event listener to the field’s ‘keypress’ event.

Printing grids with Ext JS

Grids are one of the most widely used components in Ext JS, and often represent data that the user would like to print. As the grid is usually part of a wider application, simply printing the page isn’t often a good solution.

You could attach a stylesheet with media=”print”, which hides all of the other items on the page, though this is rather application-specific, and a pain to update. It would be far better to have a reusable way of printing the data from any grid.

The way I went about this was to open up a new window, build a table containing the grid data into the new window, then print it and close. It’s actually pretty simple, and with a bit of CSS we can even get the printable view looking like it does in the grid.

Here’s how you use it (this is a slightly modified version of the Array Grid Example):

var grid = new Ext.grid.GridPanel({
  store  : store,
  columns: [
      {header: &quot;Company&quot;,      width: 160, dataIndex: 'company'},
      {header: &quot;Price&quot;,        width: 75,  dataIndex: 'price', renderer: 'usMoney'},
      {header: &quot;Change&quot;,       width: 75,  dataIndex: 'change'},
      {header: &quot;% Change&quot;,     width: 75,  dataIndex: 'pctChange'}
      {header: &quot;Last Updated&quot;, width: 85,  dataIndex: 'lastChange', renderer: Ext.util.Format.dateRenderer('m/d/Y')}
  ],
  title:'Array Grid',
  tbar : [
    {
      text   : 'Print',
      iconCls: 'print',
      handler: function() {
        Ext.ux.GridPrinter.print(grid);
      }
    }
  ]
});

So we’ve just set up a simple grid with a print button in the top toolbar. The button just calls Ext.ux.GridPrinter.print, which does all the rest. The full source code that this example was based upon can be found at http://extjs.com/deploy/dev/examples/grid/array-grid.js.

The source for the extension itself is pretty simple (download it here):

If you look at the source above you’ll see it includes a ‘print.css’ stylesheet, which can be used to style the printable markup. The GridPrinter expects this stylesheet to be available at /stylesheets/print.css, but this is easy to change:

  //add this before you call Ext.ux.GridPrinter.print
  Ext.ux.GridPrinter.stylesheetPath = '/some/other/path/gridPrint.css';

Finally, here is some CSS I’ve used to achieve a grid-like display on the printable page:

html,body,div,dl,dt,dd,ul,ol,li,h1,h2,h3,h4,h5,h6,pre,form,fieldset,input,p,blockquote,th,td{margin:0;padding:0;}
img,body,html{border:0;}
address,caption,cite,code,dfn,em,strong,th,var{font-style:normal;font-weight:normal;}
ol,ul {list-style:none;}caption,th {text-align:left;}h1,h2,h3,h4,h5,h6{font-size:100%;}q:before,q:after{content:'';}

table {
  width: 100%;
  text-align: left;
  font-size: 11px;
  font-family: arial;
  border-collapse: collapse;
}

table th {
  padding: 4px 3px 4px 5px;
  border: 1px solid #d0d0d0;
  border-left-color: #eee;
  background-color: #ededed;
}

table td {
  padding: 4px 3px 4px 5px;
  border-style: none solid solid;
  border-width: 1px;
  border-color: #ededed;
}

This technique could easily be adapted to print any component that uses a store – DataViews, ComboBoxes, Charts – whatever. It just requires changing the generated markup and stylesheet.

Force Ext.data.Store to use GET

Say you have a simple Ext store:

var myStore = new Ext.data.Store({
  url:    '/widgets.json',
  reader: someReader
});

Which you put in a grid, along with a paging toolbar:

var myGrid = new Ext.grid.GridPanel({
  store:   myStore,
  columns: [.....],
  bbar:    new Ext.PagingToolbar({
    store: myStore
  })
  ... etc ...
});

Your grid loads up and the store performs a GET request to /widgets.json, which returns your widgets along with a total (see an example).

Awesome, but now we click one of the paging buttons on the PagingToolbar and we have a problem – our request has turned into POST /widgets.json, with “start=20” and “limit=20” as POST params.

Now we don’t really want that – we’re not POSTing any data to the server after all, we’re just trying to GET some. If you’re using a nice RESTful API on your server side this may cause you a real problem, as POST /widgets will likely be taken as an attempt to create a new Widget.

Luckily, as with most things the solution is simple if you know how. An Ext.data.Store delegates loading its data off to an Ext.data.DataProxy subclass. By default your store will create an Ext.data.HttpProxy using the url: ‘/widgets.json’ you passed in your store config. To make sure your stores are always requesting data using GET, just provide a proxy like this:

var myStore = new Ext.data.Store({
  proxy: new Ext.data.HttpProxy({
    url:    '/widgets.json',
    method: 'GET'
  }),
  reader: someReader
});
<span>%d</span> bloggers like this: