Add Columns By Category

enorthrop

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? Ideally I wouldn't have to hard code each category as I am sure they will be added and removed over time. I am not sure how to make the category dynamic, however.

Here is my script:

"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;
                };
                
                // ************NOT WORKING************ \\   
                // Total by office (kind of working, no filtered total)
                byOffice = api
                    .cells(function (index, data, node)
                    {
                        return api
                            .row(index)
                            .data()[2] === 'Tokyo' ?
                            true : false;
                    }, 5)
                    .data()
                    .reduce(function (a, b)
                    {
                        return intVal(a) + intVal(b);
                    });                
                // ************NOT WORKING************ \\

                // 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;
                }

                $(api.column(5).footer()).html(
                    pageTotal.toFixed(2)
                );

                // Update footer
                $(api.column(5).footer()).html(
                    '$' + pageTotal.toFixed(2) + ' filtered total ($' + total.toFixed(2) + ' total salary)' + '<br>' + '$' + byOffice + ' Tokyo total'
                );

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

Thank you very much for any help. I am struggling with this problem.

colin

Hi @enorthrop ,

Thanks for that, that was a nice challenge to start the day - though don't tell the boss...

See your updated fiddle here. You need to format the numbers, but I wanted to leave you something to do

Cheers,

Colin

enorthrop

Thank you very much! I've been struggling with this for a while now.

enorthrop

My last part that I need to figure out is how to remove any offices that have a "0" value, but I can spend some time figuring that out. Thank you for your help.