JS: Calculate column sum in the result page

[caspio]

JavaScript Solution: Calculate the sum of a column values in the result page

Feature Description:

This JavaScript solution shows how to perform the sum of all elements of column in a result page. The result is displayed in a row below the last record.

For example, if the results have 3 records, and the values in a specific column are 12.00, 25.20, and 45.65, the sum (82.85) will be shown in a row below the last record.

Implementation:

This solution can be used "as-is", without any changes if

a. It is used in a Search and Report DataPage

b. The results page has 5 columns

c. It calculates the sum of the items of the 4th column

To use this solution,

a. Highlight to copy the code provided in the text area shown below.

b. Paste the respective snippet inside the HTML Header or HTML Footer section of the Results page of the Search and Report DataPage.

Header:

<div id="cb_resultTotal">

Footer:

</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) 
   {
      var cas_rows = cas_form.getElementsByTagName("table")[1].getElementsByTagName("tr");
      for(var rowIndex=1; rowIndex < cas_rows.length; rowIndex++) 
      {
         var cells = cas_rows[rowIndex].getElementsByTagName("td");

/* 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);
      }
      var v_nrow = cas_rows.length;
      cas_form.getElementsByTagName("table")[1].insertRow(v_nrow); 
      var o_lastRow = cas_form.getElementsByTagName("table")[1].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>

Tips for Customization

Follow these steps to customize this script for different column positions:

a. Change the line of code immediately below comment (1) to:

v_rev = cells[COLUMNNUMBER].innerHTML;

where COLUMNNUMBER is the index of the column you wish to sum. Remember that column indexes start at 0 (ZERO).

b. Change the line of code immediately below comment (2) to:

var v_colText = o_lastRow.insertCell(COLUMNNUMBER-1);

c. Change the line of code immediately below comment (3) to:

var v_colValue = o_lastRow.insertCell(COLUMNNUMBER);

d. Ensure that for every column, except for columns COLUMNNUMBER and COLUMNNUMBER-1, there is the following line of code:

o_lastRow.insertCell(INDEX);

where INDEX is the index of the column. Also, make sure they are in order (i.e. o_lastRow.insertCell(0); is before o_lastRow.insertCell(1); and so on)

Tested Browsers

This JavaScript solution was tested on the following platforms and Internet Browsers only.

• MS Windows - IE 8.0, Firefox 3.5.7, Chrome 3.0.195.38, Safari 4.0.3

• Macintosh - Firefox 3.5.7, Safari 4.0.3

Disclaimer: Use these JavaScript solutions at your own risk! They are provided to assist you in the customization of your programs and while we believe they work properly, by using them you accept all responsibility about them and confirm that they are not a feature of Caspio's platforms and as such are provided "as is" without any warranties, support or guarantees.