Total in calculated columns of Sharepoint lists
now that’s a tough one. I had to do some research on this and finding many solutions varying from editing the list page in Sharepoint designer to editing the code of sharepoint list columns and the “Total” and one article explained to write a VB code for it. Well there’s a simple solution – although based on Java Script – but the script is available for free on Google Codes and I attach it here with this post – Download it here –
Now place this .js in a shared documents as you are going to link to it through your script and note the shortcut to this script.
For this to work:
- you need to have totals working on one of the other columns.
- After you have put totals (any kind) for another column in a view
- edit the page and add a webpart on top
- set the “chrome type” to none and in the source code editor type the code below
- replacing http://my.sharepoint.com/doclib/ with the shortcut of your VLE + Doc. Lib where you uploaded the script
- and change the column number (9 in this code) in var col (line 15) to the column number (in the list view) of the calculated column.
- in line 16 you can change the format of prefix (currently $) to any other currency or leave it as “” for non-money format (instruction in code as well)
<script type=“text/javascript”>
function addCommas(nStr) {//formats number
nStr += ”;
x = nStr.split(‘.’);
x1 = x[0];
x2 = x.length > 1 ? ‘.’ + x[1] : ”;
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
x1 = x1.replace(rgx, ‘$1’ + ‘,’ + ‘$2’);
}
return x1 + x2;
}
$(function() {//sums money in specific list column
var col = 4; //which column to sum
var m = “$”; //change to “” for non-money format
var arrayList = $(“table.ms-listviewtable:first> tbody:eq(2)> tr”).find(“>td:eq(“+col+“)”).get();
var x = 0;
var p1 = “”;
var p2 = “”;
$.each(arrayList, function(){
x += Number($(this).text().replace(/\$|,|\)/g, “”).replace(/\(/g,“-“));
});
if (x < 0) {//format for negative numbers
p1 = “(“;
p2 = “)”;
x = Math.abs(x);
}
$(“#aggr> tr:first> td:eq(“+col+“)”)
.css(“text-align”,“right”)
.html(“<b>Total = “+p1+m+addCommas(x.toFixed(2))+p2+“</b>”);
});
</script>
In case you want this to work with Group view, the code is below:
<script src=”http://my.sharepoint.com/doclib/jquery-126min.js” type=”text/javascript”>
<script type=“text/javascript”>
function addCommas(nStr) {//formats number
nStr += ”;
x = nStr.split(‘.’);
x1 = x[0];
x2 = x.length > 1 ? ‘.’ + x[1] : ”;
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
x1 = x1.replace(rgx, ‘$1’ + ‘,’ + ‘$2’);
}
return x1 + x2;
}
function CalcGroup(groupName) {//sums money in specific list column
var x = 0;
var m = “”;
var p1 = “”;
var p2 = “”;
if (groupName == ”) {
$(“table.ms-listviewtable:first> tbody:eq(2)”).attr(“id”,“tbod”);
}
var arrayList = $(“#tbod”+groupName+“> tr”)
.find(“>td:last”).get(); //array for groups
$.each(arrayList, function(i,e){
x += Number($(e).text().replace(/\$|,|\)/g, “”).replace(/\(/g,“-“));
if ($(e).text().indexOf(“$”) >= 0) { m = “$” };
});
if (x < 0) {//format for negative numbers
p1 = “(“;
p2 = “)”;
x = Math.abs(x);
}
if (arrayList.length > 0) {
$(“#aggr”+groupName+“> tr:first> td:last”)
.css(“text-align”,“right”)
.html(“<b>Total = “+p1+m+addCommas(x.toFixed(2))+p2+“</b>”);
}
}
//rewrite of WSS function
function ExpGroupRenderData(htmlToRender, groupName, isLoaded)
{
var tbody=document.getElementById(“tbod”+groupName+“_”);
var wrapDiv=document.createElement(“DIV”);
wrapDiv.innerHTML=“<TABLE><TBODY id=\”tbod”+groupName+“_\” isLoaded=\”“+isLoaded+“\”>”+htmlToRender+“</TBODY></TABLE>”;
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
if (CalcGroup) {
CalcGroup(groupName+“_”);
}
}
$(function() {
CalcGroup(”);
});
</script>
Code courtesy of Paul Grenier – original post available here
