CODEWITHSUNDEEP
datatables
Tim
Tim

Reputation: 424

Dynamically calculate percentage in a datatable

How can I dynamically calculate a percentage in a datatable when a search is applied ? The result should be like this :

<tr>
<td>10%</td><td>100</td>
<td>90%</td><td>900</td>
</tr>

The percentage should be based on the total of rows shown.

Upvotes: 0

Views: 1084

Answers (1)

andrewJames
andrewJames

Reputation: 21918

The following example shows you a basic approach.

The end result looks like this - note you may get a small rounding error when summing the percentages:

enter image description here

The code is as follows:

UPDATE - There was a problem with my original solution - it used jQuery to populate DataTable cells with the calculated percentages. That meant DataTables did not respond to sorting/filtering for the percentages column. It's better to use DataTables functions to populate the data cells. 

<body>

<div style="margin: 20px;">

<table id="example" class="display dataTable cell-border" style="width:100%">
  <thead>
   <tr><th>ID</th><th>Amount</th><th>Percent</th></tr>
  </thead>
  <tbody>
    <tr><td>1</td><td>123.4</td><td class="percent"></td></tr>
    <tr><td>2</td><td>234.5</td><td class="percent"></td></tr>
    <tr><td>3</td><td>543.21</td><td class="percent"></td></tr>
    <tr><td>4</td><td>76</td><td class="percent"></td></tr>
    <tr><td>5</td><td>87</td><td class="percent"></td></tr>
  </tbody>
</table>

</div>

<script type="text/javascript">

  $(document).ready(function() {

    var table = $('#example').DataTable({

      // wait for table and data to finish being initialized:
      "initComplete": function( settings, json ) {
        populatePercentages();
      }

    });

    function populatePercentages() {
      var total = 0.0;

      // first calcuate the total:
      $('#example').DataTable().rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        // assumes amounts are not null, all numeric, and are in column 2:
        total = total + Number(this.data()[1]); 
      });

      // then calculate the percentage for each row:
      $('#example').DataTable().rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        var amount = this.data()[1];
        // calculate percent to 2 decimal places:
        var percentage = Math.round((amount / total) * 10000) / 100;
        var cells = $('#example').DataTable().cells(rowIdx, 2);       
        cells.every( function () {
          this.data(percentage);
        });

      });
    }

  });
</script>

</body>

Points to note:

1) It uses a "percent" class for the cells where percentages will be shown. This makes selection easier.

2) You can do something similar as (1) for the amounts cells also. I did not, here.

3) The code loops through the table data twice: Once to calculate the grand total amount; and again to calculate the percentages.

4) You would need to adjust assumptions about column offsets to fit your table design.

5) There is more validation which should really be added, to handle missing or non-numeric data, to make the code less brittle.

Handling the case when a search is applied:

Filtering:

The above solution can be enhanced to re-calculate percentages when data is filtered.

1) There are two places where the rows() function is used. In both places change this to the following: rows( { search: 'applied' } ). This will ensure only visible rows are considered when percentages are calculated.

2) Add a new section of code to handle keyup events for the search (filter) box. In my case the selector for this input control is as shown below - but this will need to be edited for other datatables with different IDs (my example table's ID is example):

<script type="text/javascript">

  $(document).ready(function() {

    var table = $('#example').DataTable({
      ...
    });

    function populatePercentages() {
      ...
    }

    // catch filter events and re-calculate percentages accordingly:
    $('div#example_filter.dataTables_filter > label > input').keyup(function (e) {
      populatePercentages();
    });

  });
</script>

With these changes, a filtered list will look like this:

enter image description here

Upvotes: 2

Related Questions