Response to "JQuery for Everyone: Total Calculated Columns"

Finally there is somekind of light-weight workaround for Calculated Columns and Totals using jQuery and the beloved CEWP. Find the original post here: http://www.endusersharepoint.com/?p=1106 (great work by the way :) ).

I have made an extension to this, so instead of just calculating the last column one can add any column for a total sum.
I use the sortable element ID to find the cellIndex for all rows that needs to be calculated (diidSortMean = a calculated column with the namne "Mean"). I use IE Dev Toolbar for that purpose.

The script currently calculates a total for three columns, but can easily modified in the CalcGroup function. My list is also expanded as default, will do more testing in the weekend.

If you find bugs, please leave a comment.


<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript"></script>
<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 getCellIndex(elId)
{
var myColumn = $(elId);
var myParent = myColumn.parents('th');
var result = myParent.attr("cellIndex");
return result;
}

function getArrayList(varIndex, groupName)
{

return $("#tbod"+groupName+"> tr").find(">td:eq(" + varIndex+ ")").get(); //array for groups based on cellIndex
}

function getArrayForOutput(varIndex, groupName)
{
return $("#aggr"+groupName+"> tr:first> td:eq(" + varIndex + ")");
}

function CalcGroupPerColumn(groupName,varIndex)
{
var x = 0;
var m = "";
var p1 = "";
var p2 = "";
if (groupName == '') {
$("table.ms-listviewtable:first> tbody:eq(2)").attr("id","tbod");
}

var arrayList = getArrayList(varIndex,groupName);

$.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);
}

var aggEl = getArrayForOutput(varIndex,groupName);

if (arrayList.length > 0) {
aggEl
.css("text-align","right")
.html("<b>Total = "+p1+m+addCommas(x.toFixed(2))+p2+"</b>");
}
}


function CalcSqrtPerColumn(groupName, varIndex, sourceValue)
{
var aggEl = getArrayForOutput(varIndex,groupName);
var sqrtVal = Math.sqrt(sourceValue);

if( sqrtVal > 0 )
{
if (aggEl.length > 0) {
aggEl
.css("text-align","right")
.html("<b>Sqrt = "+ addCommas(sqrtVal.toFixed(2)) + " </b>");
}
}

}


function CalcGroup(groupName) {//sums money in specific list column

var meanCellIndex = getCellIndex("#diidSortMean");

var stdDevCellIndex = getCellIndex("#diidSortStd_x002e_dev");

var varianceCellIndex = getCellIndex("#diidSortVariance");

CalcGroupPerColumn(groupName,meanCellIndex);
CalcGroupPerColumn(groupName,varianceCellIndex);
CalcGroupPerColumn(groupName,stdDevCellIndex);

}
//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>


New employeer

Hi all,

Back after a nice christmas vacation and with a new job. I have started to work as a SharePoint consultant for MMP AB in Malmö, where I´ll lead a new focus area regarding SharePoint.

// Henrik

off-topic: My grandmother Hilda celebrates her 100:th birthday

Link to the local magazine in Varberg

// Henrik