summing over row group
summing over row group
ranga
Posts: 31Questions: 12Answers: 2
i need to built an excel look a like invoice using dataatables where users can dynamically add categories and details and i need a row grouping and summation for each group at the end of the last row of the group.
the way i managed to do this was simply using the row grouping and duplicate row feature in editor, for grouping and new record insertions to a specific group. so far its good but i need some guidance over summation of columns for each group in the last row of each group. still have no idea how to achieve it. any guiding you can provide will be very much appreciated. Thank You.
This is the code i have
`var editor;
$(document).ready(function () {
var groupColumn = 2;
editor = new $.fn.dataTable.Editor( {
ajax: "/api/staff",
table: "#example",
fields: [ {
label: "First name:",
name: "first_name"
}, {
label: "Last name:",
name: "last_name"
}, {
label: "Position:",
name: "position"
}, {
label: "Office:",
name: "office"
}, {
label: "Extension:",
name: "extn"
}, {
label: "Start date:",
name: "start_date",
type: "datetime"
}, {
label: "Salary:",
name: "salary"
}
]
} );
var api = this.Api;
var table1 = $('#example').DataTable( {
dom: "Bfrtip",
ajax: "/api/staff",
columns: [
{ data: null, render: function ( data, type, row ) {
// Combine the first and last names into a single table field
return data.first_name+' '+data.last_name;
} },
{ data: "position" },
{ data: "office" },
{ data: "extn" },
{ data: "start_date" },
{ data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }
],
"columnDefs": [
{ "visible": false, "targets": groupColumn }
],
"order": [[groupColumn, 'asc']],
"displayLength": 25,
"drawCallback": function (settings) {
var api = this.api();
var rows = api.rows({ page: 'current' }).nodes();
var last = null;
api.column(groupColumn, { page: 'current' }).data().each(function (group, i) {
if (last !== group) {
$(rows).eq(i).before(
'<tr class="group"><td colspan="5">' + group + '</td></tr>'
);
last = group;
}
});
},
paging :false,
select: true,
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor },
{
extend: "create",
text: "Summation",
action: function (e, dt, node, config) {
// Immediately add `250` to the value of the salary and submit
editor
.edit(table1.row({ selected: true }).index(), false)
.set('salary', (table1.column(5, { page: 'current' }).data().sum()))
.table1.column(5, { page: 'current' }).data().sum()
.submit();
}
},
{
extend: "selected",
text: 'Duplicate',
action: function ( e, dt, node, config ) {
// Start in edit mode, and then change to create
editor
.edit( table1.rows( {selected: true} ).indexes(), {
title: 'Duplicate record',
buttons: 'Create from existing'
} )
.mode( 'create' );
}
},
],
});
} );
jQuery.fn.dataTable.Api.register('sum()', function () {
return this.flatten().reduce(function (a, b) {
if (typeof a === 'string') {
a = a.replace(/[^\d.-]/g, '') * 1;
}
if (typeof b === 'string') {
b = b.replace(/[^\d.-]/g, '') * 1;
}
return a + b;
}, 0);
});
$('#example tbody').on('click', 'tr.group', function () {
var currentOrder = table.order()[0];
if (currentOrder[0] === groupColumn && currentOrder[1] === 'asc') {
table.order([groupColumn, 'desc']).draw();
}
else {
table.order([groupColumn, 'asc']).draw();
}
});`
This question has an accepted answers - jump to answer
Answers
I wasn't aware that duplication of records is that simple. Thank you for that hint! Just found this page with the example:
https://editor.datatables.net/reference/api/mode()
To your question I have a table that is also grouped and I do a summation for each group.
I monitor this table event and call a little function that does the summation:
And the function that does the summation and the formatting. I render my numbers on the server so I first have to deformat them based on the respective language (English: decimal point and comma as thousand separator; German: opposite). Subsequently I add them up and format them again.
My summation field "cashflowAccum" is just a regular table field
but the trick is to have it in the data coming from the server as well even though the field doesn't exist on the server but is always calculated at run time whenever the table is redrawn, e.g. based on user initated reordering etc.
In PHP I use this Editor field definition to have it in the datatable. I am just aliasing another field that I also select with the same editor instance to create this empty pseudo-field.
@rf1234 i just found out there is a plugin for row grouping with summations .
https://datatables.net/extensions/rowgroup/examples/initialisation/customRow.html
is this plugin reliable? cos it doesn't have any updates after 2017 and if im correct ngm has only around 150 downloads. any idea ?
I would assume it is reliable since it is an official data tables extension. I haven't used it because I do the grouping on the server. All I needed was the dynamic calculation of my accumulator. So that is a different use case than yours. I would give the row grouping extension a try!
I attached a screenshot of my use case. The yellow marked column is the accumulator.
Where did you get your stats? Last update was 8 days ago.
npm has the latest release at 9 days ago and 542 downloads from npm last week. The download builder is used more commonly than npm as well.
Allan
@Allan
oops then may be i got the wrong extension i guess .
i got this
sorry about that.
@rf1234
Thanks for your Example. ill try row grouping extension cos thats exactly what im looking for.
That's not a package that we create and distribute I'm afraid.
Allan