Can anyone please suggest me how to get 2 level grouping I tried using row grouping plugin but no
Can anyone please suggest me how to get 2 level grouping I tried using row grouping plugin but no
Rahul_n
Posts: 2Questions: 1Answers: 0
Here is my code , I need subtotals too row grouping doesn't support that
<link rel="stylesheet" type="text/css" href="http://cdn.datatables.net/1.10.0/css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css"/>
<style type="text/css">
tr.group,
tr.group:hover {
background-color: #ddd !important;
}
.hidden {
display: none;
}
</style>
| Hierarch | Title | Year2015 | Year2016 | Year2017 | Year2018 | Year2019 | Total Expense |
|---|
<
script type="text/javascript">
function formatNumber( iIn ) {
if ( iIn < 1000 ) {
// A small optimisation for what is likely to be the majority of use cases
return iIn;
}
var s=(iIn+""), a=s.split(""), out="", iLen=s.length;
for ( var i=0 ; i<iLen ; i++ ) {
if ( i%3 === 0 && i !== 0 && out[0].toString() !== ".") {
out = "," +out;
}
out = a[iLen-i-1]+out;
}
return out;
}
$(document).ready(function() {
var call = $.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('Demo%20Custom%20List')/items?$select=Level1,Title,Year2015,Year2016,Year2017,Year2018,Year2019,Exp_x0020_Total",
type: "GET",
dataType: "json",
headers: {
Accept: "application/json;odata=verbose"
}
});
call.done(function (data,textStatus, jqXHR){
$('a[data-toggle="tab"]').on( 'shown.bs.tab', function (e) {
$.fn.dataTable.tables( {visible: true, api: true} ).columns.adjust();
} );
var table = $('#example').DataTable({
"aoColumnDefs": [ {
"aTargets": [2,3,4,5,6,7],
"mRender": function ( val, type, all ) {
return ( type === "display" || type === "filter" ) ? "$"+formatNumber(parseFloat(val).toFixed(0)) :val;
}
} ],
"order": [[ 0, 'asc' ]],
"displayLength": 25,
"bLengthChange" : false,
"aaData": data.d.results,
"aoColumns": [
{ "mData": "Level1",
"visible": false
},
{ "mData": "Title" },
{ "mData": "Year2015" },
{ "mData": "Year2016" },
{ "mData": "Year2017" },
{ "mData": "Year2018" },
{ "mData": "Year2019" },
{ "mData": "Exp_x0020_Total" }
],
"drawCallback": function ( settings ) {
var api = this.api();
var rows = api.rows( {page:'current'} ).nodes();
var last=null;
api.column(0, {page:'current'} ).data().each( function ( group, i ) {
if ( last !== group ) {
alert(group);
$(rows).eq( i ).before(
'<tr class="group"><td colspan="1">'+group+'</td><td class="noCount" id=e'+group+'>0</td><td class="noCount" id=f'+group+'>0</td><td class="noCount" id=g'+group+'>0</td><td class="noCount" id=h'+group+'>0</td><td class="noCount" id=i'+group+'>0</td><td class="noCount" id=j'+group+'>0</td></tr>'
);
last = group;
}
val = api.row(api.row($(rows).eq( i )).index()).data(); //current order index
$("#e" + val.Level1).text(parseFloat($("#e" + val.Level1).text()) + parseFloat(val.Year2015));
$("#f" + val.Level1).text(parseFloat($("#f" + val.Level1).text()) + parseFloat(val.Year2016));
$("#g" + val.Level1).text(parseFloat($("#g" + val.Level1).text()) + parseFloat(val.Year2017));
$("#h" + val.Level1).text(parseFloat($("#h" + val.Level1).text()) + parseFloat(val.Year2018));
$("#i" + val.Level1).text(parseFloat($("#i" + val.Level1).text()) + parseFloat(val.Year2019));
$("#j" + val.Level1).text(parseFloat($("#j" + val.Level1).text()) + parseFloat(val.Exp_x0020_Total));
} );
$('tbody').find('.group').each(function (i,v) {
var rowCount = $(this).nextUntil('.group').length;
$(this).find('td:first').append($('<span />', { 'class': 'rowCount-grid' }).append($('<b />', { 'text': ' ('+rowCount+')' })));
});
$("#eCapital").text(formatNumber(parseFloat( $("#eCapital").text()).toFixed(0)));
$("#fCapital").text(formatNumber(parseFloat( $("#fCapital").text()).toFixed(0)));
$("#gCapital").text(formatNumber(parseFloat( $("#gCapital").text()).toFixed(0)));
$("#hCapital").text(formatNumber(parseFloat( $("#hCapital").text()).toFixed(0)));
$("#iCapital").text(formatNumber(parseFloat( $("#iCapital").text()).toFixed(0)));
$("#jCapital").text(formatNumber(parseFloat( $("#jCapital").text()).toFixed(0)));
$("#eExpense").text(formatNumber(parseFloat( $("#eExpense").text()).toFixed(0)));
$("#fExpense").text(formatNumber(parseFloat( $("#fExpense").text()).toFixed(0)));
$("#gExpense").text(formatNumber(parseFloat( $("#gExpense").text()).toFixed(0)));
$("#hExpense").text(formatNumber(parseFloat( $("#hExpense").text()).toFixed(0)));
$("#iExpense").text(formatNumber(parseFloat( $("#iExpense").text()).toFixed(0)));
$("#jExpense").text(formatNumber(parseFloat( $("#jExpense").text()).toFixed(0)));
$("#eCapital").prepend('$');
$("#fCapital").prepend('$');
$("#gCapital").prepend('$');
$("#hCapital").prepend('$');
$("#iCapital").prepend('$');
$("#jCapital").prepend('$');
$("#eExpense").prepend('$');
$("#fExpense").prepend('$');
$("#gExpense").prepend('$');
$("#hExpense").prepend('$');
$("#iExpense").prepend('$');
$("#jExpense").prepend('$');
}
} );
// Order by the grouping
$('#example tbody').on( 'click', 'tr.group', function () {
var rowsCollapse = $(this).nextUntil('.group');
$(rowsCollapse).toggleClass('hidden');
} );
});
call.fail(function (jqXHR,textStatus,errorThrown){
alert("Error retrieving Tasks: " + jqXHR.responseText);
});
} );
</script>
Answers
I need two level grouping along with subtotals please help me out i am new at this
It doesn't use the plugin, but look at this thread on multiple grouping. You should be able to add some running totals to the loop and get the subtotals. The biggest problem is that it puts the titles at the top of the group, but you don't know the total until the bottom. So, you'll need a span in the group header with an ID that you can reference at the end of the group so you can write the subtotal back to that span using jquery's inner text.
http://datatables.net/forums/discussion/comment/82303//