row group with subtotals

row group with subtotals

MadMax76MadMax76 Posts: 149Questions: 33Answers: 1
edited March 2021 in Priority support

Hi,

is it possible to have a table like this:
http://live.datatables.net/migixiqi/327/edit

but with not only the number of rows but also the sum of the salarys; e.g. "Edinburgh (10 people; total salary $ 456.902)"?

Thanks
Max

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Checkout this example.

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    Hi Kevin,

    tried to implement this in my code, but I don't get it running. Here without sums:
    http://freigabe.kontura.at/test/rowgr.html
    Adding the lines from your example stops it from wokring completely:
    http://freigabe.kontura.at/test/rowgr2.html
    Thanks!
    Max

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    You are getting this error in the browser's console:

    rowgr2.html:63 Uncaught TypeError: Cannot read property 'replace' of undefined

    When summing a column using columns.render you will need to use cells().render(). See Colin's example from this thread.

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1
    edited March 2021

    so why are you working on sunday?

    I dot get it, I tried to implement this into
    http://freigabe.kontura.at/test/rowgr2.html

    Is it normal that I can not use a number for the cells, but have ti give the full name - is that because of ajax?

    And an extra question: the consol of my firefox, having errors and warnings to be shown, doesn't show me that error - how do you do that?

    Thanks
    Max

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    You are getting this error:

    Uncaught TypeError: this.api is not a function

    Instead of trying to use this.api() just use the table variable you have. Try this:

                        pageTotal = table
                            .column( "V_Zahllauf_Details.Zahlungsbetrag" )
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );
    

    Is it normal that I can not use a number for the cells, but have ti give the full name - is that because of ajax?

    It was my mistake. I thought you were using columns.render for that column,

    And an extra question: the consol of my firefox, having errors and warnings to be shown, doesn't show me that error - how do you do that?

    Sorry, I don't understand your question.

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    We are very close!!!
    After also adding the "intval", now the example shows a number - but the total of the page, which is my fault as I gave it that name. But I am looking for the total only of a group.
    And the collapsing works for the second level ,not for the (now added) first level of the groups.

    And another (i hope last one for this topic) question: Can I start the table with all rows collapsed (showing only sums?

    Thanks a lot!

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    But I am looking for the total only of a group.

    See if this thread helps.

    And another (i hope last one for this topic) question: Can I start the table with all rows collapsed (showing only sums?

    You can. Start with this thread to see how to expand and collapse rowGroups with a click event. There are some threads that show how to start with all the groups collapsed.

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    maybe it is too late... bnoth does not work...
    http://freigabe.kontura.at/test/rowgr3.html
    And I have another step to go then to implement a second-level-group with the first(0)-column....

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Not sure what you mean both don't work. Please provide specific details of how to see the problems.

    And I have another step to go then to implement a second-level-group with the first(0)-column....

    You will find more threads on this too :-)

    All of these questions have been discussed many times on the forum. Guess someone should write a technote on these subjects :smile:

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    Hi Kevin,

    now in "more" detail:
    example is here:
    http://freigabe.kontura.at/test/rowgr3.html

    3 problems:

    '1. despite having the following code in there it starts with rows expanded.

            // Start with all groups collapsed
            table.column(1).data().unique().each( function ( d, j ) {
                collapsedGroups[d] = !collapsedGroups[d];
            } );
    

    '2. expand/collaps works on 2. level (e.g. 310000), but not on first level (e.g. 0)

    '3. this:
    ```

                    var filteredData = table
                        .rows()
                        .data()
                        .filter( function ( data, index ) {
                            return data[1] == group ? true : false;
                        } )
                        .pluck(5)
                        .sum();
    

    ```
    does always bring a result of Zero

    I do find discussions on rowgroup, but none working example with multiple groups AND subtotals. This here https://datatables.net/forums/discussion/47614/row-grouping-and-its-subtotal-for-multiple-level seems to be based on a out-dated multi-level-grouping, and also is not showing a running example.

    Thanks!
    Max

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    despite having the following code in there it starts with rows expanded.

    Place a debugger breakpoint on line 3 of this code:

    // Start with all groups collapsed
    table.column(1).data().unique().each( function ( d, j ) {
        collapsedGroups[d] = !collapsedGroups[d];
    } );
    

    You will see it doesn't execute. The reason is you are fetching data via ajax (asynchronous process) and the Datataable initialization isn't complete when this code is executed. Move it inside initComplete or the init event.

    expand/collaps works on 2. level (e.g. 310000), but not on first level (e.g. 0)

    You have this code to

                        } else {
                            // if parent collapsed, nothing to do
                            if (!!collapsedGroups[top]) {
                                return;
                            }
                            all = top + group;
                        }
    
                        var collapsed = !!collapsedGroups[all];
    

    I think this is from one of the examples but the code has !! which is not not so it doesn't seem to be making any changes. Start by placing a debugger breakpoint on this line (line 115 in your example):

                        rows.nodes().each(function(r) {
    

    This will allow you to debug the values of the collapsedGroups object and the collapsed variable. My guess is you need to replace the !! with one !.

    does always bring a result of Zero

    You are using .pluck(5) but are using object based data. You will want to use `.pluck('V_Zahllauf_Details.Zahlungsbetrag') instead.

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1
    edited March 2021

    Hi Kevin,

    thanks for your patience.

    I put the first part into "initcomplete", now it fires, but still does not work.

    In the second code I removed all !!, which makes no change. having only one ! "looks good", but: it removes the second level and when clcking first, time, all OTHER groups are expanded, completely wrong way....

    and for the pluck I tried
    'V_Zahllauf_Details.Zahlungsbetrag'
    "V_Zahllauf_Details.Zahlungsbetrag"
    'Zahlungsbetrag'
    "Zahlungsbetrag"
    none works.

    All changes were made in
    http://freigabe.kontura.at/test/rowgr3.html

    Thanks,
    Max

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Have you tried debugging the collapsedGroups variable to see what is happening? There is nothing specific to Datatables about that code. Its just a matter of getting collapsedGroups to properly track the state of the rowGroup.

    and for the pluck I tried

    ITs hard to debug this without access to the code to make changes. I would start by removing .sum() and see what filteredData contains. Make sure the proper rows are returned for each group. Maybe nothing is returned. Or maybe you need to do some debugging of the sum() plugin. Looks like the values are strings, maybe the sum() plugin isn't handling it correctly.

    If you want to build a simple test case with a sample of your data (not 171 rows but just a few) we can do more to help with debugging since we can work with the code.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    Sorry, I cant provide a test case. I tried to start with some example, as that seems easiest way to do that. I do not think you can have ajax-sources there (at least I haven't seen one up to now), so I chose this one:

    http://live.datatables.net/kuruvipi/77/edit

    which works perfectly apart from not having sub-totals (or totals)

    as soon as I put this into it:

                        var intVal = function ( i ) {
                            return typeof i === 'string' ?
                                i.replace(/[\$,]/g, '') * 1 :
                                typeof i === 'number' ?
                                    i : 0;
                        };
                       sumzahl = table
                            .column( 4 )   // or 'order_id' instead of 4
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );
    

    the datatable stops working. SAme with

            initComplete: function( settings, json ) {
                    // Start with all groups collapsed
                    table.column(0).data().unique().each( function ( d, j ) {
                        collapsedGroups[d] = !collapsedGroups[d];
                    } );
                    table.column(1).data().unique().each( function ( d, j ) {
                        collapsedGroups[d] = !collapsedGroups[d];
                    } );
                    },
    
    

    ....also kills it.

    best regards,
    Max

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited March 2021

    Thanks for the test case! It was missing the HTML tab so I copied it from a previous version:
    http://live.datatables.net/kuruvipi/79/edit

    The table variable is not available, ie, doesn't have the Datatables API, until after the Datatable has initialized. In initComplete you can get an instance of the API using this.api(). In RowGroup you can get is using $('#table_orders').DataTable().

    In initComplete you will need to call draw() to redraw the table with the collapsedGroups that is built. Also change column(4) in rowGroup.startRender to get the last column.

    HTH,
    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    Your working time is worse then mine!!!! sunday, midnight,....

    Thanks for your effort, but only level 1- group is hidden (acutally the details are there, only the middle-level is "missing".
    Also the sum is the same in every sub-total - I guess it is the sum of the complete table.

    Thank you, Max

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    This solves the problem with the group count: http://live.datatables.net/gudaquvi/1/edit .

    I wasn't clear about the "level-1" group, could you explain that please.

    Colin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    great for the sums!!!!

    All rows should be collapsed from start; So i get to see only the two lines "Sonya Frost" and "Tiger Nixon" with their total counts/sums.
    But i see row-groups for webmaster ("Sonya Frost"), the arcdate ("2020-06-19") and the details - only the "A-plus"(offer-name) is hidden until I click "Sonya Frost" one.
    Also when clicking on "Sonya Frost", only the offer_name-subtotal vanishes, but the other details stay there. In opposite when clicking "Royal Queen" it does what I think it should to - all sub-groups and details vanish

    Thanks,
    Max

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    The key to this is how collapsed groups is built. It relies on combining the group names per level to build the key to access the proper collapsedGroup object. I changed the code in initComplete to iterate all rows to build the collapsed groups. Added a couple console.log statements so you can see what is happening.

    http://live.datatables.net/hijiholo/1/edit

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    Hi Kevin,

    YES your example is exactly what I need!!!! Great to see that is possible.
    I tried to adapt the code for my needs with ajax-datasource, sonehow this again does not work .I guesss the reason is you call the data like this:

    var data = [{"webmaster":"Tiger Nixon","offer_name":"Power Bank Solar","arcdate":"2020-06-19","order_id":1358639},{"webmaster":"Tiger Nixon","offer_name":"GERBER Bear Grylls","arcdate":"2020-06-19","order_id":1358587},{"webmaster":"Tiger Nixon","offer_name":"GERBER Bear Grylls","arcdate":"2020-06-19","order_id":1358765},{"webmaster":"Tiger Nixon","offer_name":"GERBER Bear Grylls","arcdate":"2020-06-20","order_id":1358910},{"webmaster":"Tiger Nixon","offer_name":"GERBER Bear Grylls","arcdate":"2020-06-20","order_id":1358911},{"webmaster":"Tiger Nixon","offer_name":"GERBER Bear Grylls","arcdate":"2020-06-20","order_id":1359035},{"webmaster":"Tiger Nixon","offer_name":"GERBER Bear Grylls","arcdate":"2020-06-20","order_id":1359131},{"webmaster":"Tiger Nixon","offer_name":"GERBER Bear Grylls","arcdate":"2020-06-20","order_id":1359194},{"webmaster":"Tiger Nixon","offer_name":"Туристический гамак","arcdate":"2020-06-19","order_id":1358762},{"webmaster":"Sonya Frost","offer_name":"A-Plus 4","arcdate":"2020-06-19","order_id":1358666},{"webmaster":"Sonya Frost","offer_name":"Royal Queen","arcdate":"2020-06-19","order_id":1358589},{"webmaster":"Sonya Frost","offer_name":"Royal Queen","arcdate":"2020-06-19","order_id":1358622},{"webmaster":"Sonya Frost","offer_name":"Royal Queen","arcdate":"2020-06-19","order_id":1358708},{"webmaster":"Sonya Frost","offer_name":"Royal Queen","arcdate":"2020-06-19","order_id":1358713},{"webmaster":"Sonya Frost","offer_name":"Royal Queen","arcdate":"2020-06-19","order_id":1358797}];
    
    

    whilst I use the ajay inside the datatable.

            var table = $('#table_orders').DataTable({
                ajax: "../../../../../../../DataTables/Editor-PHP-1.9.6/controllersKontura/Zahllauf_test.php",
     
    

    So my hopefully really last question: how do i get this into the data-variable?

    Thanks
    Max

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    The data source doesn't matter. You don't need to use a data-variable. We moved the code into initComplete to run after the Datatables is initialized due to the asynchronous Ajax request.

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    i am VERY sorry, I stil ldont get it to work:

    http://freigabe.kontura.at/test/rowgr2.html

    again no sums and not collapsed.... As far as I can say it is a exact copy of the code in
    http://live.datatables.net/hijiholo/1/edit
    just data-source and column-names changed...

    Good night,
    max

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited March 2021 Answer ✓

    The console output shows this for the rowGroup dataSrc:

    (3) ["V_Zahllauf_Details.ZVK_KONTOZVSPERRE", "V_Zahllauf_Details.ZVK_BUC_KONTONR", "V_Zahllauf_Details.ZVK_FREIGABE_NEIN_GRUND"]
    

    It builds the collapsedGroup that looks like this which is not correct:

    {undefined: true, undefinedundefined: true, undefinedundefinedundefined: true}
    0: false
    0310000: false
    undefined: true
    undefinedundefined: true
    undefinedundefinedundefined: true
    

    The loop building this looks like this:

    for (i=0; i<dataSrc.length; i++) {
         key += data[dataSrc[i]];
          collapsedGroups[key] = true;
    }
    

    Basically it looks like this key += data["V_Zahllauf_Details.ZVK_KONTOZVSPERRE"]; which won't work. You will need to split the key at the period.

    See if this works, I didn't test it so you may need to make adjustments:

    for (i=0; i<dataSrc.length; i++) {
        var splitKey = dataSrc[i].split('.');
        key += data[ splitKey[0] ][ splitKey[1] ];
        collapsedGroups[key] = true;
    }
    
    

    Kevin

  • MadMax76MadMax76 Posts: 149Questions: 33Answers: 1

    good morning!!!!

    WE HAVE DONE IT!!!!

    I changed the datasource and eliminated the "V_Zahllauf_Details." - part, now it works!!!!

    Thanks a lot for your never-ending patience!

    THANK YOU
    Max

This discussion has been closed.