How to add Datatables Custom row rendering / aggregates

I’m trying to add a custom row to a serverside rendered data table, inorder to show the total amount of a column the following is how the table headers are Date | Name | Amount | Ref |

 <table id="tableExport_filter">
    <thead>
      <tr>
       <th>Date</th>
       <th>Name</th>
       <th>Amount</th>
       <th>Ref</th>
     </tr>
    </thead>
    <tbody>
     <tr></tr>
    </tbody>                                   
  </table>

to get the data using AJAX

var dataTable = $('#tableExport_filter').DataTable({
    'processing': true,
    'serverSide': true,
    'serverMethod': 'post',

 'ajax': {
       'url':'app/functions/collections_by_agent.php',
       'data': function(data){}
    },
'columns': [
      
       { data: 'date_created'},
       { data: 'name'},
       { data: 'amount' }, 
       { data: 'ref' }
    ],
});

I need help to append a row to the table and Add the Sum total of the amount column

Answer

I would recommend doing this by using the table footer <tfoot> instead of adding a new row to the body of the table.

Steps:

1. In your HTML table, add an empty footer after the closing <tbody> tag:

<tfoot>
  <tr>
   <th></th>
   <th></th>
   <th></th>
   <th></th>
 </tr>
</tfoot>

2. Add a footerCallback option to your DataTable:

var dataTable = $('#tableExport_filter').DataTable({

  // your existing options go here

  "footerCallback": function( tfoot, data, start, end, display ) {
    var api = this.api();
    $( api.column( 2 ).footer() ).html(
      api.column( 2 ).data().reduce( function ( a, b ) {
        return a + b;
      }, 0 )
    );
  }

} );

In this function, you use var api = this.api(); to gain access to the DataTables API functions from within the table itself.

You then select column index 2 (i.e. the 3rd column) of the footer as the target for the sum.

Finally, you use a reduce function to sum up all the values from data column index 2. That 0 at the end of the function is the starting value used when performing the initial step of the reduce function.

The footer callback is documented here.

The reduce function is documented here.