row group with subtotals

MadMax76 · March 2021

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

kthorngren · March 2021

Checkout this example.

Kevin

MadMax76 · March 2021

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

kthorngren · March 2021

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

MadMax76 · 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

kthorngren · March 2021

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

MadMax76 · March 2021

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!

kthorngren · March 2021

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

MadMax76 · March 2021

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

kthorngren · March 2021

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

Kevin

MadMax76 · March 2021

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

kthorngren · March 2021

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

MadMax76 · 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

kthorngren · March 2021

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

MadMax76 · March 2021

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

kthorngren · 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

MadMax76 · March 2021

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

colin · March 2021

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

MadMax76 · March 2021

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

kthorngren · March 2021

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

MadMax76 · March 2021

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

kthorngren · March 2021

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

MadMax76 · March 2021

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

kthorngren · March 2021

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

MadMax76 · March 2021

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

row group with subtotals

row group with subtotals

Answers

Howdy, Stranger!

Categories

DataTables

row group with subtotals

row group with subtotals

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables