YUI2 DataTable summary statistics

Introducation

This is an example of use of a datatable extension that adds a table footer with summary statistics. The extension adds a tfoot with the stats you need, and keeps it updated as the table changes.

Implementation

The javascript can be downloaded/forked on github: https://github.com/mattparker/Yui-DataTable-extension-summary-statistics. There are three files that you need (this is all described in the readme on github). One gives a YAHOO.util.Stats class; one adds a parseField method to DataSource that's used by the column stats; one is the actual extension to the DataTable.

An example

Here's a fairly simple datatable, with sortable and movable columns, and a paginator. The 'add row' etc buttons do what they say, so you can see the effects of programatically changing the datatable.

add row add 2 rows delete row delete 2 rows add col del col

This example is a variation of one from the YUI examples site. There are some changes, though. Firstly, to the column definitions. We add columnStats: true to the columns we want summarised. You may also want to make sure that formatters are defined.


   var myColumnDefs = [
      {key:"id", sortable:true, resizeable:true},
      {key:"date", formatter:YAHOO.widget.DataTable.formatDate, sortable:true, 
       sortOptions:{defaultDir:YAHOO.widget.DataTable.CLASS_DESC},resizeable:true},
      {key:"quantity", formatter:YAHOO.widget.DataTable.formatNumber, sortable:true, resizeable:true, 
        editor: new YAHOO.widget.TextboxCellEditor(), columnStats: true},
      {key:"amount", formatter:YAHOO.widget.DataTable.formatCurrency, sortable:true, resizeable:true,
        editor: new YAHOO.widget.TextboxCellEditor(), columnStats: true},
      {key:"title", sortable:true, resizeable:true}
  ];

Secondly, the datasource. You need to make sure that parsers are set on the columns you want to summarise:


   myDataSource.responseSchema = {
      fields: ["id", 
        "date",
        {key: "quantity", parser: "number"},
        {key: "amount", parser: "number"},
        "title"]
   };

And then in the DataTable constructor, you add the columnStats to the config object. columnStats should be an object literal with keys 'on', 'pagedTotals', and 'stats'. 'on' === true turns on the stats; 'pagedTotals' determines whether stats are for the entire recordset (false) or just the current view (ie current page with a paginated table) - true. 'stats' is an array of the statistics you want to display. This is the config for the example on this page: more explanation is below.


  myDataTable = new YAHOO.LPLT.DataTable("basic", myColumnDefs, myDataSource, {
   caption:"DataTable Caption", 
   showColumnChooser: true,
   columnStats: {on: true, pagedTotals: true, stats: [
      
      // Custom summary function:
      {label: 'Weighted mean', fn: function(stats, allStats, oCol) {
        
        // stats is the YAHOO.util.Stats instance for this column
        // allStats is an object literal with all the Stats instances
        // for 'summarisable' columns
        // oCol is the column instance
        // this is the DataTable instance
        
        // This example works out a weighted mean - ie. mean sales value
        
        var i, tot = 0, ds;
        
        if (oCol.key == "amount") {
          if (allStats.quantity.stats.sum() == 0) {
            return '';
          }
          
          // This returns either the visible recordset (if pagedTotals is true)
          // or the entire recordset (if pagedTotals is false)
          ds = this.colStatsGetRecordSet();
          
          for (i = 0; i < ds.length; i=i+1) {
            tot = tot + (ds[i].getData("quantity") * ds[i].getData("amount"));
          }
          return tot / (allStats.quantity.stats.sum());
        }
        return '';
        
      }},
      {label: 'Grand total', fn: 'sum'}, 
      {label:'Average', fn: 'mean'}
   ]}, //, 'mean', 'max', 'min', 'stdev', 'range', 'variance'
   draggableColumns: true,
   paginator: new YAHOO.widget.Paginator({
     rowsPerPage: 4
   })
});

The 'stats' value may be an array of strings. These strings should be names of methods on the YAHOO.util.Stats class - meaning you can add methods to the prototype of YAHOO.util.Stats and have them available as summary stats in your datatable, if you want other statistics. Currently you can choose from:

sum
mean
min
max
range
count
median
stdev
variance
varianceUnbiased

If you do this, the attribute setter will normalise the array of strings to an array of objects.

Alternatively, you can pass an array of object literals, specifying 'label' and 'fn' keys. 'label' is the label to be used in the first column (labels always appear there); 'fn' is the name of the function to use (again a string, as above), or a function (as in the example above for 'Weighted mean'). This function needs to return a value for the summary, which will then be formatted (if possible) and rendered.

Or you can use a mixture of strings and object literals!

Any comments, bugs etc, please put them on the github issue tracker https://github.com/mattparker/Yui-DataTable-extension-summary-statistics. Thanks.