Using the Ext JS PivotGrid

July 29, 2010 by Ed Spencer · 2 Comments 

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.

Offline Apps with HTML5: A case study in Solitaire

June 21, 2010 by Ed Spencer · 3 Comments 

One of my contributions to the newly-launched Sencha Touch mobile framework is the Touch Solitaire game. This is not the first time I have ventured into the dizzying excitement of Solitaire game development; you may remember the wonderful Ext JS Solitaire from 18 months ago. I’m sure you’ll agree that the new version is a small improvement.

Solitaire is a nice example of a fun application that can be written with Sencha Touch. It makes use of the provided Draggables and Droppables, CSS-based animations, the layout manager and the brand new data package. The great thing about a game like this though is that it can be run entirely offline. Obviously this is simple with a native application, but what about a web app? Our goal is not just having the game able to run offline, but to save your game state locally too.

The answer comes in two parts:

Web Storage and the Sencha data package

HTML5 provides a brand new API called Web Storage for storing data locally. You can read all about it on my Web Storage post on Sencha’s blog but the summary is that you can store string data locally in the browser and retrieve it later, even if the browser or the user’s computer had been restarted in the meantime.

The crucial part of the sentence above is that we can only store string data. In the case of a game of Solitaire we need to store data on the elapsed time and number of moves as well as the location and status of each card. This doesn’t sound like the kind of data we want to manually encode into a string, so thankfully the data package comes to the rescue.

The Sencha Touch data package is a complete rewrite of the package that has been so successful in powering Ext JS 3.x. It shares many of the same philosophies and adds the learning we have gained from developing Ext JS 3.x over the past year. One of the new capabilities it offers us is a Local Storage proxy, which automatically marshalls your model data into local storage and transparently restores it when you need it.

Using the new proxy is simple – all we need to do is set up a new Store, specifying the Proxy and the Model that will be saved to it. Models are the spiritual successor to Ext JS 3.x’s Records. Now whenever we add, remove or update model instances in the store they are automatically saved to localStorage for us. Loading the store again is equally easy:

//set the store up
var gameStore = new Ext.data.Store({
    proxy: new Ext.data.LocalStorageProxy({
        id: 'solitaire-games'
    }),
    model: 'Game'
});

//saves all outstanding modifications, deletions or creations to localStorage
gameStore.sync();

//load our saved games
gameStore.read({
    scope: this,
    callback: function(records) {
        //code to load the first record
    }
});

And just like that we can save and restore games with Web Storage. We can visit our app’s webpage and start a game then come back later and find it automatically restored. But we still can’t play offline, for that we need the application cache.

The HTML5 Application Cache Manifest

The application cache is one of the best features of HTML5. It provides a simple (though sometimes frustrating) way of telling the browser about all of the files your application relies on so that it can download them all ready for offline use. All you have to do is create what’s known as a manifest file which lists all of the files the application needs – the Solitaire manifest looks like this:

CACHE MANIFEST
#rev49

resources/icon.png
resources/loading.png

resources/themes/wood/board.jpg
resources/themes/wood/cards.png

resources/css/ext-touch.css
resources/solitaire-notheme.css
resources/themes/wood/wood.css
resources/themes/metal/metal.css

ext-touch-debug.js
solitaire-all-debug.js

We tell the browser about the manifest file by pointing to it in the tag’s manifest atttibute. When the browser finds this file it downloads each of the listed assets so that they are ready for offline consumption. Note that it does not automatically include them on the page, you still need to do that yourself via the usual link and script tags. Here’s a snippet of the Solitaire index.html file:

<!doctype html>
<html manifest="solitaire.manifest">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Solitaire</title>

        <link rel="stylesheet" href="resources/css/ext-touch.css" type="text/css">
        <link rel="stylesheet" href="resources/solitaire-notheme.css" type="text/css">
        <link rel="stylesheet" href="resources/themes/wood/wood.css" type="text/css">

        <script type="text/javascript" src="ext-touch-debug.js"></script>
        <script type="text/javascript" src="solitaire-all-debug.js"></script>

Note the manifest file definition in the html element at the top, and the fact that we still include our page resources the normal way. It sounds easy, but without a little setup first it can be a very frustrating experience. Usually your browser will try to cache as many files as possible, including the manifest file itself – we don’t want this. As soon as your browser has a long-term cache of the manifest file it is extremely difficult to update your application – all of the files are already offline and won’t be updated, and the browser won’t even ask the server for an updated manifest file.

Preventing this behaviour turns out to be fairly easy, and the solution in its simplest form comes in the shape of a .htaccess file with contents like the following:

<Files solitaire.manifest>
    ExpiresActive On
    ExpiresDefault "access"
</Files>

This directs Apache to tell the browser not to cache the manifest file at all, instead requesting the file from the server on every page load. Note that if the device is currently offline it will use the last manifest file it received.

This is half the battle won, but let’s say you change one of your application files and reload – you’ll find nothing happened. This is because when your browser asked the server for the manifest file it actually asked if the file had changed or not. As the manifest itself wasn’t updated, the server responds with a 304 (Not Modified) and your browser keeps the old file.

To make the browser pick up on the change to the application file you need to update the manifest file itself. This is where the mysterious “#rev49″ comes in on the manifest example file above. This is a suggestion from the excellent diveintohtml5 article on the subject – whenever you change any application files just bump up the revision number in the manifest file and your browser will know to download the updated files.

