multiply two columns in Jquery datatables and sum it

Question

I have a datatable, and displays fine. I can sum a particular column. I want to multiply two columns and sum the resultant multiplications. Below is the Datatable Jquery code.

I want to multiply column5 and Column7 and then Sum the multiplication.

    <script type="text/javascript">
    $(document).ready(function () {

        $("#btnGet").click(function () {

            $('.spinner').css('display', 'block');  //if clicked ok spinner shown
            $(function () {
                $.ajax({
                    type: "POST",
                    url: "/Reports/ItemsReports",


                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        $("#tblAllItems").DataTable(
                            {
                                dom: 'Blfrtip',

                                bLengthChange: true,
                                lengthMenu: [[10, 20, -1], [10, 20, "All"]],
                                bFilter: true,
                                bSort: true,
                                bPaginate: true,
                                buttons: [
                                    'csv',
                                    {
                                        extend: 'excel',
                                        messageTop: 'All Items Report',
                                        footer: true
                                    },
                                    {
                                        extend: 'pdf',
                                        messageTop: 'All Items Report ',
                                        footer: true
                                    },
                                    'print'],

                                data: response,
                                columns: [{ 'data': 'Item_ID' },
                                    { 'data': 'Item_category' },
                                    { 'data': 'Item_Name' },
                                    { 'data': 'Item_Description' },
                               {
                                    'data': 'Cost_Price', render: $.fn.dataTable.render.number(',', '.', 2,) // Format to return Currency with comma and 2d.p
                               },
                               {
                                   'data': 'Sales_Price', render: $.fn.dataTable.render.number(',', '.', 2,) // Format to return Currency with comma and 2d.p
                               },

                                { 'data': 'Quantity' },
                              
                                { 'data': 'Reorder_Level' },

                                { 'data': 'Barcode1' },
                                { 'data': 'Barcode2' },
                                { 'data': 'Barcode3' },
                                { 'data': 'Barcode4' },
                                { 'data': 'Item_Discontinued' },
                                    { 'data': 'Supplier' },                                    
                                    { 'data': 'Date_Added' },
                                    { 'data': 'Added_By' },
                                
                                ],

                                // Getting the Sum of the Account Balance
                                "footerCallback": function (row, data, start, end, display) {
                                    var api = this.api(), data;

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

                                    // Total over all pages
                                    total = api
                                        .column(5)
                                        .data()
                                        .reduce(function (a, b) {
                                            return a + b;
                                        }, 0);

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

                                    // Update footer
                                    var numFormat = $.fn.dataTable.render.number(',', '.', 2,).display; // This is to format the output to 2d.p
                                    $(api.column(5).footer()).html(numFormat(pageTotal) + ' ( ' + numFormat(total) + ' OverAllTotal)'
                                    );
                                },

                                destroy: true // To allow for Table Reinitialization


                            });
                        $('.spinner').css('display', 'none');
                    },
                    failure: function (response) {
                        alert(response.d);
                        $('.spinner').css('display', 'none');
                    },
                    error: function (response) {
                        alert(response.d);
                        $('.spinner').css('display', 'none');
                    }
                });
            });

        });

    });

</script>

I want to multiply column5 and Column7 and then Sum the multiplication.

Answer 1

I found a way around using array.

// This is to be used if no need for the totalcostpricecolumn
var costPrice = api.column(4).data().toArray();
var qty = api.column(6).data().toArray();

var total = 0;

for (i = 0; i < costPrice.length; i++) {
    total += costPrice[i] * qty[i];
}
$(api.column(7).footer()).html(numFormat(total)); // Display the totalcostPrice