When a user clicks the header to sort I want the row labeled "Grand Total" to stay as the first row visible.
I can catch the table sort event as follows:
google.visualization.events.addListener(table.getChart(), 'sort', function() {
console.log('User clicked to sort.');
};
But I don't know what to do in order to move the total row back to the first record.
How can I achieve this? I appreciate your hints.
Working Example: (starter code)
google.charts.load('current', {
'packages': ['corechart', 'table', 'gauge', 'controls', 'charteditor']
});
$(document).ready(function() {
renderChart_onPageLoad();
});
function renderChart_onPageLoad() {
google.charts.setOnLoadCallback(function() {
drawDashboard();
});
}
function drawDashboard() {
var data = google.visualization.arrayToDataTable([
['Name', 'RoolNumber', 'Gender', 'Age', 'Donuts eaten'],
['Michael', 1, 'Male', 12, 5],
['Elisa', 2, 'Female', 20, 7],
['Robert', 3, 'Male', 7, 3],
['John', 4, 'Male', 54, 2],
['Jessica', 5, 'Female', 22, 6],
['Aaron', 6, 'Male', 3, 1],
['Margareth', 7, 'Female', 42, 8],
['Miranda', 8, 'Female', 33, 6]
]);
var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard'));
var categoryPicker = new google.visualization.ControlWrapper({
controlType: 'CategoryFilter',
containerId: 'categoryPicker',
options: {
filterColumnLabel: 'Gender',
ui: {
labelStacking: 'vertical',
allowTyping: false,
allowMultiple: false
}
}
});
var proxyTable = new google.visualization.ChartWrapper({
chartType: 'Table',
containerId: 'proxyTable',
options: {
width: '500px'
}
});
var table = new google.visualization.ChartWrapper({
chartType: 'Table',
containerId: 'table',
options: {
width: '500px'
}
});
dashboard.bind([categoryPicker], [proxyTable]);
dashboard.draw(data);
google.visualization.events.addListener(dashboard, 'ready', function() {
redrawChart();
});
function redrawChart() {
var sourceData = proxyTable.getDataTable();
var dataResults = sourceData.toDataTable().clone();
var group = google.visualization.data.group(sourceData, [{
// we need a key column to group on, but since we want all rows grouped into 1,
// then it needs a constant value
column: 0,
type: 'number',
modifier: function() {
return 1;
}
}], [{
column: 1,
id: 'SumRool',
label: 'SumRool',
type: 'number',
aggregation: google.visualization.data.sum
}, {
column: 3,
id: 'SumAge',
label: 'SumAge',
type: 'number',
aggregation: google.visualization.data.sum
}, {
// get the average age
column: 4,
id: 'SumEaten',
label: 'SumEaten',
type: 'number',
aggregation: google.visualization.data.sum
}]);
dataResults.insertRows(0, [
['Grand Total', group.getValue(0, 1), null, group.getValue(0, 2), group.getValue(0, 3)],
]);
//Set dataTable
table.setDataTable(dataResults);
table.draw();
// table sort event
google.visualization.events.addListener(table.getChart(), 'sort', function() {
console.log('User clicked header to sort.');
//When a user clicks the header and resorts the table I want the row labeled "Grand Total" to stay as the first row visible.
});
}
}<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard">
<div id="categoryPicker"></div><br />
<div id="proxyTable" style='display:none;'></div>
<div id="table"></div><br /><br />
</div>in order to accomplish, we will need to manually control the sort order.
first, set the sort option on the table chart.
var table = new google.visualization.ChartWrapper({
chartType: 'Table',
containerId: 'table',
options: {
sort: 'event', // <-- set sort to 'event'
width: '500px'
}
});
next, we need to assign event listeners before the chart is drawn.
first, we must wait for the wrapper to be ready,
then assign the sort event to the chart.
but we only want to do this one time, hence --> addOneTimeListener
google.visualization.events.addOneTimeListener(table, 'ready', function() {
google.visualization.events.addListener(table.getChart(), 'sort', function(sender) {
the sort event receives properties for which column and direction the sort occurred. (sender)
we'll use these properties to get the sorted rows from the data table.
afterwards, we find the index of the grand total row, remove it from the sort order,
then add it back as the first index.
// table sort event
google.visualization.events.addOneTimeListener(table, 'ready', function() {
google.visualization.events.addListener(table.getChart(), 'sort', function(sender) {
// sort data table according to sort properties
var tableData = table.getDataTable();
var sortIndexes = tableData.getSortedRows({column: sender.column, desc: !sender.ascending});
// find grand total row
var grandTotal = tableData.getFilteredRows([{
column: 0,
value: 'Grand Total'
}]);
if (grandTotal.length > 0) {
// find grand total in sort
var grandTotalSort = sortIndexes.indexOf(grandTotal[0]);
// remove grand total from sort
sortIndexes.splice(grandTotalSort, 1);
// add grand total as first index
sortIndexes.unshift(grandTotal[0]);
// set table sort arrow
table.setOption('sortAscending', sender.ascending);
table.setOption('sortColumn', sender.column);
// set table view
table.setView({rows: sortIndexes});
// re-draw table
table.draw();
}
});
});
see following working snippet...
google.charts.load('current', {
'packages': ['corechart', 'table', 'gauge', 'controls', 'charteditor']
});
$(document).ready(function() {
renderChart_onPageLoad();
});
function renderChart_onPageLoad() {
google.charts.setOnLoadCallback(function() {
drawDashboard();
});
}
function drawDashboard() {
var data = google.visualization.arrayToDataTable([
['Name', 'RoolNumber', 'Gender', 'Age', 'Donuts eaten'],
['Michael', 1, 'Male', 12, 5],
['Elisa', 2, 'Female', 20, 7],
['Robert', 3, 'Male', 7, 3],
['John', 4, 'Male', 54, 2],
['Jessica', 5, 'Female', 22, 6],
['Aaron', 6, 'Male', 3, 1],
['Margareth', 7, 'Female', 42, 8],
['Miranda', 8, 'Female', 33, 6]
]);
var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard'));
var categoryPicker = new google.visualization.ControlWrapper({
controlType: 'CategoryFilter',
containerId: 'categoryPicker',
options: {
filterColumnLabel: 'Gender',
ui: {
labelStacking: 'vertical',
allowTyping: false,
allowMultiple: false
}
}
});
var proxyTable = new google.visualization.ChartWrapper({
chartType: 'Table',
containerId: 'proxyTable',
options: {
width: '500px'
}
});
var table = new google.visualization.ChartWrapper({
chartType: 'Table',
containerId: 'table',
options: {
sort: 'event',
width: '500px'
}
});
// table sort event
// Moved this listener to main drawDashboard() because having this in redrawChart() was adding a new listener every time someone filtered categoryPicker.
google.visualization.events.addOneTimeListener(table, 'ready', function() {
google.visualization.events.addListener(table.getChart(), 'sort', function(sender) {
// sort data table according to sort properties
var tableData = table.getDataTable();
var sortIndexes = tableData.getSortedRows({column: sender.column, desc: !sender.ascending});
// find grand total row
var grandTotal = tableData.getFilteredRows([{
column: 0,
value: 'Grand Total'
}]);
if (grandTotal.length > 0) {
// find grand total in sort
var grandTotalSort = sortIndexes.indexOf(grandTotal[0]);
// remove grand total from sort
sortIndexes.splice(grandTotalSort, 1);
// add grand total as first index
sortIndexes.unshift(grandTotal[0]);
// set table sort arrow
table.setOption('sortAscending', sender.ascending);
table.setOption('sortColumn', sender.column);
// set table view
table.setView({rows: sortIndexes});
// re-draw table
table.draw();
}
});
});
dashboard.bind([categoryPicker], [proxyTable]);
dashboard.draw(data);
google.visualization.events.addListener(dashboard, 'ready', function() {
redrawChart();
});
function redrawChart() {
var sourceData = proxyTable.getDataTable();
var dataResults = sourceData.toDataTable().clone();
var group = google.visualization.data.group(sourceData, [{
// we need a key column to group on, but since we want all rows grouped into 1,
// then it needs a constant value
column: 0,
type: 'number',
modifier: function() {
return 1;
}
}], [{
column: 1,
id: 'SumRool',
label: 'SumRool',
type: 'number',
aggregation: google.visualization.data.sum
}, {
column: 3,
id: 'SumAge',
label: 'SumAge',
type: 'number',
aggregation: google.visualization.data.sum
}, {
// get the average age
column: 4,
id: 'SumEaten',
label: 'SumEaten',
type: 'number',
aggregation: google.visualization.data.sum
}]);
dataResults.insertRows(0, [
['Grand Total', group.getValue(0, 1), null, group.getValue(0, 2), group.getValue(0, 3)],
]);
//Reset view to clear any user sorting initiated by event listener table.getChart(), 'sort'
table.setView(null);
//Set dataTable
table.setDataTable(dataResults);
table.draw();
}
}<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard">
<div id="categoryPicker"></div><br />
<div id="proxyTable" style='display:none;'></div>
<div id="table"></div><br /><br />
</div>