Tuesday, June 4, 2013

Sum Grouped Calculated Columns in SharePoint

After finally migrating to sharepoint 2010 from sharepoint 2007, i realized some of our custom Data view webparts were no longer working. They had been customized to add up the total hours and display it for each group.
The view had items grouped by user and a total number of hours for that user. This the hours was computed by a check in time and check out time. Sharepoint does not provide a way to calculate a total for calculated fields.
Most solutions tell you to use a dataview webpart to do this. Our old solution worked that way, but it was a hassle to add new views and re-use the code in other projects.

My solution was to throw out the old code and re-build it using out of the box, easy to create views and some javascript/jQuery magic.

The following code will find items by group, and compute a total for that group.
The javascript has two variables:
COLUMNTITLE: The name of the column  you would like to find the total of all the rows
TEXTHEADER: The text you would like to show in front of the total. This cannot be blank, and should be some unique text. It is also used to tell if the total has already been calculated and displayed.

This code will automatically display totals for groups that have been expanded. If the group has not been expanded, it cannot show the total because SharePoint loads the data using ajax. When the group is expanded, the total will be calculated and displayed.

This works on a SharePoint 2007 site upgraded to 2010, but still using the 2007 UI. I haven't tested this in other scenarios. The idea should be the same, some of the css tags might change. YMMV.


var TEXTHEADER = "Total Hours: ";// text to put in front of the total
var COLUMNTITLE = "TotalBillableTime";// which column of data do you want to sum up?
var COLUMNNUMBER =0; // placeholder variable.

//alert("found:"+FindColumnNumber(COLUMNTITLE));

$(function() {
    // attach to clicks to load the data when the user clicks to expand any closed sections
    COLUMNNUMBER = FindColumnNumber(COLUMNTITLE);
    AttachClicksToComputeTotalTime();
    // find the loaded blocks of data and populate the header
    // this can take some time, so we're calling it after a delay.
    // better method would be to attach to the ajax complete call of sharepoint if there is one.

    setTimeout(function(){FindLoadedBlocks();},1000);
   
});

function     FindLoadedBlocks(){
        // search for all tbody's that have tbod in the id and have isLoaded = true
        // compute the totals for these
         $('tbody[id^="tbod"][isLoaded="true"]').each(function($this){
            ComputeBlock(this.id);
          });   
}

function AttachClicksToComputeTotalTime(){
    // find all the header links that the user clicks to expand the data.
    // add an onClick event to compute and display the totals
    $("td.ms-gb2 a").click(function($this){
        FindBlock(this);
    });
}

function FindBlock(Headerblock){
    // given a header, find it's child tbody and wait until it is fully loaded to compute its data
        var parentSectionID = Headerblock.parentNode.parentNode.parentNode.id;
        var childSection = parentSectionID.replace("titl","tbod")+"_";
        var kidSection = $("#"+childSection);
        if($(kidSection).text().length<100 br="">            setTimeout(function(){FindBlock(Headerblock);},50);
            return;
       
        }    else{
        // it's done, let's load it.
            ComputeBlock(childSection);           
        }

}


function ComputeBlock(blockName){
    // for a block of data rows, compute the total and insert it into the header
        var parentSectionID = blockName.replace("tbod","titl").replace("__","_");       

    // when groups are expanded by default, a second tobdy is created
    // check if this tobody has data, if not, go to the next
         if($('#'+blockName).html().length<100 br="">             var block = $('#'+blockName).next();

        }else{
            var block = $('#'+blockName);
        }
       
        var rowSum = 0;
        childRows = $(block).find("tr").each(function(){ 

            // find the 6th column
            var x = $(this).find(":nth-child("+COLUMNNUMBER +")");
            var rowVal = parseFloat($(x).text().trim());
            if(!isNaN(rowVal)){
                rowSum+= rowVal;
            }

        });
        rowSum = rowSum.toFixed(2); // trim it to 2 decimal places

        // find the parent section
        var ParentSection =$("#"+parentSectionID+" tr td");
    // check that the parent section doesnt' already have the totals   
        if($(ParentSection).text().indexOf(TEXTHEADER)>0){

        }
        else{

            $(ParentSection).append("   "+TEXTHEADER +"   "+rowSum+"");
        }

}

function FindColumnNumber(ColumnTitle){
// find the column by name and figure out where it is.
    var pos = 0;
    var foundVal = 0;

    $(".ms-vh2").each(function(){
         pos++;
         if($(this).text() == ColumnTitle){
                 foundVal = pos;
         }
    });
    return foundVal;
}






each group section will look something like this: