# 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:

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

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>
##### Share on other sites

Hi,

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

Read more here in  the "REPORTING FEATURES" section

##### Share on other sites

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

## 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.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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