Hitachi Vantara Pentaho Community Forums
Results 1 to 14 of 14

Thread: Table Component - Grand total

  1. #1
    Join Date
    Jun 2011
    Posts
    18

    Question Table Component - Grand total

    Hi,
    I have a table component in a dashboard. I couldn't find a way to automatically generate the grand total for a numeric column for example.
    This could be displayed at the top (as the first or last row, bold).
    Any idea other than calculating it in the query itself ?

  2. #2
    Join Date
    Dec 2010
    Posts
    304

    Post

    Hi,
    I build the grand total row in the draw function of the table component, for example:

    Code:
    function() {
        var grandTotalRow = "<tfoot><tr><td>Total</td>";
        
        for(i=1;i<numCols;i++) { // replace numCols with the number of columns in your table
            var total=0;
            
            var rows = $('#' + this.htmlObject + ' tbody tr');
            rows.each(function() {
                var cellVal = parseFloat($('td:eq('+i+')', this).text().replace(',',''));
                if(!isNaN(cellVal) {
                    total += cellVal;
                }
            });
                    
            grandTotalRow += "<td>"+total+"</td>";
        }
        
        grandTotalRow += "</tr></tfoot>";
    
        if($('#'+this.htmlObject+' tfoot').length==0)
            $('#'+this.htmlObject).find('table').append(totalRowHtml);
    }
    Probably it's not the best performant implementation, but it's a solution...

  3. #3

    Default

    I ran with lukolap's sample and extended it to the entire dataset. Also included class names for easy css formatting.

    The main and helper functions are part of a reference jscript file. I'm going to be re-using this one.

    Draw Function
    Code:
    function() {
        // Array of size: table columns - 1
        // 1 - calculate sum, 0 - empty footer
        var calcSumFlag=[0,1,1,1,1,1,1,1,1,0]; 
    
        // Write footer, first column value will be "Total"
        TableTotal(this,"Total",calcSumFlag);
    }
    Main Function
    Code:
    function TableTotal(tblObj,totalStr,calcSumFlags) {
        if($('#'+tblObj.htmlObject+' tfoot').length==0){
            var grandTotalRow = "<tfoot><tr><td class=\"column0 string\">" + totalStr + "</td>";
    
            for ( var c=1; c <= calcSumFlags.length; c++ ){
                grandTotalRow += "<td class=\"column" + c + " numeric\">";
                sumVal = 0;
                if(calcSumFlags[c-1] == 1){
                    for( var i=0; i < tblObj.rawData.resultset.length; i++){
                        if (isNumber(tblObj.rawData.resultset[i][c])){
                            sumVal += tblObj.rawData.resultset[i][c];
                        }
                    }
                    grandTotalRow += addCommas(Math.round(sumVal));
                }
                grandTotalRow += "</td>";
            }
            
            grandTotalRow += "</tr></tfoot>";
            $('#'+tblObj.htmlObject).find('table').append(grandTotalRow);
        }
    }
    Helper functions
    Code:
    function isNumber(n) {
      return !isNaN(parseFloat(n)) && isFinite(n);
    }
    function addCommas(nStr)
    {
       nStr += '';
       x = nStr.split('.');
       x1 = x[0];
       x2 = x.length > 1 ? '.' + x[1] : '';
       var rgx = /(\d+)(\d{3})/;
       while (rgx.test(x1)) {
          x1 = x1.replace(rgx, '$1' + ',' + '$2');
       }
       return x1 + x2;
    }
    Some Useful CSS
    Code:
    .myTable tfoot td{
        background-color:#FFF;
        font-weight:bold;
        font-style:normal;
        border-top:solid #000 2px;
    }
    
    .myTable table,
    .myTable table th
    {
       text-align:left;
    }
    
    
    .myTable table .numeric,
    .myTable table th .numeric{
       text-align:right;
    }
    Last edited by hi5; 05-02-2012 at 04:14 PM.

  4. #4

    Default

    This thread and this one: http://forums.pentaho.com/showthread...nent-clickable
    are the two very useful source of information for making your dashboard tables do more for the users.

  5. #5
    Join Date
    Mar 2010
    Posts
    183

    Default

    Hi guys,
    I tried the function posted by lukolap, and it works....
    but I have a problem in the total count, when the table is shown rows by rows..

    i.e. if I visualize rows ten by ten the total computed is only on first ten rows shown in the table, but not the total of all rows of my table component.

    How I can solve this situation?

    THANKS

  6. #6

    Default

    Quote Originally Posted by hi5 View Post
    I ran with lukolap's sample and extended it to the entire dataset. Also included class names for easy css formatting.

    The main and helper functions are part of a reference jscript file. I'm going to be re-using this one.

    Draw Function
    Code:
    function() {
        // Array of size: table columns - 1
        // 1 - calculate sum, 0 - empty footer
        var calcSumFlag=[0,1,1,1,1,1,1,1,1,0]; 
    
        // Write footer, first column value will be "Total"
        TableTotal(this,"Total",calcSumFlag);
    }
    Main Function
    Code:
    function TableTotal(tblObj,totalStr,calcSumFlags) {
        if($('#'+tblObj.htmlObject+' tfoot').length==0){
            var grandTotalRow = "<tfoot><tr><td class=\"column0 string\">" + totalStr + "</td>";
    
            for ( var c=1; c <= calcSumFlags.length; c++ ){
                grandTotalRow += "<td class=\"column" + c + " numeric\">";
                sumVal = 0;
                if(calcSumFlags[c-1] == 1){
                    for( var i=0; i < tblObj.rawData.resultset.length; i++){
                        if (isNumber(tblObj.rawData.resultset[i][c])){
                            sumVal += tblObj.rawData.resultset[i][c];
                        }
                    }
                    grandTotalRow += addCommas(Math.round(sumVal));
                }
                grandTotalRow += "</td>";
            }
            
            grandTotalRow += "</tr></tfoot>";
            $('#'+tblObj.htmlObject).find('table').append(grandTotalRow);
        }
    }
    Helper functions
    Code:
    function isNumber(n) {
      return !isNaN(parseFloat(n)) && isFinite(n);
    }
    function addCommas(nStr)
    {
       nStr += '';
       x = nStr.split('.');
       x1 = x[0];
       x2 = x.length > 1 ? '.' + x[1] : '';
       var rgx = /(\d+)(\d{3})/;
       while (rgx.test(x1)) {
          x1 = x1.replace(rgx, '$1' + ',' + '$2');
       }
       return x1 + x2;
    }
    Some Useful CSS
    Code:
    .myTable tfoot td{
        background-color:#FFF;
        font-weight:bold;
        font-style:normal;
        border-top:solid #000 2px;
    }
    
    .myTable table,
    .myTable table th
    {
       text-align:left;
    }
    
    
    .myTable table .numeric,
    .myTable table th .numeric{
       text-align:right;
    }
    Thanks a lot! works great!

  7. #7
    Join Date
    Feb 2013
    Posts
    530

    Default

    How to group by rows aggregations ?

    Name:  1.png
Views: 1316
Size:  14.8 KB

    As show in figure, how to get the summation values after grouping the data ?

    It's urgent for me.. I hope I am clear in thread.

    Thank you.

    Sadakar
    BI developer

  8. #8
    Join Date
    Feb 2013
    Posts
    530

    Default

    Had found this thread http://stackoverflow.com/questions/1...-footer-header
    but unable to get it work ...

  9. #9
    Join Date
    Jan 2014
    Posts
    189

    Default

    You can write some code in postFetch that adds a row. For instance, for grouping on column 0 and yielding a grand total on the 3rd column:

    function f(data){

    var groupedStates = _.groupBy(data.resultset, function(row){return row[0];});
    var resultset = [];

    _.each(data.groupedStates, function(groupedState){
    var sum = _.reduce(groupedState, function(memo, row){ return memo + row[2]; }, 0);
    groupedState.push(["column 0", "column 1", sum]);
    _.each(groupedState, function(row){ resultset.push(row); });
    });
    data.resultset = resultset;
    data.queryInfo.totalRows = resultset.lenght;
    return data;
    }

    I did not test this code, but you should be able to get the idea...

  10. #10
    Join Date
    Feb 2013
    Posts
    530

    Default

    This is not returning any out put.

  11. #11
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi lukolap

    NaN messge is displaying for non string columns.. How to eliminate it... in Current version of CDE the below code snippet is not working.. & if I write it, it is giving error symbols on the editor console.

    if(!isNaN(cellVal) {
    total += cellVal;
    }


    Name:  issue.png
Views: 499
Size:  2.3 KB





    Quote Originally Posted by lukolap View Post
    Hi,
    I build the grand total row in the draw function of the table component, for example:

    Code:
    function() {
        var grandTotalRow = "<tfoot><tr><td>Total</td>";
        
        for(i=1;i<numCols;i++) { // replace numCols with the number of columns in your table
            var total=0;
            
            var rows = $('#' + this.htmlObject + ' tbody tr');
            rows.each(function() {
                var cellVal = parseFloat($('td:eq('+i+')', this).text().replace(',',''));
                if(!isNaN(cellVal) {
                    total += cellVal;
                }
            });
                    
            grandTotalRow += "<td>"+total+"</td>";
        }
        
        grandTotalRow += "</tr></tfoot>";
    
        if($('#'+this.htmlObject+' tfoot').length==0)
            $('#'+this.htmlObject).find('table').append(totalRowHtml);
    }
    Probably it's not the best performant implementation, but it's a solution...

  12. #12
    Join Date
    Jan 2014
    Posts
    189

    Default

    sadakar:

    The snippet was meant only to give an idea, to help you understand how a certain concept can be implemented.
    In this case, the data is grouped by column 0 (first column) and the sum occurs on column 2 (third column)

    The following postFetch *could* work, but may also not work:

    function f(data){

    var groupedStates = _.groupBy(data.resultset, function(row){return row[0];});
    var resultset = [];

    _.each(groupedStates, function(rows, key){
    var sum = _.reduce(rows, function(memo, row){ return memo + row[2]; }, 0);
    rows.push(["column 0", "column 1", sum]);
    _.each(rows, function(row){
    resultset.push(row);
    });
    });
    data.resultset = resultset;
    data.queryInfo.totalRows = resultset.lenght;
    return data;
    }
    Last edited by crusso; 07-02-2014 at 07:52 AM. Reason: typos

  13. #13
    Join Date
    Mar 2012
    Posts
    276

    Default

    Hi all ,


    I cannot get the running sum for the table . This shows the total for the first set/page in the table .
    When i navigate to next page in the table , This still shows the same old(first page) total. Can some one give me inputs on this .


    Thanks,
    Surya

  14. #14
    Join Date
    Mar 2012
    Posts
    276

    Default

    Hi All,

    Do any one implemented the same functunolity as below .http://datatables.net/examples/advan..._callback.html

    Can some one help to know the trick.


    Thanks,
    Surya Thanuri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.