Summing Column Data - Using the DataTables and Editor API Part 1

In an earlier blog post, I gave examples of how the DataTables and Editor APIs can be used to enhance a CloudTable. This blog post takes that further, describing a method to summarize data from the table is the form of summations.

Button-like Summary

The example below is what we will be building in this article - the block above the table contains a summary of the total salaries for each location. You can also click on each location to filter the table.

The code to achieve this is broken down below.

First, we need to iterate through the rows in the table to calculate the salary for each office.

// Create an object that contains all the salary information
function calculateSalaries() {
  var obj = {};

  // iterate over each row
  table.rows().every(function(idx) {
    var data = table.row(idx).data();

    // iterate over each office
    data['l-5'].forEach(function(o) {
      var office = JSON.parse(o['dp-12'])[0].text;

      if (obj[office] === undefined) {
          obj[office] = {
              total: parseInt(data['dp-11']),
              color: o.color
          };
      }
      else {
          obj[office].total = parseInt(data['dp-11']) + obj[office].total;
      }
    });
  });

  return obj;
}

Once we have those totals for each office, we can then insert it into the document. In this example, we use a flexbox div with each office having its own element in the container to create the tidy spacing.

// Insert the data into the DOM
function insertIntoDOM(arr) {
	let summary = $('#totals-summary').empty();

	for (const key in arr) {
		summary.append(
			$('<div>')
				.addClass('total')
				.data('key', key)
				.css({
					background: arr[key].color,
					color: '#000000'
				})
				.append('<h3>' + key + '</h3>')
				.append('<h4>' + $.fn.dataTable.render.number(',', '.', 0, '$').display(arr[key].total) + '</h4>')
				.appendTo(summary)
		);
	}
}

And finally, we need to tie it all together by creating that summary information on the first draw of the table. We can also add an additional feature by allowing the summaries to also trigger a search when clicked upon.

// Update the salaries table on the first draw of the CloudTable
table.one('draw', function() {
  insertIntoDOM(calculateSalaries());

  table.columns([1,5,6]).visible(false);

  // Add filtering when a record is clicked upon
  $('.total').on('click',function() {
    var office = $(this).data('key');

    // Toggle the search
    if (table.search() === office) {
        table.search('').draw();
    }
    else {
        table.search(office).draw();
    }
  });
});

Coming soon

In the next blog post I'll demonstrate how that summary data can be included in a DataTable.