How To Calculate Column Sum In Tabular Reports With Grouping Enabled

[aam82]

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

http://forums.caspio.com/index.php/topic/3171-js-calculate-column-sum-in-the-result-page/

 

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

 

</div>

<script>

 

function NumberFormatted(amount,decimal) 

{

   if(isNaN(amount))

      i = 0.00;

   else

   {

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

      else

         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;

      cas_form.getElementsByTagName("table")[2].insertRow(v_nrow); 

      var o_lastRow = cas_form.getElementsByTagName("table")[2].rows[v_nrow];

      o_lastRow.style.background = "#385C7E";

      o_lastRow.insertCell(0);

      o_lastRow.insertCell(1);

 

 

/* 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>";

 

      o_lastRow.insertCell(4);

   }

}

</script>

 

<script>

f_calTotal();

</script>

[svitlanataran]

Hi,

 

You can use calculated fields in aggregations, it was fixed in 9.1 release.

Read more here in  the "REPORTING FEATURES" section

[aam82]

haha, right you are. I only worked on that for *cough* hours.