How to include currency format in sum datatable

Question

I have this datatable, I want to sum the column and add the result with currency format in the footer . The sum is working fine but I can't figure out how to add the curency format in the totals. Can you help me please?

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

user_12567365 · Answer

You can make the following changes to display the column sums as currency amounts:

Assign the reduce function to a separate variable.
Use the JavaScript Intl.NumberFormat() function to format the sum as a currency.
(Optional step) Create a common function for each column, to avoid repeating the same code multiple times.

Steps 1 and 2 would look as follows for a single column:

footerCallback: function( tfoot, data, start, end, display ) {
  var api = this.api();
  var sum = api.column(1).data().reduce(function ( a, b ) {
      return a + b;
    }, 0);
  var amount = Intl.NumberFormat('en-US', {style: 'currency', currency: 'USD'}).format(sum);
  $(api.column(1).footer()).html(amount);
}

You can obviously change the USD currency code to whatever currency format you prefer.

For step 3, there are different approaches.

Here is one approach: We start with an array containing the column indexes where we want to display a sum. In my case this is [1, 2]. Each value is passed to a function doSum():

var dataSet = [
    {
      "id": "1",
      "amount_a": 1,
      "amount_b": 12.34
    },
    {
      "id": "2",
      "amount_a": 3,
      "amount_b": 456.78
    },
    {
      "id": "3",
      "amount_a": 5,
      "amount_b": 678.90
    },
    {
      "id": "4",
      "amount_a": 2,
      "amount_b": 32.21
    },
    {
      "id": "5",
      "amount_a": 3,
      "amount_b": 1.12
    },
    {
      "id": "6",
      "amount_a": 1,
      "amount_b": 2.23
    },
    {
      "id": "7",
      "amount_a": 4,
      "amount_b": 3.34
    }
  ];
 
$(document).ready(function() {

  var table = $('#example').DataTable( {
    data: dataSet,
    columns: [
      { title: "ID", data: "id" },
      { title: "Amount A", data: "amount_a" },
      { title: "Amount B", data: "amount_b" }
    ],

    footerCallback: function( tfoot, data, start, end, display ) {
      var api = this.api();
      [1, 2].forEach( function( colIdx ) {
        doSum( api.column(colIdx) );
      } );
    }

  } );

  function doSum(col) {
    var sum = col.data().reduce(function ( a, b ) {
      return a + b;
    }, 0);
    var amount = Intl.NumberFormat('en-US', {style: 'currency', currency: 'USD'}).format(sum);
    $(col.footer()).html(amount);
  }
  
} );

How to include currency format in sum datatable

1 Answers