3

There is a very nice solution below from ViewMaster (http://www.viewmaster365.com/#/Create/Sum) that involves some scripting within the formula of a calculated column itself.

However, the Sum will be displayed at the bottom of the column (after the last item in the view), but I need it to be at the top of the column (before the first item in the view), exactly like SharePoint displays Totals for normal (non-calculated) columns.

Is there anyone who can modify the scripting below to achieve this? I would be immensely grateful.

="<div style=""color:"
& IF(    [Rate]<[Buy]   ,"red","green")
& """>"
& DOLLAR(   [Qty]*[Rate]   )
& "</div>"
& "<div class=""vmSums"" 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 dataSum='data-vmSum',dataCnt='data-vmCount',total=value="
&    [Qty]*[Rate]   
& ",count=0;"
& "if(TBODY.getAttribute(dataSum)){"
& "total=parseFloat(TBODY.getAttribute(dataSum))+value;"
& "count=~~TBODY.getAttribute(dataCnt)+1}"
& "TBODY.setAttribute(dataSum,Number(Math.round(total+'e2')+'e-2').toFixed(2));"
& "TBODY.setAttribute(dataCnt,count);"
& "window.clearTimeout(window.vmSumFunc);"
& "window.vmSumFunc=window.setTimeout((function(){"
& "var lastrownr=~~TBODY.getAttribute(dataCnt),"
& "last=document.getElementsByClassName('vmSums').item(lastrownr),"
& "total=parseFloat(TBODY.getAttribute(dataSum)).toFixed(2);"
& "last.textContent='$'+total;"
& "last.style.display='block';"
& "}), 100)"
& "}"">"
1

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back


Original answer:

This whole technique is based on how SharePoint displays the View.

And this code (for every ListItem) is not executed in the visible Row-Order because it runs async (because IMGs load async.. and we need IMGs becuase Microsoft blocked the SCRIPT tag)

  • Every Row gets a total (but the DIV is hidden)

  • Every Row starts a setTimout to display the Total

  • But every next processed row cancels that function again

  • So the function is executed only once eventually

So all you have to do is delete the code that displays the last total in the last row and write total to someplace else in your page.

replace

& "last.style.display='block';"

with

& "document.getElementById('pageTitle')=total;"

Update #1

For the non-javascript users... that should be:

& "document.getElementById('pageTitle').innerHTML=total;"

--The ViewMaster

Solution

Lucky you! You won the Once-A-Month I really help people who do not understand HTML or Javascript ticket

As I explained every row gets a Total DIV, So all you have to do is display that DIV above the row-calculation and at the end not show the last one, but the first one.

It will mess up the alignment a bit; if you do not want that you need to create a HTML/DIV placeholder on screen and use the innerHTML from above

Now hit that Question Answered button :-)

="<div class=""vmSums"" style=""display:none;font-weight:bold;border-bottom:1px solid black;""></div>"
&"<div style=""color:"
& IF(    [Rate]<[Buy]   ,"red","green")
& """>"
& DOLLAR(   [Qty]*[Rate]   )
& "</div>"
& "<img src=""/_layouts/images/blank.gif"" onload=""{"
& "var TBODY=this;TBODY=this;while(TBODY.tagName!='TBODY'){TBODY=TBODY.parentNode};"
& "var dataSum='data-vmSum',dataCnt='data-vmCount',total=value="
&    [Qty]*[Rate]   
& ",count=0;"
& "if(TBODY.getAttribute(dataSum)){"
& "total=parseFloat(TBODY.getAttribute(dataSum))+value;"
& "count=~~TBODY.getAttribute(dataCnt)+1}"
& "TBODY.setAttribute(dataSum,Number(Math.round(total+'e2')+'e-2').toFixed(2));"
& "TBODY.setAttribute(dataCnt,count);"
& "window.clearTimeout(window.vmSumFunc);"
& "window.vmSumFunc=window.setTimeout((function(){"
& "var lastrownr=~~TBODY.getAttribute(dataCnt),"
& "last=document.getElementsByClassName('vmSums').item(0),"
& "total=parseFloat(TBODY.getAttribute(dataSum)).toFixed(2);"
& "last.textContent='$'+total;"
& "last.style.display='block';"
& "}), 100)"
& "}"">"

CSR version

SP.SOD.executeFunc("clienttemplates.js", "SPClientTemplates", function() {

  function sumColumn(columnName){
    var rows=ctx.ListData.Row; // global ctx!!
    var sum=rows.reduce(function(sums,item){  // loop over all Array items
        var value=item[columnName];
        return sums + (value ? Number(value) : 0); // calculate sums, does not take care of strings!
    },0); // sums starts at 0
    var id="Sum"+columnName; // unique id so multiple columns can be summed
    var sumDIV=document.getElementById(id);
    if(!sumDIV){ // if no Sum div exist
      sumDIV=document.createElement('DIV');
      sumDIV.id=id;
      // find column
      var header=document.querySelector("[id*='thead-WPQ1'] div[name='"+columnName+"']");
      header.appendChild(sumDIV);
    }
    sumDIV.innerHTML=String.format("<B>Sum: {0}</B>",sum);
  }

  function init() {
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
      Templates: {},
      OnPostRender: function(ctx) { 
        sumColumn('MonthSpan');   // USE INTERNAL FIELD NAME!
      },
    });
  }
  RegisterModuleInit(SPClientTemplates.Utility.ReplaceUrlTokens("~siteCollection/Style Library/sumcalculated.js"), init);
  init();
});

ICC

  • Very grateful for the ViewMaster himself to help with this, but the replacement line did not work -- the calculated column happily accepted the change, but the Total was no longer showing at all in the view. Your replacement line excluded the variable "last" entirely, could this be the cause of the problem? – user47684 Oct 8 '15 at 20:57
  • This is pure JavaScript coding only wrapped in a Calculated Column. May I presume you do not master JavaScript? – Danny '365CSI' Engelman Oct 9 '15 at 5:58
  • No, I am certainly no JavaScript master, but I do understand that it is, along with some HTML, wrapped inside the calculated column. I made the replacement exactly as you had instructed, but the total will not show at all. Were you able to test this modification to the code yourself with a successful result? If there are there other steps that need to be taken that you may have assumed I would understand, forgive me, I do not. – user47684 Oct 9 '15 at 16:32
  • No I did not check it... you learn the most by fixing mistakes (others make) ;-) – Danny '365CSI' Engelman Oct 9 '15 at 19:30
  • Thank you, but that put the total at the top of the page, not at the top (beginning) of the calculated column. I have definitely been experimenting with the code, just can't get it to work. – user47684 Oct 9 '15 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.