CODEWITHSUNDEEP
htmldatatables
softech
softech

Reputation: 376

DataTables footerCallback - conditional on another column value

I'm trying to implement a footerCallback in DataTables that do a conditional sum of some columns, based on a cell that's in a different column in the same row.Can anyone help me with this? I have used below code and check alert(cur_index); but I think it is not working as expected. And I did not get a correct sum of values of a column. My code is: 

pageTotal6 = api
            .column( 6, { page: 'current'} )
            .data()
            .reduce( function (a, b) {
                var cur_index = api.column(6).data().indexOf(b);
                alert(cur_index);
                alert(api.column(3).data()[cur_index]);
                if (api.column(3).data()[cur_index] != "Pending review") {
                    return parseInt(a) + parseInt(b);
                }
                else { return parseInt(a); }
                    return intVal(a) + intVal(b);
            }, 0 );

And in 3rd column I have some repeated value and I want sum only for distinct value from 3rd column. How can I do this 2 thing using datatable & html

Upvotes: 1

Views: 2156

Answers (1)

LavsTo
LavsTo

Reputation: 129

Theres two ways you can go about this.

First Method
(I will assume you are reading JSON data from Database [ViewModel] in C#, and using server-side processing)

Using the image below as reference to how I solved the problem

enter image description here

I wanted to sum of the "Amount" column where "Measure Type" (last column) != 99. First thing I did with the ViewModel that was going to pass the list to my JSON object was add a column sum column that didnt read any MeasureType = 99 rows from the table.

So essentially my JSON object has two columns that read the Amount column data, one is visible that you see in the image that has all figures and another invisible that only reads the values I want to sum in my footer.

while (MyDataReader.Read())
{
   //get all other columns
   //column with amount figures measuretype != 99
   if (reportData.q_measuretype != 99)
   {
      reportData.amountNo99 = Convert.ToDecimal(String.Format("{0:0.00}", read["q_amount"]));
   }
   else
   {
      reportData.amountNo99 = 0;
   }

   list.Add(reportData);

}

After that step, then within the footerCallback function you can keep it simple by just summing the invisible column, because the condition has already been set when you get the list of rows onto the page

totalNettNo99 = api
   .column(8, { page: 'current' }) //remember this is the last invisible column
   .data()
   .reduce(function (a, b) {
        return intVal(a) + intVal(b);
   });

You can then update your footer with that sum on the visible column 3 (index 2) 

$(api.column(2).footer()).html(
     '€' + totalNettNo99.toFixed(2)
);

Remember to set the invisble column this way in "columnDefs"

"ajax": {
                "url": "/Reports/loadTransactionList",
                "type": "POST",
                "datatype": "JSON"

            },

            "columnDefs": [

                {
                    "targets": [8],
                    "visible": false,
                    "searchable": false,
                    "render": false
                }
            ],

            "columns": [
                {
                    "data": "convertDateToString"
                },
                {
                    "data": "convertTimeToString"
                },
                {
                    "data": "q_receiptnumber"
                },

As you can see from the image, only the rows with Guinness Pint total the sum on the footer. Its a bit more typing but solves the problem if you have been tearing your hair with script solution.

Second Method
You can have a look at this answer here done purely in script and less typing that my solution

https://stackoverflow.com/a/42215009/7610106

credit to nkbved

Upvotes: 1

Related Questions