Support » Plugin: TablePress - Tables in WordPress made easy » autosum column values with filtering

  • Resolved microtune

    (@microtune)


    Thank you for this great plugin!

    Is there a way to display the sum of all values in a column that automatically adjusts after filtering?

    Right now, I am using the =SUM(B2:B50)formula for column B in the footer, but that formula has to be manually updated when more rows are added and (of course) it still displays the sum of all 50 values after a filtering when fewer rows are displayed.

    Example: column B is an expense column for different projects. If there are 20 rows with a particular project name, I need to have the sum of those 20 column B cells only, not all 50.

    Thank you for your help!

    http://wordpress.org/plugins/tablepress/

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    thanks for your question, and sorry for the trouble.

    No, I’m really sorry, I do not know a solution for this 🙁 The problem simply is that the evaluation of the formula and the filtering are independent from each other, and they basically don’t share common knowledge (about the number of rows for example) 🙁
    Please also see http://wordpress.org/support/topic/sum-to-number_rows-with-filters for a similar problem.
    As the formulas are evaluated before the filter is applied, it just can not know how many rows there are in the filtered table 🙁

    Regards,
    Tobias

    Thread Starter microtune

    (@microtune)

    Thank you for your quick reply!

    I found that what I need can be done with the DataTables footer callback.

    They give an example for the initialisation code:

    $('#tablepress-1').dataTable( {
            "fnFooterCallback": function ( nRow, aaData, iStart, iEnd, aiDisplay ) {
    
                var iTotalMarket = 0;
                for ( var i=0 ; i<aaData.length ; i++ )
                {
                    iTotalMarket += aaData[i][1]*1;
                }
    
                var iPageMarket = 0;
                for ( var i=iStart ; i<iEnd ; i++ )
                {
                    iPageMarket += aaData[ aiDisplay[i] ][1]*1;
                }
    
                var nCells = nRow.getElementsByTagName('th');
                nCells[1].innerHTML = parseFloat(iPageMarket);
            }
        } );

    I just was not able to figure out how to add the ‘fnFooterCallback’ parameter to the existing call for dataTable. But I was able to call from the table page with the above code, and the summation works.

    However, the page gives the following error message:
    “DataTables warning (table id=’tablepress-1′):Cannot reinitialise DataTable.

    I understand the problem, but since I am not familiar with your code, it would take me forever to figure out how to add the function parameter to the existing code.

    If it does not take too much of your timetime, could you please give me some pointers on how to integrate this function into the dataTable code?

    I am using the column filter widgets and the tabletools plugins.

    Thank you so much!

    Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    nice idea! That code basically does the summation in JS, which should work fine for “easy” formulas like this 🙂

    For the integration, you have two possibilities:
    1. You could write a small custom TablePress Extension that adds this to the currently used arguments for the dataTable() call in the JS.
    2. The easier solution would be to pass this as an “Custom Command” to the DataTables JS library. For that, the code for the footer callback has to be written in one line:

    "fnFooterCallback": function ( nRow, aaData, iStart, iEnd, aiDisplay ) { var iTotalMarket = 0; for ( var i=0 ; i<aaData.length ; i++ ) { iTotalMarket += aaData[i][1]*1; } var iPageMarket = 0; for ( var i=iStart ; i<iEnd ; i++ ) { iPageMarket += aaData[ aiDisplay[i] ][1]*1; } var nCells = nRow.getElementsByTagName('th'); nCells[1].innerHTML = parseFloat(iPageMarket); }

    Just paste that into the “Custom Commands” textfield on the “Edit” screen of the table.

    Regards,
    Tobias

    Thread Starter microtune

    (@microtune)

    Hello Tobias,

    Thank you so very much!

    I added the code as a “Custom Command” as you suggested in #2, and it worked like a charm!

    (I had to modify the function itself to behave exactly as I needed, but that is irrelevant, your solution was right on target.)

    Adding “fnFooterCallback” simply to your “Custom Commands” textfield on the “Edit” screen of the table would also work for displaying the number of rows correctly after filtering as the other thread you suggested wanted to have.

    Thank you again!
    microtune

    Plugin Author TobiasBg

    (@tobiasbg)

    Hi,

    no problem, you are very welcome! 🙂 Good to hear that this helped!

    Yes, this is a really powerful solution, except that it doesn’t use the formulas in the table cells, but requires a little bit of JS programming.
    Thanks for finding and sharing this!

    Regards,
    Tobias

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘autosum column values with filtering’ is closed to new replies.