CODEWITHSUNDEEP
javascriptchartsgoogle-visualization
cmill
cmill

Reputation: 891

Google Visualization - On event table sort, keep specific row as first visible record?

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>

Upvotes: 2

Views: 949

Answers (1)

WhiteHat
WhiteHat

Reputation: 61222

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>

Upvotes: 3

Related Questions