Posted on November 17, 2016|by: Ricardo Palhano| 2 Comments

Creating a Calculated Column

MetroStar previously published a blog on using the DataView Web Part and SharePoint Designer to get the total on a Calculated Column. This is because SharePoint does not do totaling on Calculated Columns Out of the Box (OOB). Here we discuss an alternative method that does not have any restrictions on the number of items and does not require Designer.

This is based on the excellent resource found at http://www.viewmaster365.com/#/Create/Sum, and just tries to simplify things a bit for someone who wants a quick solution to totaling their values. Please visit the link above for a more in depth discussion on the solution.

Creating the List

We begin with a simple list that just has 3 columns of information: Title (String), Quantity(Number), and Rate(Currency).

Simple SharePoint with three columns

Creating a Calculated Column

The first thing that we will do is create a new calculated column called Cost(Currency).

=[Quantity]*[Rate]

For the Formula we enter in the Quantity times the Rate. Be sure to change any values highlighted in yellow to the names you use for your columns.

Cost Currency Column SharePoint

Now we can see that we have the totals for the Cost. But if we add totals to the view, the Cost column does not come back with a result.

Cost Column without results

Generating a Total for the Cost Column

In order to get a total for the Cost column we need to create a new column that will effectively duplicate the calculation found in the Cost column, but will also perform some JavaScript functions to hide those values, then create a row underneath that sums all of the previously calculated values.

What the code is doing is creating a new div called SumCost where each value is hidden so that we only show the final calculated total in the last row in the table inside the div. Then we set the display back to block and add a dollar sign for that last one.

Generating a total for the cost column

="<div class=""SumCost"" style=""display:none;font-weight:bold;border-top:1px solid black;""></div>"
& "<img src=""/_layouts/images/blank.gif"" onload=""{"
& "var TBODY=this;TBODY=this;while(TBODY.tagName!='TBODY'){TBODY=TBODY.parentNode};"
& "var TotalSum='data-Sum',dataCnt='data-Count',total=value="
&     [Quantity]*[Rate]
& ",count=0;"
& "if(TBODY.getAttribute(TotalSum)){"
& "total=parseFloat(TBODY.getAttribute(TotalSum))+value;"
& "count=~~TBODY.getAttribute(dataCnt)+1}"
& "TBODY.setAttribute(TotalSum,Number(Math.round(total+'e2')+'e-2').toFixed(2));"
& "TBODY.setAttribute(dataCnt,count);"
& "window.clearTimeout(window.TotalSums);"
& "window.TotalSums=window.setTimeout((function(){"
& "var lastrownr=~~TBODY.getAttribute(dataCnt),"
& "last=document.getElementsByClassName('SumCost').item(lastrownr),"
& "total=parseFloat(TBODY.getAttribute(TotalSum)).toFixed(2);"
& "last.textContent='$'+total;"
& "last.style.display='block';"
& "}), 100)"
& "}"">"

Create a new column called SumCost(Number) and copy and paste the code into the calculation. Remember to change any values that are bold and underlined to the names you used. You can also remove any code that you do not want, such as the display styles, or the $ we add.

Displaying Total Calculated Columns

The end result is a total value for the Cost across all the items displayed in that view.

View of cost columns with total displayed

Calculated Column with Grouping

Because this method is using a Calculated Column, it also works with Grouping.

Calculated Column with Grouping

Calculated Items on a Page

Note that with paging, only the items on that particular page are calculated.

Calulated items on a page

Filed in: Collaboration, Sharepoint

2 Comments

  1. Rick Hon
    November 21, 2016 at 3:24 pm

    Where’s the code? You mention items in yellow. Where?

    Thanks – I could really use this fix.

    • November 23, 2016 at 3:08 pm

      Rick,

      Looks like a new formatting change wiped the code. I updated the post with the code again and now it is bolded and underlined instead. Thanks for the heads up!

Your email is kept private. Required fields are marked *

*
*