Add Columns And Separate By Category

Question

I have a datatable that is using standard features (pagination, sorting, searching, date range, etc.), but I also need to have a portion at the bottom of the table that displays the total salary of each office. The output would (ideally) look something like this if you searched for, say, "engineer":

London: $295,500
San Francisco: $409,350
Singapore: $234,500
Tokyo: $139,575
Edinburgh: $103,600
New York: $125,250
Total Hours: $1,307,775.00

I have tried a handful of different approaches, but frankly my script knowledge is lacking and I am out of my depth. Can anyone point me in the right direction on how to solve this issue?

Here is my script:

"footerCallback": function (row, 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;
                };


                // ************NOT WORKING************ \\   
                // Total by category

                // First Attempt 
                if (api.column(5,
                    {
                    search: 'applied'
                    })
                    .data()
                    .length) {
                    var byCategory = api
                        .rows()
                        .data()
                        .reduce(function (a, c) {
                            a[c[7]] = a[c[7]] || 0;
                            a[c[7]] += intVal(c[5]);
                            return a;
                        },
                        {});
                }
                else {
                    byCategory = 0;
                }
                console.clear();
                console.dir('by category', byCategory); 
                /*
                // Second Attempt
                if (api.column(5, {
                    search: 'applied'
                }).data().length) {
                    var byCategory = api
                        .rows(5, {
                            search: 'applied'
                        })
                        .data()
                        .reduce(function (category, hours) {
                            category[hours[7]] = category[hours[7]] || 0;
                            category[hours[7]] += intVal(hours[5]);
                            return category;
                        }, {});
                }
                else {
                    byCategory = 0;
                }
                console.clear();
                console.dir('by category', byCategory); */
                // ************NOT WORKING************ \\  

                // Third Attempt
                /*var byCategory = api
                    .rows()
                    .data()
                    .reduce(function (a, c) {
                    a[c[7]] = a[c[7]] || 0;
                    a[c[7]] += intVal(c[5]);

                        for (var key in byCategory) {
                            if (byCategory.hasOwnProperty(key)) {
                                console.log(key + " -> " + byCategory[key]);
                            }
                        }                    
                }, {}); */

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

                // Total over all filtered pages
                if (api.column(5, {
                    search: 'applied'
                }).data().length) {
                    pageTotal = api
                        .column(5, {
                            search: 'applied'
                        })
                        .data()
                        .reduce(function (a, b) {
                            return intVal(a) + intVal(b);
                        });
                } else {
                    pageTotal = 0;
                }                 

                // Update footer
                $(api.column(5).footer()).html(                    
                    pageTotal.toFixed(2) + ' hours ( ' + total.toFixed(2) + ' total hours)' + '<br>' + Object.entries(byCategory) + ' hours'
                    //pageTotal.toFixed(2) + ' hours ( ' + total.toFixed(2) + ' total hours)' + '<br>' + Object.keys(byCategory).map(key => { console.log(key, byCategory[key]) }) + ' hours'
                    //pageTotal.toFixed(2) + ' hours ( ' + total.toFixed(2) + ' total hours)' + '<br>' + Object.keys(byCategory).forEach(key => { console.log(key, byCategory[key]) }) + ' hours' 
                );
            }

Here is a link to my jsfiddle: https://jsfiddle.net/l337method/hfyo90w7/

Hi @l337method, i'm not sure how you display that. but this will help you to understand — yash
Oh, awesome. I had not found that example while I was scavenging for information. Thank you. :D — l337method

yash yash · Accepted Answer · 2018-08-24T14:10:21

You Can do sum of salary by office using below code sample as said over here, which you can modify according to your need.
Replace 1 by column number you want to compare data with.

 total = api.cells( function ( index, data, node ) {
                        return api.row( index ).data()[1] === 'textA' ?
                            true : false;
                    }, 0 )
              .data()
              .reduce( function (a, b) {
                  return intVal(a) + intVal(b);
              } );

To be more specific : you can do something like this, you can use this function to sum up values. see @davidkonrad for exactly what you want with filter.

$("#example").on('search.dt', function() {
    alert(table.column( 0, {page:'current'} ).data().sum() );
});

Add Columns And Separate By Category

2 Answers