Friday, 30 January 2009

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>

9 comments:

chasepes said...

Hello Henrik:
Thanks for this workaround!

The script is hard to copy from the blog page. Is there another way to post the script so we can easily copy it?

Charlie Epes
Buffalo, NY USA
charlie.epes at fnrm.com

Henrik Andersson said...

ok, I´ll try to fix a better solution.

Henrik Andersson said...

Added code tags around the script, hopefully it helps when copying the code.

vivasuzi said...

Hi I'm not very much into scripts for Sharepoint, so can you tell me how to find the ID for my columns? How does the toolbar help?

Thanks!!

Henrik Andersson said...

@vivasuzi: Install and start (the dev toolbar installs an icon in the IE toolbar) the dev toolbar in your browser. Click on "Find" --> "Select element by Click" in the dev toolbar. Then click on the column name in the list (the one you usually click on for sorting).

Now this element is selected in the dev toolbar. Click on "Show Read-Only Properties" to display all attributes of the element. Check the ID of the element to get the input to the script.

// Henrik

Is said...

This is very nice. I appreciate you posting this! I can get neither the original nor your script to show totals for groups that are expanded by default. Is there a trick to this?

Anonymous said...

Can u Explain in detail.

Anonymous said...

Can u Please Explain From Where I Get These (varianceCellIndex) and (stdDevCellIndex)Values.

Thanks..

cookie said...

Great upgrade. This is not a true issue, but I added Christophe Tabed webparts, displaying multiple views from a list. When I implemented this script it will only return the total for the first list. The id's are replicated for each view. Not sure the impact of that with trying to make this work. in a webpage part, but I could not get totals for any secondary list views