One final detail is that your Apache server probably isn’t set up to server manifest files with the correct mime type, so be sure to add the following line to your Apache config and restart the server:

AddType text/cache-manifest .manifest

Wrapping it up

Offline access is a big deal for mobile apps and Sencha Touch makes them much easier to write. The benefit is not so much that the apps can run without an internet connection (many modern touch devices have a near-permanent connection to the internet already), but that web apps can now be treated as first-class citizens alongside native apps.

The fact that many devices allow your users to save your app to their home screen and load it as though it were native is an important step – you keep all of the advantages of web app deployment while gaining some of the benefits of native apps. As more and more native hardware APIs become available to web apps their importance will only grow.

If you want to check out Solitaire’s offline support for yourself visit the application’s site and save it to your iPad’s home page. Try turning on airplane mode and loading the app and see how it behaves as though it were native. If you don’t have an iPad, you can load the app in up-to-date versions of Chrome or Safari and get a similar experience.

2010: The year Ext JS takes over

January 13, 2010 by Ed Spencer · 18 Comments 

On January 1st 2010 I officially joined Ext JS to take over the role of lead developer. After living and breathing Ext for the last 3 years I am delighted to have joined the company itself. Ext JS has lead the way in developing rich client side applications since the very first release; this is a tradition we will continue and build upon.

2010 is going to be an extremely exciting year for Ext JS. A new focus is being placed on helping developers create their applications much more quickly, with the help of advanced creation tools and a standardised application architecture right out of the box.

We will continue the performance improvements started in 3.1 to make sure that Ext applications really fly. Ext JS 3.2 will be the fastest, most stable version ever released.

2010 is also the year that Ext JS becomes much easier to learn. With a completely reinvented learning section, Ext will no longer take months to learn and understand – even our API documentation will get a facelift.

The upcoming Marketplace will be the perfect venue to find and share new, high quality components created by our awesome developer community. Think of the Marketplace as the App Store for Ext JS – full of great offerings that are easy to drop in to any application.

Calling all able-minded Ext JS developers

Ext JS is already the best JavaScript library in the world for creating rich, desktop-quality applications on the web. If you want to help us make it even better, I want to hear from you.

As well as creating new components and improving our application support, we need people to help us maintain the quality and stability of what we already have. If you’re intimate with Ext and think you have what it takes to get involved, drop me a PM and introduce yourself.

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

November 24, 2009 by Ed Spencer · 36 Comments 

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.

Ext.ux.layout.FillContainer

September 28, 2009 by Ed Spencer · 3 Comments 

One of the pages on the Ext JS app I’m currently working on has a form with a grid underneath. The page exists as a tab inside an Ext.TabPanel, and uses the border layout, with the form as the ‘north’ component and the grid as ‘center’.

The trouble with this is that the grid shrinks down to an unusable size when the browser window is too small, ending up like this:

Border layout

We could alternatively use a basic container layout, but this limits us to a fixed height for the grid, meaning we waste space at the bottom:

Container layout

Enter the imaginatively named FillContainer:

new Ext.Panel({
  autoScroll: true,
  layout: 'fillcontainer',
  items : [
    {
      html  : 'Pretend this is a form',
      height: 400
    },
    {
      html         : 'And this is the grid',
      minHeight    : 250,
      fillContainer: true
    }
  ]
});

If our containing panel shrinks to less than 650px in height, the grid will be automatically sized to 250px and a vertical scrollbar will appear on the panel, like this:

Fill Container with scroll bar

If the panel’s height increases to, say, 900px, the grid gets resized to 500px high. This way we use the space when it’s available, while maintaining a usable interface when height is limited:

Fill Container with stretched item

Here’s the code that makes it work:

Ext.ns('Ext.ux.layout');

/**
 * @class Ext.ux.layout.FillContainerLayout
 * @extends Ext.layout.ContainerLayout
 * @author Ed Spencer (http://edspencer.net)
 * Extended version of container layout which expands a given child item to the
 * full height of the container, honouring the item's minHeight property
 */
Ext.ux.layout.FillContainerLayout = Ext.extend(Ext.layout.ContainerLayout, {
  monitorResize: true,

  /**
   * After rendering each item, resize the one with fillContainer == true
   */
  onLayout: function(ct, target) {
    Ext.ux.layout.FillContainerLayout.superclass.onLayout.apply(this, arguments);

    var ctHeight    = ct.getHeight(),
        itemsHeight = 0,
        expandItem;

    ct.items.each(function(item) {
      if (item.fillContainer === true) {
        expandItem = item;
      } else {
        itemsHeight += item.getHeight();
      }
    });

    //set the expand item's height to fill the container
    if (expandItem != undefined && ctHeight > itemsHeight) {
      var newHeight = ctHeight - itemsHeight;

      expandItem.setHeight(Math.max(newHeight, expandItem.minHeight));
    }
  }
});

Ext.Container.LAYOUTS['fillcontainer'] = Ext.ux.layout.FillContainerLayout;

As we’re just extending the default container layout, your items will be rendered in the order you specify them. The expanding item doesn’t have to be the last one – we could equally have set fillContainer and minHeight on the form to expand that instead of the grid.

Next Page »