A data table with grand totals, thanks to Google Charts

8 May 2015, by Baptiste Autin

The Datatable is the core structure of the powerful Google Visualization API. As its name suggests, a Datatable can be used to generate HTML data tables, through a google.visualization.Table object, but it can also be used to generate graphics (along with Google Charts).

Unfortunately, by default, a google.visualization.Table cannot automatically display grand totals, lines or columns, so I will show you how to do that with a bit of script.

Our starting point is just some Javascript objects:

	var data = new google.visualization.DataTable();
	data.addColumn('string', 'Nom');
	data.addColumn('number', 'Janvier');
	data.addColumn('number', 'Février');
	data.addColumn('number', 'Mars');
	data.addColumn('number', 'Avril');
	data.addColumn({type: 'number', label: 'Total'});

	data.addRow(['Jean', 5, 12, 18, 25, 60]);
	data.addRow(['Robert', 0, 1, 20, 2, 23]);
	data.addRow(['Maud', 11, 5, 7, 9, 32]);
	data.addRow(['Tom', 3, 1, 1, 4, 9]);

After a bit of magic on the Datatable data, we create a google.visualization.Table object, we invoke its method draw(), and here is what we get:


Not bad, isn’it?

Read the complete source code here.

Although it is rather easy to get the vertical total (by applying an aggregation google.visualization.data.sum), the horizontal total is more tricky to obtain (you must create x aggregation columns, filter, and add the line).

Note that all columns are sortable, an elegant default feature of the google.visualization.Table

Also note that we had to hack a bit the Total column sort. The last row must be excluded from sorting, indeed, otherwise you’ll see the grand total move up one time in two: hence the listener on the ‘sort’ event, which maintains the grand total at the bottom of the table.

Derived bar charts

As a final present, here are two bar charts, easily obtained from our previously built Datatable, thanks to the Google Charts API.
The first graphic is rendered by instanciating a ColumnChart, and by calling its method draw() with data as parameter:

	var chart = new	google.visualization.ColumnChart(document.getElementById("chart_div"));
	data.removeColumn(data.getNumberOfColumns()-1);		// We must remove the Total column!
	chart.draw(data, options);

To generate the second chart (where columns and rows have swapped), we just need to build an inverted Datatable of data by pivoting.
The code of this permutation can be found in the full source code of the example.

Laisser une réponse

«