RowGroup custom rendering and data source change event combined?

RowGroup custom rendering and data source change event combined?

thowithowi Posts: 64Questions: 7Answers: 0

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!

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 3,280Questions: 0Answers: 584

    Hi @thowi ,

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

    Cheers,

    Colin

  • thowithowi Posts: 64Questions: 7Answers: 0
    edited November 2018

    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();
            }
        } );
    
  • colincolin Posts: 3,280Questions: 0Answers: 584

    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

  • thowithowi Posts: 64Questions: 7Answers: 0

    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/

  • colincolin Posts: 3,280Questions: 0Answers: 584

    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

  • thowithowi Posts: 64Questions: 7Answers: 0

    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?

  • colincolin Posts: 3,280Questions: 0Answers: 584

    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

  • thowithowi Posts: 64Questions: 7Answers: 0

    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?

  • colincolin Posts: 3,280Questions: 0Answers: 584
    edited November 2018

    Hi @thowi ,

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

    Cheers,

    Colin

  • thowithowi Posts: 64Questions: 7Answers: 0
    edited November 2018

    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!

  • colincolin Posts: 3,280Questions: 0Answers: 584

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

  • thowithowi Posts: 64Questions: 7Answers: 0

    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 :)

  • colincolin Posts: 3,280Questions: 0Answers: 584
    Answer ✓

    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

  • thowithowi Posts: 64Questions: 7Answers: 0
    edited November 2018

    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 :wink:

    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! :D Awesome support. Enjoy your weekend!

  • thowithowi Posts: 64Questions: 7Answers: 0

    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?

  • colincolin Posts: 3,280Questions: 0Answers: 584

    Hi @thowi ,

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

    Cheers,

    Colin

  • thowithowi Posts: 64Questions: 7Answers: 0

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

Sign In or Register to comment.