Friday 7th April, 2017

RowGroup - new extension

Grouping rows into sets of data that share a single characteristic can be very powerful tool, letting end user's easily understand and take knowledge from a complex data sets. A single table will provide a base grouping (for example showing a list of students), but you might also wish to group by a data point inside the table (e.g. age in the case of the students example).

The DataTables examples have long included an example showing how row grouping can be done, but any customisation requires modification of the code presented in that example; it isn't always intuitive what those changes should be if the developer is new to DataTables. As such, it gives me great pleasure to introduce a new extension for DataTables: RowGroup.

Name Position Office Salary

Download

The RowGroup extension is available on the DataTables CDN and in the download builder. You can also install it from NPM and Bower.

More examples can be found in the RowGroup documentation.

Features

Fundamentally RowGroup takes a single data point from the table's data (this doesn't need to be in a column - unless you want to be able to order the table by that data) and visually groups the table based on rows which have similar values.

The group is shown with a header and / or footer row for each group which is inserted dynamically into the table. By default only a grouping header is shown with the value of the grouping data point, but the start and end grouping rows are entirely under your control and custom renderers can be used to display summary information that you compute about rows (totals, averages, counts, etc).

Usage

The most commonly used option for RowGroup will be the rowGroup.dataSrc option. This is used to tell it where to read the data for the grouping. This parameter is very similar to columns.data in that it can be given as an integer (for array sourced tables) or a string (for object sourced tables). The string option also supports Javascript dotted object notation so you can read data from nested properties.

DOM / Array sourced data

Initialisation:

$('#myTable').DataTable( {
    rowGroup: {
        // Group by office
        dataSrc: 2
    }
} );

Row data source:

<tr>
    <td>Tiger Nixon</td>
    <td>System Architect</td>
    <td>Edinburgh</td>
    <td>61</td>
    <td>2011/04/25</td>
    <td>$320,800</td>
</tr>

An HTML table with the following structure for the tbody rows (note that you could also use rowGroup.dataSrc as an integer if you use array based data which has been Ajax loaded or sourced from Javascript).

Ajax / Object

Initialisation:

$('#myTable').DataTable( {
    ajax: '/api/staff',
    columns: [
        { data: 'name' },
        { data: 'position' },
        { data: 'office' },
        ...
       ],
    rowGroup: {
        // Group by office
        dataSrc: 'office'
    }
} );

Row data source:

{
  "name": "Tiger Nixon",
  "position": "System Architect",
  "salary": "$320,800",
  "start_date": "2011/04/25",
  "office": "Edinburgh",
  "extn": "5421"
}

After the ability to specify what data should be used for grouping the rowGroup.startRender and rowGroup.endRender options will the next most commonly used options. These define what will be shown for the start and end grouping rows respectively. Each can be set to null to disable them (which the end is by default) or can be set to be a function that takes a DataTable.Api instance filled with the rows of the group (i.e. the result of rows()) and also the value of the grouping data point.

The rendering functions let you customise what the end user sees, so you can perform calculations such as counts, summations, averages, etc. In this example the end renderer is used to show the average of the salary and age columns inline with the rest of the table:

$('#myTable').DataTable( {
    order: [[2, 'asc']],
    rowGroup: {
        startRender: null,
        endRender: function ( rows, group ) {
             var ageAvg = rows
                .data()
                .pluck(3)
                .reduce( function (a, b) {
                    return a + b*1;
                }, 0) / rows.count();

            var salaryAvg = rows
                .data()
                .pluck(5)
                .reduce( function (a, b) {
                    return a + b.replace(/[^\d]/g, '')*1;
                }, 0) / rows.count();
            salaryAvg = $.fn.dataTable.render.number(',', '.', 0, '$').display( salaryAvg );

            return $('<tr/>')
                .append( '<td colspan="3">Averages for '+group+'</td>' )
                .append( '<td>'+ageAvg.toFixed(0)+'</td>' )
                .append( '<td/>' )
                .append( '<td>'+salaryAvg+'</td>' );
        },
        dataSrc: 2
    }
} );

Further examples can be found in the RowGroup documentation and reference.

Known limitations

It is worth highlighting that RowGroup is currently not compatible with the following:

  • The export options of Buttons
  • FixedColumns
  • Scroller.

The compatibility chart details the compatibility between DataTables core features and the extensions.

Future work

You might think, that all sounds nice, but what about other features such as collapsing groups, multi-level groups, etc. This is an initial release that makes the base software available to the community - actually writing this initial version of the software took less than a day, but it has taken the best part of two weeks to make it publishable with testing, examples, documentation and deployment infrastructure taking the majority of the time. With this all now in place it makes it easier to bring new features to the software.

With that in mind, the following features are currently planned:

  • Click to sort on the grouping data point
  • Click and drag to group - dragging a column header into a grouping zone to allow easy end user defining of grouping
  • Multi-level grouping - allowing nested grouping
  • Support for Button's export options - currently the grouping information will be ignored on export.

Longer term work:

  • Expand and collapse groups - because DataTables core doesn't have any concept of groups this is not something that is going to be trivial to add
  • Rowspan display layout option - showing the visual hierarchy clearer.

These new features aren't going to happen over night I'm afraid. Like the rest of the DataTables it is a constantly evolving process that will be done alongside improvements to the rest of the DataTables project.

This is my list of thoughts for what could be added to the software, but I am also keep to receive feedback from you as to what you would like to see added to RowGroup in future!

Enjoy!