RowGroup custom rendering and data source change event combined?

thowi

Hi all together,

I combined the custom rendering and the Data source change event examples from this page: https://datatables.net/extensions/rowgroup/examples/

Instead of average salary in the custom rendering example, I used the sum() function to have the sum of salary for each city.

I now need to have a sort option which will sort the groups using the summed up value. When I now click on the last column the single elements in each city will be ordered, but I need the whole groups ordered using the summed up values.

So, e.g. London is the city with the highest over all salaries, the "London group" should be up, next the second highest salary group and so on.

This is my current script: https://pastebin.com/KkwYcmNG

Hope you can help me out here! I tried to built an example in jsfiddle, but I wasn't able to get the custom rendering example running on jsfiddle. If you have a jsfiddle link with this example running I am happy to add all further details of my code.

Thank you so much!

colin

Hi @thowi ,

This example here should get you going - it's along the lines of what you're after,

Cheers,

Colin

thowi

Hi @colin

Just checked that example and ordered the column "Salary" descending. This is the order I am seeing:
1. London $1,200,000
2. San Francisco $850,000
3. New York $2,045,075 ($725,000 + $675,000 + $645,750)

So New York should be at position 1 and not at position 3, because the whole group salary amount is larger than London.

I added the following code block and it will only sort inside each group, but won't sort the whole groups

// Order by the grouping
    $('#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();
        }
    } );

colin

Hi @thowi ,

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

thowi

Sorry, of course! I didn't get it doing with jsfiddle, but live.datatables worked fine!
This is the link: http://live.datatables.net/surifeda/1/

colin

Hi @thowi ,

Thanks for that fiddle, that helped understand what you're doing. I did try it, but it looks like it's doing what I would expect. Could you give steps please on how to reproduce the problem, also saying what you would expect it to do,

Cheers,

Colin

thowi

Hi @colin,

sure! In my example you can see 3 groups:
* Art1
* Art2
* Art5
When I now click on the column title "Brutto Umsatz" the values inside the groups are being sorted, but the groups themselves stay in their order.

I now would like to change the order of the whole group. In my example, the group with the highest summed value is Art5 with 237,80 €. So when I click on the column title "Brutto Umsatz" this whole group should be at the first position.

So I don't want to sort the single items in each group, but I want to sort the whole groups.

Do you know what I mean?

colin

Hi @thowi ,

Yep, I understand, thanks for the explanation.

I think the only way you could do it is with orthogonal data. You'll need to do a couple of things:

1. remove orderFixed as that keeps the primary order on the first column,
2. and use columns.render. When type is sort, for those columns with totals, don't return the cell data but the total for the group. This will keep the grouping but allow ordering. If the data is static, you could calculate those values in initComplete, and just return a looked up value to make it faster.

It requires a bit of coding, but should be possible. Shout if it's not working,

Cheers,

Colin

thowi

Hi @colin
Thank you for your comment! I think this is a bit over my not available javascript / datatables knowledge
Do you know someone who would implement that into my above linked project - paid? I hope it won't take too long when there is someone who is very experienced with datatables... what do you think?

colin

Hi @thowi ,

How about something like this - I think this is what you're after. I made it my morning project

Cheers,

Colin

thowi

Hi @colin ,

wooooow, das looks amazing! That's exactly the way I thought about it. Thank you so much for your help
I will right set that into my project and check it out.
Just one thing: the collapse function (and start collapsed) doesn't work anymore in your version.
The section below // Collapse Groups is in your example, but it doesn't to anything.
Does the new render function block that feature?

Also found a small bug: When using one of the 3 blue sort links, like "Marktplatz" and then changing back to "Artikelnummer" the sort doesn't work anymore. But that's no big deal, page refresh is also a good solution for me.

Thank you again for your awesome help!

colin

I'll look at the collapse thing in a tick, just rushing out on a school run, I forgot about those links. I'd disabled the searching on those two columns as it would break the grouping - is that OK, or would you prefer those columns to still be searchable? If so, I'll think about ways of tidying it up...

thowi

Sure - take your time and have a good run

Nope, the search is only needed for the first column "Art-Nr" and this works perfectly! All other columns are not needed for search. But sorting of column "Markt" would be very nice. This column search is currently disabled with the line {orderable: false},.

Enjoy your run and take your time - no need to hurry

colin

Ah, school run - just taking the kids to school - though was we're always late, it does turn into a proper run...

I think this address all those remaining points. The orderFixed was interfering, so I had to disable it, but hopefully that'll give you the functionality you want,

Cheers,

Colin

thowi

Ah, I thought that's a kind of "event" where you have to solve your next marathon... But being a bit late is always a good way to sneak in a bit of sports - I heard that it's ok then to plump on the sofa afterwards

Wow, your code looks amazing - and yes, functionality is fully there, wow! Thank you so much, so saved my day

When starting the script the tabled is sorted by the first column "Art-Nr" ascending. When opening the site I'd like to have the order by the last column descending.

So I checked the order() documentation and changed the last command in the initComplete section to example.order( [ 4, 'desc' ] ).draw(false);
That seems to do the job!

Thank you again so much! Awesome support. Enjoy your weekend!

thowi

Hi @colin ,

I just tried to enable the number format for the single rows - currently the number format only works for the groups. There I used two $.fn.dataTable.render.number functions to format my values.

I think the single values are created here, right?

columns: [
      null,
      null,
      null,
      {render: function(data, type, row, meta) {
        return (type !== 'sort' || qtys[row[groupColumn]] === undefined)? data : qtys[row[groupColumn]].split(' ')[0];
      }},
      {render: function(data, type, row, meta) {
        return (type !== 'sort' || salesGroupSums[row[groupColumn]] === undefined)? data : salesGroupSums[row[groupColumn]].split(' ')[0];
      }},
    ],

Where do I need to wrap the $.fn.dataTable.render.number( '.', ',', 2, '', ' €' ).display( ........ ) around to have my formatting also for the single lines?

colin

Hi @thowi ,

Around the data on lines 6 and 9, as in this example here,

Cheers,

Colin

thowi

Hi @colin ,
aaah, thank you! The brackets were my mistake, that's why it didn't work here.
Awesome! Now it's perfect

thowi

Hi all together,

I just noticed one slight problem in the above example: http://live.datatables.net/halecasu/1/edit

When you try to sort the table by column "Anzahl", the sort is not correct. I assume it is being sorted as a "string" (formatted value) but the sort itself should work by the raw data to have the numbers in correct order?

Do you know any way how I could achieve that?

Thank you for you help!

Cheers,
thowi

colin

Hi @thowi ,

Yep, you would use orthogonal data as you are already. In your columns.render you would add a check for sort, currently you're explicitly checking for type !== sort,

Hope that helps,

Cheers,

Colin

thowi

Hi @colin

that's a good hint, thanks! I am not sure why there is this check for type !== sort being added in the first place.
So just removing the section type !== 'sort' || seems to solve the problem! Does that make sense?

Cheers,
thowi

colin

Hi @thowi ,

It's Friday evening, let's say it makes perfect sense

The only downside of just removing it, is that the search doesn't take into account the "Stk", which may or may not be an issue.

Cheers,

Colin

thowi

Hi @colin

good to know thank you again for your help and enjoy your weekend!

Cheers,
thowi