Grand Total in Google visualization dashboard table

I have a simple Google visualization dashboard sample with one categorypicker control and a table. My code is given below.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load('visualization', '1.1', {packages: ['controls']});
    </script>
    <script type="text/javascript">
      function drawVisualization() {
        // Prepare the data.
        var data = google.visualization.arrayToDataTable([
          ['Metric', 'Value'],
          ['CPU' , 12],
          ['Memory', 20],
          ['Disk', 7],
          ['Network', 54]
        ]);

        // Define a category picker for the 'Metric' column.
        var categoryPicker = new google.visualization.ControlWrapper({
          'controlType': 'CategoryFilter',
          'containerId': 'control',
          'options': {
            'filterColumnLabel': 'Metric',
            'ui': {
              'allowTyping': false,
              'allowMultiple': true,
              'selectedValuesLayout': 'belowStacked'
            }
          },

        });

        // Define a table.
        var table = new google.visualization.ChartWrapper({
          'chartType': 'Table',
          'containerId': 'chart',
          'options': {
            'width': 400,
            'height': 180
          }
        });

        // Create the dashboard.
        var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard')).
          // Configure the category picker to affect the table
          bind(categoryPicker, table).
          // Draw the dashboard
          draw(data);
      }

      google.setOnLoadCallback(drawVisualization);
    </script>
  </head>
  <body style="font-family: Arial;border: 0 none;">
    <div id="dashboard">
      <table>
        <tr style='vertical-align: top'>
          <td style='width: 300px; font-size: 0.9em;'>
            <div id="control"></div>
          </td>
          <td style='width: 600px'>
            <div style="float: left;" id="chart"></div>
          </td>
        </tr>
      </table>
    </div>
  </body>
</html>

I am stuck with a problem that I want the grand total of the values at the end. How can I add one more row at the end of table so that it shows the Grand total of all values in second column. i.e., initially it should show the total of all four rows and when filter is applied it should show the total of filtered row values.

Solution

You can just add a row before you "draw" the chart that shows the total:

For instance:

  var data = google.visualization.arrayToDataTable([
    ['Year', 'Austria', 'Belgium', 'Czech Republic', 'Finland', 'France', 'Germany'],
    ['2003',  1336060,   3817614,       974066,       1104797,   6651824,  15727003],
    ['2004',  1538156,   3968305,       928875,       1151983,   5940129,  17356071],
    ['2005',  1576579,   4063225,       1063414,      1156441,   5714009,  16716049],
    ['2006',  1600652,   4604684,       940478,       1167979,   6190532,  18542843],
    ['2007',  1968113,   4013653,       1037079,      1207029,   6420270,  19564053],
    ['2008',  1901067,   6792087,       1037327,      1284795,   6240921,  19830493]
  ]);

  function getSum(data, column) {
    var total = 0;
    for (i = 0; i < data.getNumberOfRows(); i++)
      total = total + data.getValue(i, column);
    return total;
  }

  alert(getSum(data, 1));

This will give you a nice alert box saying 9920627

1336060+1538156+1576579+1600652+1968113+1901067=9920627

You can just add a line to the end saying something like:

addRow('Total', getSum(data, 1));

If the question is on how to get the sum of a column that's been filtered by your sliders and the like, that's a different beast, and something that this jsfiddle by asgallant may be better for:

google.load('visualization', '1.1', {packages: ['controls']});
google.setOnLoadCallback(drawVisualization);

function drawVisualization() {
    // Prepare the data
    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]
    ]);

    // Define a slider control for the Age column.
    var slider_rn = new google.visualization.ControlWrapper({
        controlType: 'NumberRangeFilter',
        containerId: 'control2',
        options: {
            filterColumnLabel: 'RoolNumber',
            ui: {
                labelStacking: 'vertical'
            }
        }
    });

    var slider = new google.visualization.ControlWrapper({
        controlType: 'NumberRangeFilter',
        containerId: 'control1',
        state: {
            lowValue: 10,
            highValue: 40,
            highThumbAtMax: true
        },
        options: {
            filterColumnLabel: 'Age',
            ui: {
                labelStacking: 'vertical'
            }
        }
    });

    // Define a category picker control for the Gender column
    var categoryPicker = new google.visualization.ControlWrapper({
        controlType: 'CategoryFilter',
        containerId: 'control3',
        options: {
            filterColumnLabel: 'Gender',
            ui: {
                labelStacking: 'vertical',
                allowTyping: false,
                allowMultiple: false
            }
        }
    });

    // Define a Pie chart
    var pie = new google.visualization.ChartWrapper({
        chartType: 'PieChart',
        containerId: 'chart1',
        options: {
            width: 300,
            height: 300,
            legend: 'none',
            title: 'Donuts eaten per person',
            chartArea: {
                left: 15,
                top: 15,
                right: 0,
                bottom: 0
            },
            pieSliceText: 'label'
        },
        // Instruct the piechart to use colums 0 (Name) and 3 (Donuts Eaten)
        // from the 'data' DataTable.
        view: {
            columns: [0, 4]
        }
    });

    // Define a table
    var table = new google.visualization.ChartWrapper({
        chartType: 'Table',
        containerId: 'chart2',
        options: {
            width: '500px'
        }
    });

    // get average and geometric mean values
    google.visualization.events.addListener(table, 'ready', function () {
        var group = google.visualization.data.group(table.getDataTable(), [{
            // 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;
            }
        }], [{
            // get the average age
            column: 3,
            label: 'Average Age',
            type: 'number',
            aggregation: google.visualization.data.avg
        }, {
            // get the geometric mean of age
            column: 3,
            label: 'Geometric Mean of Age',
            type: 'number',
            aggregation: function (values) {
                var product = 1;
                var n = values.length;
                for (i = 0; i < n; i++) {
                    product = product * values[i];
                }
                return nthroot(product, n);
            }
        }]);
        document.getElementById('avg').innerHTML = group.getValue(0, 1);
        document.getElementById('geomean').innerHTML = group.getValue(0, 2);
    });

    // Create a dashboard
    new google.visualization.Dashboard(document.getElementById('dashboard')).
    // Establish bindings, declaring the both the slider and the category
    // picker will drive both charts.
    bind([slider, slider_rn, categoryPicker], [table, pie]).
    // Draw the entire dashboard.
    draw(data);
}

// nthroot function courtesy of http://gotochriswest.com
// see http://gotochriswest.com/blog/2011/05/06/cube-root-an-beyond/
// special function necessary because Math.pow(-8, 1/3) returns NaN instead of -2
function nthroot(x, n) {
    try {
        var negate = n % 2 == 1 && x < 0;
        if (negate) {
            x = -x;
        }
        var possible = Math.pow(x, 1 / n);
        n = Math.pow(possible, n);
        if (Math.abs(x - n) < 1 && (x > 0 == n > 0)) {
            return negate ? -possible : possible;
        }
    } catch(e){}
}