Answers

• kthorngren Posts: 21,330Questions: 26Answers: 4,951

  September 2023

  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 Posts: 28Questions: 4Answers: 0

  September 2023 edited September 2023

  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 Posts: 21,330Questions: 26Answers: 4,951

  September 2023 edited September 2023 Answer ✓

  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 Posts: 28Questions: 4Answers: 0

  September 2023

  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!