Summary Reports

[casamurphy]

Is there any way to create a report which sums the values from specific fields in multiple records?

[Guest]

Is there any way to create a report which sums the values from specific fields in multiple records?

This is not a standard feature of Caspio Bridge. However, you can use Javascript to accomplish this. Copy and paste code below in the footer of the result page:

var regExp = /]+>| /gi;

function ReplaceTags(xStr){

xStr = xStr.replace(regExp,"");

return xStr;

}

var mytable=document.getElementsByTagName("table")[0];

var myrow=mytable.getElementsByTagName("tr");

var i;

//Change the priceCol number accordingly

var priceCol=2;

var total=0;

rowlength=myrow.length;

for (i=1; i

var mycel =myrow.getElementsByTagName("td");

var textNum1 = ReplaceTags(mycel[priceCol].innerHTML);

var num1 = parseFloat(textNum1);

total = total + num1;

}

document.getElementsByTagName('table')[0].insertRow(rowlength);

var a=document.getElementsByTagName('table')[0].rows[rowlength];

a.insertCell(0);

//Label for the sum

var z=a.insertCell(1);

z.innerHTML="Total:";

//We insert a cell in column two and display the total.

var y=a.insertCell(2);

y.innerHTML="

$"+parseFloat(total).toFixed(2)+"

";

//Insert the rest of the cells

a.insertCell(3);

a.insertCell(4);

a.insertCell(5);

[casamurphy]

Thank you. Please consider placing this answer in the support section under the javascript section.

[benbutler123]

I just want to post a short note of concern for this topic. A while back I was enquiring about the possibility of making this happen and was told that it would be a custom java script solution that I would have to pay for. I in turn paid for the code to be written and have since deployed on my page. The reason for my concern is that you now seem to be \"giving away\" free code to folks when others have had to pay for such customizations.

[Guest]

I just want to post a short note of concern for this topic. A while back I was enquiring about the possibility of making this happen and was told that it would be a custom java script solution that I would have to pay for. I in turn paid for the code to be written and have since deployed on my page. The reason for my concern is that you now seem to be "giving away" free code to folks when others have had to pay for such customizations.

Hello Ben,

Thank you for posting your concern. We have many Javascript solutions at http://www.caspio.com/support/jssolution.asp. We use these Javascript example as the basis for doing custom programming to meet our customer's requirement. We don't charge our customers if they can use the code in the link themselves. However, we do charge a setup fee if customers would like for us to customize it for them. Each script is different for each customer.

[manecito]

Thanks to the earlier responses I was able to accomplish our requirements. I have modified it to give totals under two columns. I also added some extra formatting code to help make this display a bit more aesthetically pleasing, but the CSS may be subject to change depending on your situation.

I had issues with the original regExp parameters, but found that this worked for our needs.

<script type="text/javascript"> 
var regExp = /\$|\,/gi;
function ReplaceTags(xStr){ 
xStr = xStr.replace(regExp,""); 
return xStr; 
} 

<!-- Begin formatting for to display results as $ currency
function formatCurrency(num) {
num = num.toString().replace(/\$|\,/g,'');
if(isNaN(num))
num = "0";
sign = (num == (num = Math.abs(num)));
num = Math.floor(num*100+0.50000000001);
cents = num%100;
num = Math.floor(num/100).toString();
if(cents<10)
cents = "0" + cents;
for (var i = 0; i < Math.floor((num.length-(1+i))/3); i++)
num = num.substring(0,num.length-(4*i+3))+','+
num.substring(num.length-(4*i+3));
return (((sign)?'':'-') + '$' + num + '.' + cents);
}
//  End currency formatting-->

var mytable=document.getElementsByTagName("table")[0]; 
var myrow=mytable.getElementsByTagName("tr"); 

var i; 
//Change the salesCol and gpCol numbers accordingly 
var salesCol=2; 
var gpCol=3; 
var salesTotal=0; 
var gpTotal=0; 
rowlength=myrow.length; 
for (i=1; i<rowlength; i++) { 
var mycel =myrow[i].getElementsByTagName("td"); 
var textNum1 = ReplaceTags(mycel[salesCol].innerHTML); 
var num1 = parseFloat(textNum1); 
var textNum2 = ReplaceTags(mycel[gpCol].innerHTML); 
var num2 = parseFloat(textNum2); 

salesTotal = salesTotal + num1; 
gpTotal = gpTotal + num2; 
} 

document.getElementsByTagName('table')[0].insertRow(rowlength); 
var a=document.getElementsByTagName('table')[0].rows[rowlength]; 

a.insertCell(0); 

//Label for the sum 
var z=a.insertCell(1); 
z.innerHTML="<div align=right class='cbResultSetTableCell cbResultSetData'>[b]Total:[/b]</div>"; 

//Insert a cell in columns two & three and display the totals. 

var y=a.insertCell(2); 
y.innerHTML="<div class='cbResultSetTableCell cbResultSetData'>[b]"+formatCurrency(salesTotal)+"[/b]</div>"; 

var y=a.insertCell(3); 
y.innerHTML="<div class='cbResultSetTableCell cbResultSetData'>[b]"+formatCurrency(gpTotal)+"[/b]</div>"; 

//Insert the rest of the cells 
a.insertCell(4); 
a.insertCell(5); 

</script>