Jump to content

How To Calculate Column Sum In Tabular Reports With Grouping Enabled

Recommended Posts

This old post from Caspio should be updated with the following changes so that it works when Grouping is enabled:



Aggregation should be used instead, but if you want to SUM a Calculated Field which in turn is based on a sub-query, it will not be available for Aggregation. See the second paragraph here for details on this case http://howto.caspio.com/release-notes/caspio-bridge-9-0/9-0-known-issues/



I've highlighted the changes below


function NumberFormatted(amount,decimal) 
      i = 0.00;
      var v_number = parseFloat(amount);
      var v_minus = '';
      if(v_number < 0)
         v_minus = '-';
      v_number = Math.abs(v_number);
      v_number = Math.round(v_number*Math.pow(10,decimal));
      v_number = v_number/Math.pow(10,decimal);
      v_numStr = new String(v_number);
      v_decStr = new String(Math.pow(10,decimal));
      if(v_numStr.indexOf(".") < 0) 
         v_numStr = v_numStr + "." + v_decStr.substr(1,v_decStr.length);
         v_numStr = v_numStr + v_decStr.substr(1,v_decStr.length);
      return (v_minus + v_numStr.substr(0,v_numStr.indexOf(".") + decimal + 1));
function f_calTotal() 
   var v_totalRev = 0;
   var v_rev = 0;
   var cas_form = document.getElementById("cb_resultTotal");
   if (cas_form.getElementsByTagName("table").length > 0) 
//For counting all rows, so that we can use the count as the index for inserting a new row.
var countRows = cas_form.getElementsByTagName("table")[2].getElementsByTagName("tr");
//For reading just the data rows, not the Grouping rows, to avoid null values.
      var cas_rows = cas_form.getElementsByTagName("table")[2].querySelectorAll('tr[data-cb-name=data]');
      for(var rowIndex=1; rowIndex < cas_rows.length; rowIndex++) 
//Narrows down to just data cells.
         var cells = cas_rows[rowIndex].querySelectorAll(".cbResultSetData");
/* 1 – change value inside brackets to choose column to calculate sum */
         v_rev = cells[3].innerHTML;
         if ( v_rev != " " && !isNaN(v_rev.substr(1)))
            v_totalRev = v_totalRev + parseFloat(v_rev);
//New row will be based on count of all rows, not count of rows searched.
      var v_nrow = countRows.length;
      var o_lastRow = cas_form.getElementsByTagName("table")[2].rows[v_nrow];
      o_lastRow.style.background = "#385C7E";
/* 2 – Display the “Total†label (2 lines below) */
      var v_colText = o_lastRow.insertCell(2);
      v_colText.innerHTML = "<div style='padding:5px;color:#ffffff;font-size:14px;font-weight:bold;font-family:Arial'>Total</div>";
      var v_colValue = o_lastRow.insertCell(3);
/* 3 – Display the result of the calculation (2 lines below) */
      v_colValue.innerHTML = "<div style='padding:5px;color:#ffffff;font-size:14px;font-weight:bold;;font-family:Arial'>$" + NumberFormatted(v_totalRev,2) + "</div>";
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...