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_nRahul_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>

HierarchTitleYear2015Year2016Year2017Year2018Year2019Total 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

  • Rahul_nRahul_n Posts: 2Questions: 1Answers: 0

    I need two level grouping along with subtotals please help me out i am new at this

  • ThomDThomD Posts: 334Questions: 11Answers: 43

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

This discussion has been closed.