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.
Touch Solitaire is beautiful and feels like a native app
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.
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.
Jaml: beautiful HTML generation for JavaScript
November 4, 2009 by Ed Spencer · 30 Comments
Generating HTML with JavaScript has always been ugly. Hella ugly. It usually involves writing streams of hard-to-maintain code which just concatenates a bunch of strings together and spits them out in an ugly mess.
Wouldn’t it be awesome if we could do something pretty like this:
div(
h1("Some title"),
p("Some exciting paragraph text"),
br(),
ul(
li("First item"),
li("Second item"),
li("Third item")
)
);
And have it output something beautiful like this:
<div>
<h1>Some title</h1>
<p>Some exciting paragraph text</p>
<br />
<ul>
<li>First item</li>
<li>Second item</li>
<li>Third item</li>
</ul>
</div>
With Jaml, we can do exactly that. Jaml is a simple library inspired by the excellent Haml library for Ruby. It works by first defining a template using an intuitive set of tag functions, and then rendering it to appear as pretty HTML. Here’s an example of how we’d do that with the template above:
Jaml.register('simple', function() {
div(
h1("Some title"),
p("Some exciting paragraph text"),
br(),
ul(
li("First item"),
li("Second item"),
li("Third item")
)
);
});
Jaml.render('simple');
All we need to do is call Jaml.register with a template name and the template source. Jaml then stores this for later use, allowing us to render it later using Jaml.render(). Rendering with Jaml gives us the nicely formatted, indented HTML displayed above.
So we’ve got a nice way of specifying reusable templates and then rendering them prettily, but we can do more. Usually we want to inject some data into our template before rendering it – like this:
Jaml.register('product', function(product) {
div({cls: 'product'},
h1(product.title),
p(product.description),
img({src: product.thumbUrl}),
a({href: product.imageUrl}, 'View larger image'),
form(
label({'for': 'quantity'}, "Quantity"),
input({type: 'text', name: 'quantity', id: 'quantity', value: 1}),
input({type: 'submit', value: 'Add to Cart'})
)
);
});
In this example our template takes an argument, which we’ve called product. We could have called this anything, but in this case the template is for a product in an ecommerce store so product makes sense. Inside our template we have access to the product variable, and can output data from it.
Let’s render it with a Product from our database:
//this is the product we will be rendering
var bsg = {
title : 'Battlestar Galactica DVDs',
thumbUrl : 'thumbnail.png',
imageUrl : 'image.png',
description: 'Best. Show. Evar.'
};
Jaml.render('product', bsg);
The output from rendering this template with the product looks like this:
<div class="product">
<h1>Battlestar Galactica DVDs</h1>
<p>Best. Show. Evar.</p>
<img src="thumbnail.png" />
<a href="image.png">View larger image</a>
<form>
<label for="quantity">Quantity</label>
<input type="text" name="quantity" id="quantity" value="1"></input>
<input type="submit" value="Add to Cart"></input>
</form>
</div>
Cool – we’ve got an object oriented declaration of an HTML template which is cleanly separated from our data. How about we define another template, this time for a category which will contain our products:
Jaml.register('category', function(category) {
div({cls: 'category'},
h1(category.name),
p(category.products.length + " products in this category:"),
div({cls: 'products'},
Jaml.render('product', category.products)
)
);
});
Our category template references our product template, achieving something rather like a partial in Ruby on Rails. This obviously allows us to keep our templates DRY and to easily render a hypothetical Category page like this:
//here's a second product
var snowWhite = {
title : 'Snow White',
description: 'not so great actually',
thumbUrl : 'thumbnail.png',
imageUrl : 'image.png'
};
//and a category
var category = {
name : 'Doovde',
products: [bsg, snowWhite]
}
Jaml.render('category', category);
All we’ve done is render the ‘category’ template with our ‘Doovde’ category, which contains an array of products. These were passed into the ‘product’ template to produce the following output:
<div class="category">
<h1>Doovde</h1>
<p>2 products in this category:</p>
<div class="products"><div class="product">
<h1>Battlestar Galactica DVDs</h1>
<p>Best. Show. Evar.</p>
<img src="thumbnail.png" />
<a href="image.png">View larger image</a>
<form>
<label for="quantity">Quantity</label>
<input type="text" name="quantity" id="quantity" value="1"></input>
<input type="submit" value="Add to Cart"></input>
</form>
</div>
<div class="product">
<h1>Snow White</h1>
<p>not so great actually</p>
<img src="thumbnail.png" />
<a href="image.png">View larger image</a>
<form>
<label for="quantity">Quantity</label>
<input type="text" name="quantity" id="quantity" value="1"></input>
<input type="submit" value="Add to Cart"></input>
</form>
</div>
</div>
</div>
You can see live examples of all of the above at http://edspencer.github.com/jaml.
Jaml currently sports a few hacks and is not particularly efficient. It is presented as a proof of concept, though all the output above is true output from the library. As always, all of the code is up on Github, and contributions are welcome 
Jaml would be suitable for emulating a Rails-style directory structure inside a server side JavaScript framework – each Jaml template could occupy its own file, with the template name coming from the file name. This is roughly how Rails and other MVC frameworks work currently, and it eliminates the need for the Jaml.register lines. Alternatively, the templates could still be stored server side and simply pulled down and evaluated for client side rendering.
Happy rendering!
Making RowEditor use your column renderers
October 29, 2009 by Ed Spencer · 4 Comments
The RowEditor plugin is one of my favourite Ext JS components. It basically allows any row on a grid to be turned into an adhoc form on the fly, saving you the effort of defining additional form components.
Recently I had a grid which had a few fields that don’t have an editor, something like this:
var myGrid = new Ext.grid.GridPanel({
plugins: [new Ext.ux.grid.RowEditor()],
columns: [
{
header : "Username",
dataIndex: 'username',
editor : new Ext.form.TextField()
},
{
header : "Signup date",
dataIndex: 'created_at',
renderer : Ext.util.Format.dateRenderer('m/d/Y')
}
]
});
Simple stuff – we just show a username and a signup date, which is altered by a renderer. When we double-click a row it turns into an editable row, and we get a textfield allowing us to edit the username. Unfortunately, while in edit mode our date renderer is ignored, and the raw value displayed instead.
Thankfully, we can fix this by altering RowEditor’s source code. The method we need to change is startEditing, which sadly suffers from long method syndrome. About halfway into that method there’s a for loop, which we’re going to alter to look like this:
for (var i = 0, len = cm.getColumnCount(); i < len; i++){
val = this.preEditValue(record, cm.getDataIndex(i));
f = fields[i];
//our changes start here
var column = cm.getColumnById(cm.getColumnId(i));
val = column.renderer.call(column, val, {}, record);
//our changes end here
f.setValue(val);
this.values[f.id] = Ext.isEmpty(val) ? '' : val;
}
We didn’t really have to do much, just grab the renderer for the column and pass it the default value and the record which was found earlier in the method.
For the curious, the empty object we pass in as the second argument to the renderer is what would usually be the ‘meta’ object (see the renderer documentation on the Column class). Under the covers, RowEditor actually creates an Ext.form.DisplayField instance for each column that you don’t specify an editor for. This is why we use f.setValue(val); above. DisplayField doesn’t have the same meta stuff as a normal cell would, so if you’re looking to customise CSS via the metadata you’ll have to do something like this instead:
columns: [
{
...
editor: new Ext.form.DisplayField({
cls: 'myCustomCSSClass',
style: 'border: 10px solid red;'
})
}
]
Pretty easy. It’s a shame we have to overwrite the source code as this makes the solution less future proof, but if you look at RowEditor’s source code you’ll see why a 45 line override would be equally unpleasant.