SharePoint 2013 : Displaying sum for calculated column in a list view using JSLink

Table of Contents

• Scenario
• Solution
• Key Points
• Code Download

Scenario

Those readers familiar with the subject will know that you cannot just use  standard Number or Currency columns in order to display the sum for a calculated column.

Sums can be achieved using XSLT customizations in previous versions of SharePoint, but that option is not available in SharePoint Designer 2013.

Solution

One potential solution involves JQuery logic but that can be quite complicated.

A simpler light-weight solution is possible using JS Link.  

This article shows how to display a sum value for a calculated column using the “JS Link” functionality. Let us start simple with a custom list. Suppose we have a list named 'Sum App' with two fields of type Number and one field of type Calculated (calculation based on other columns). Below are the list of columns and their types.

• Column 1: Project Name 
• Column 2: Offshore billing
• Column 3: Onsite billing
• Column 4: Total billing

After adding some dummy records, It will look something like below.

The requirement is to show a sum of total billing values at the top just below the 6th column header as below.

Let’s look at the detailed steps to create a JavaScript file and using it in JSLink to display total sum value for “Total Billing” column.

1. Create a new .js file, copy and paste the below script block in your js file and upload it in Master Page Gallery as a new JavaScript Display Template. 

2. var _totalValue = 0.0;
   var _totalColumn = 6;  // Specify total number of columns in list view.
   var _columnIndex = 6;  // Specify the index of the column where you want to display sum values. (i.e. index for first column = 1)
   (function () {
        
       var calculatedFieldCtx = {};   
      
       calculatedFieldCtx.Templates = {};
    
       calculatedFieldCtx.Templates.Fields = {
           "Total_x0020_billing": {
               "View": CalculatedFieldViewTemplate
           }
       };
    
       calculatedFieldCtx.OnPostRender = []; 
       calculatedFieldCtx.OnPostRender.push(function()
       {   
           // Specify id of the list view table for which you want to display sum values.  
           var HTMLTableElement = document.getElementById("{ED71D9A3-58E5-44A8-B3F7-EC3AD763A558}-{F04B2518-5A35-4EEF-A0AE-1B9B64EA0947}");
    
                   var tbodyElement = document.createElement('thead');
                   tbodyElement.id = "customAggWPQ";
    
                   var trElement = document.createElement('tr');
                    
                   var tdElement;
                   for(var i = 1; i <= _totalColumn; i++) {
                           tdElement = document.createElement('th');
                           tdElement.id = "tdElement" + i;
                           if( i == _columnIndex) {
                                 tdElement.innerHTML = "<b>Sum= " + numberFormat(_totalValue) + "</b>";
                           }
                           trElement.appendChild(tdElement);
                   }
                    
           tbodyElement.appendChild(trElement);
           if(document.getElementById("customAggWPQ") == null) {
                   HTMLTableElement.insertBefore(tbodyElement,HTMLTableElement.firstChild.nextSibling);
           }
           else {
                   document.getElementById("tdElement" + _columnIndex).innerHTML = "<b>Sum= " + numberFormat(_totalValue) + "</b>";
           }
           _totalValue = 0.0;
       })
    
       SPClientTemplates.TemplateManager.RegisterTemplateOverrides(
           calculatedFieldCtx
       );  
   })();
    
    
   function CalculatedFieldViewTemplate(ctx) {        
       _totalValue += parseFloat((ctx.CurrentItem.Total_x0020_billing).replace(',', ''));
       return ctx.CurrentItem.Total_x0020_billing;
   }
    
   var numberFormat = function(number) {
           var sDelimeter = ',';
           number = Math.round(parseFloat(number) * 100) / 100;
           var numStr = ('_'  + number).replace('_', '');
           var buffer = [];
           for (var i = 0, len = numStr.length; i < len; i += 1) {
               buffer.push(numStr.substring(len - 1 - i, len - i));
               if (((i + 1) % 3 == 0) && (i != len - 1)) {
                   buffer.push(sDelimeter);
               }
           }
           return buffer.reverse().join('').replace(",.", ".");
   };
   

3. The file is ready now we only need to specify the file in the “JS Link” property of the list view. In the “JS Link” we have specified the following value:

   

4. Click Apply button to save your changes then stop page editing.

There we go! If everything is working fine, the list should now display a sum value for the specified calculated column on the list view page.

Key Points

• This solution works for both SharePoint 2013 On-Premises and SharePoint Online.
• It also works fine with out of the box client side filtering.

Code Download

You can Download the source code from here.

Hope it helps!!!

Note : It will work only with calculated columns of type Number. If you have set it with currency then you must need to modify the code accordingly. If there is an alternate approach for this kind of solution, then please add reference links in this Wiki article.