footerCallback: sum of pageTotal and total gives the same amount

Brecht2727

Hi all,

The total and pageTotal values gives me the same amount. Any idea why?

    footerCallback: function (row, data, start, end, display) {
      let api = this.api();

      // Remove the formatting to get integer data for summation
      let intVal = function (i) {
        return typeof i === 'string'
          ? i.replace(/[\$,]/g, '') * 1
          : typeof i === 'number'
          ? i
          : 0;
      };

      // Total over all pages
      total = api
          .column(13)
          .data()
          .reduce((a, b) => intVal(a) + intVal(b), 0);

      // Total over this page
      pageTotal = api
          .column(13, { page: 'current' })
          .data()
          .reduce((a, b) => intVal(a) + intVal(b), 0);

      // Update footer
      api.column(1).footer().innerHTML = 'Page total: ' + pageTotal + ' € ('+ total + ' € total over all pages)';
    }

kthorngren

The only reason I can think of is that you are using server side processing. In this case the only data available for column().data() are the rows on the page as these are to only rows at the client. Do you have serverSide: true?

Kevin

Brecht2727

Yes, i am using server side processing. Data comes over ajax.

The amount of pageTotal is correct but the total amount is the same and not the sum over all pages.

Is there a solution?

kthorngren

You will need to calculate the total in your server side script and add the total to the JSON response for each draw. The standard response parameters are documented here. For example add something like totalSum to the response:

{
    "data": [
        ....
    ],
    "draw": 1,
    "recordsFiltered": 57,
    "recordsTotal": 57,
    "totalSum": 5000
}

In the footerCallback you can use ajax.json() to get the last JSON response to fetch the totalSum.

Kevin

Brecht2727

Hi Kevin,

I have created a separate query that will calculate the total sum outside my foreach loop in the server side script. Below is the response code:

$response = array(
  "draw" => intval($draw),
  "iTotalRecords" => $totalRecords,
  "iTotalDisplayRecords" => $totalRecordwithFilter,
  "aaData" => $data,
  "totalSum" => intval($tot_amount[0]['tot_sum'])
);

And the footerCallback is as below:

    footerCallback: function (row, data, start, end, display) {
      let api = this.api();

      // Remove the formatting to get integer data for summation
      let intVal = function (i) {
        return typeof i === 'string'
          ? i.replace(/[\$,]/g, '') * 1
          : typeof i === 'number'
          ? i
          : 0;
      };

      // Total over all pages
      total = $('#myTable').DataTable().ajax.json().totalSum;

      // Total over this page
      pageTotal = api
          .column(13, { page: 'current' })
          .data()
          .reduce((a, b) => intVal(a) + intVal(b), 0);

      // Update footer
      api.column(1).footer().innerHTML = 'Page total: ' + pageTotal + ' € ('+ total + ' € total over all pages)';
    }

Now all working fine. Many thanks!