JQuery for Everyone: Total Calculated Columns
Problem: SharePoint will not perform “Totals” (Settings > Edit View) on a Calculated Column.
Solution: Use jQuery to create an array, do the math with JavaScript, and insert the value in the Totals row.
To use this script, edit your view to use “Totals” on at least one column in your list. Edit the view’s page and add a Content Editor Web Part (CEWP). Insert the following script in the Source Editor adjusting the col variable as needed (col = 4 above).
Update: Therese commented on this article and had a great idea–make it work with groups. I did some research and came up with a single snippet that does it all with no script configuration.
With the new script, configure your list to display your calculated column that needs totaling last. I did this mainly because grouped rows have an additional, hidden column for the exact same list and I didn’t want to update the script between views.
One note on a view with groups: you will not have an “overall total” because the data does not exist on the page until the group opens. This would cause the “overall total” to only equal the total of visible groups which does not match how the other Total functions work.
(UPDATED!).
Original recipe:
<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() {//sums money in specific list column
var col = 4; //which column to sum
var m = "$"; //change to "" for non-money format
var arrayList = $("table.ms-listviewtable:first> tbody:eq(2)> tr").find(">td:eq("+col+")").get();
var x = 0;
var p1 = "";
var p2 = "";
$.each(arrayList, function(){
x += Number($(this).text().replace(/\$|,|\)/g, "").replace(/\(/g,"-"));
});
if (x < 0) {//format for negative numbers
p1 = "(";
p2 = ")";
x = Math.abs(x);
}
$("#aggr> tr:first> td:eq("+col+")")
.css("text-align","right")
.html("<b>Total = "+p1+m+addCommas(x.toFixed(2))+p2+"</b>");
});
</script>
New, with grouping support:
<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 CalcGroup(groupName) {//sums money in specific list column
var x = 0;
var m = "";
var p1 = "";
var p2 = "";
if (groupName == '') {
$("table.ms-listviewtable:first> tbody:eq(2)").attr("id","tbod");
}
var arrayList = $("#tbod"+groupName+"> tr")
.find(">td:last").get(); //array for groups
$.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);
}
if (arrayList.length > 0) {
$("#aggr"+groupName+"> tr:first> td:last")
.css("text-align","right")
.html("<b>Total = "+p1+m+addCommas(x.toFixed(2))+p2+"</b>");
}
}
//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>
- JQuery for Everyone: Accordion Left Nav
- JQuery for Everyone: Print (Any) Web Part
- JQuery for Everyone: HTML Calculated Column
- JQuery for Everyone: Dressing-up Links Pt1
- JQuery for Everyone: Dressing-up Links Pt2
- JQuery for Everyone: Dressing-up Links Pt3
- JQuery for Everyone: Cleaning Windows Pt1
- JQuery for Everyone: Cleaning Windows Pt2
- JQuery for Everyone: Fixing the Gantt View
- JQuery for Everyone: Dynamically Sizing Excel Web Parts
- JQuery for Everyone: Manually Resizing Web Parts
- JQuery for Everyone: Total Calculated Columns
- JQuery for Everyone: Total of Time Differences
- JQuery for Everyone: Fixing Configured Web Part Height
- JQuery for Everyone: Expand/Collapse All Groups
- JQuery for Everyone: Preview Pane for Multiple Lists
- JQuery for Everyone: Preview Pane for Calendar View
- JQuery for Everyone: Degrading Dynamic Script Loader
- JQuery for Everyone: Force Checkout
- JQuery for Everyone: Replacing [Today]
- JQuery for Everyone: Whether They Want It Or Not
- JQuery for Everyone: Linking the Attachment Icon
- JQuery for Everyone: Aspect-Oriented Programming with jQuery
- JQuery for Everyone: AOP in Action - loadTip Gone Wild
- JQuery for Everyone: Wiki Outbound Links
- JQuery for Everyone: Collapse Text in List View
- JQuery for Everyone: AOP in Action - Clone List Header
- JQuery for Everyone: $.grep and calcHTML Revisited
- JQuery for Everyone: Evolution of the Preview
- JQuery for Everyone: Create a Client-Side Object Model
- JQuery for Everyone: Print (Any) Web Part(s) Plugin
- JQuery for Everyone: Minimal AOP and Elegant Modularity
- JQuery for Everyone: Cookies and Plugins
- JQuery for Everyone: Live Events vs. AOP
- JQuery for Everyone: Live Preview Pane
- JQuery for Everyone: Pre-populate Form Fields
- JQuery for Everyone: Get XML List Data with OWSSVR.DLL (RPC)
- Use Firebug in IE
- JQuery for Everyone: Extending OWS API for Calculated Columns
- JQuery for Everyone: Accordion Left-nav with Cookies Speed Test
- JQuery for Everyone: Email a List of People with OWS
- JQuery for Everyone: Faster than Document.Ready
- jQuery for Everyone: Collapse or Prepopulate Form Fields
- jQuery for Everyone: Hourly Summary Web Part
- jQuery for Everyone: "Read More..." On a Blog Site
- jQuery for Everyone: Slick Speed Test
- jQuery for Everyone: The SharePoint Game Changer
- JQuery For Everyone: Live LoadTip
@Terry,
You shouldn’t have to edit anything. You need to have at least one column of your list using a Totals function (like count on the Title column).
I was trying to implement this on a list that has several calculated columns. Every time I add the array of column numbers I causes the script to error. is there an easy way to convert this to apply to several columns?
@Larry,
Neither was tested with multiple calc columns. After this example, I moved to a more reliable model for several calculations, see my project here: (http://owsapi.codeplex.com)
Anyone know how to do this on 2010 yet?
Hi Paul, thanks for this post!
However! I want to modify the concept a little bit.
I want to create a page that only displays the totals and nothing else.
I have been trying to do this in a DVWP with xsl but the iterative nature of the xsl:value-of select is restricting me to ‘current row’ so i cannot total. Basically, just show your Sum= Average= and Total= lines on a page.
Any idea? Should I post this to STP?
@JohnnyM,
I actually had some of those same requirements which lead me to make the owsapi ( http://owsapi.codeplex.com ). It gives you a robust set of methods to perform math on a list’s values. The other benefit is that you can get those values and push them into another solution (like a graph API).
@Jason,
I’ll look into it as soon as I see a copy of 2010.
Wow, I saw that post the other day, but guess I didn’t really pay attention. So, I just downloaded and am testing.
THANK YOU PAUL!!!
I see there are only 177 downloads, cant understand why not more.
EVERYONE ELSE… Go get http://owsapi.codeplex.com It will set you FREE, like Yoga!
I’m also including it in the jQuery resources team site template. — Mark
Hey Paul, I have a custom list with a Yes/No column, There are 20 rows of data in the list and 10 are Yes and 10 are No. When I apply ‘Totals’ to the AllItems View I get a nice Count=10 at the top of the view. This ‘Count=#’ is just what I want to get into a webpart.
I am trying ows.getSum(o.ctx,’Question One’); But that wont work. i get ‘undefined’.
Do I need to filter that column?
Any ideas?
@JohnnyM,
Do you get “o is undefined” ? If so, try the following in Firebug:
owsapi(); console.log(ows.getSum('ctx1','Question One'));If that works, it’s your function that you used to set the context variable. o.ctx only means something when in a callback function (function(o){…).
Outside of a callback, you have to provide the context. Luckily, when viewing a standard list view web part, it’s always ‘ctx1′.
Yes/No data appears as 0, 1, or undefined. The getSum method can skip over undefined values, so you should get your sum even when no values are defined (result = 0). So no, you shouldn’t have to filter the column.
Duh, that was it. My context was defined for a callback function. Thank you.
Now it returns the requested value as advertised!
So, now I’ll get this to pass all column’s values to an array and populate a table or something.
Thanks again.
Hi,
Is there any limitation that a perticular group should have these many number of item ? Because I’m having 123 items in a single group. I used the above code and I’m getting Total=NaN. Please do some favour.
Regards,
Rao.
@P.Venkateswara Rao,
NaN (not a number) usually means you’re including data that can not be parsed as a number. Make sure you counted the columns correctly. Make sure you included columns like the file attachment that you don’t normally see data in.
Hi,
Thanks for the responce. I observed one thing that, When items in a group exceeds 100 it will give more message at the end of that group, which makes the problem. Because if I clcik more option then I’m redirecting to the 101 to another 100’s count. In this one I’m able to see the Sumation of the Column. I checked from 1 to 100 it also giving correct value. But in grouping, if items greater that 100 giving this type of problem. Is it possible to increase the number of items should display more that 100 item. May be we can do it by Designer. Any Suggestion please.
Thanks,
Rao.
@Rao,
If that’s true, it’s another reason to use owsapi (http://owsapi.codeplex.com) or web services over this method.
This is great. How about if I wanted the group averaged instead of totaled?
@mlv,
Change line 36:
.html(”Total = “+p1+m+addCommas(x.toFixed(2))+p2+”“);
to
.html(”Avg = “+p1+m+addCommas(x.toFixed(2)/arrayList.length)+p2+”“);
Very cool, now how can this jquery be combined to chart the totals in a % pie…
with http://www.endusersharepoint.com/2009/04/29/finally-dynamic-charting-in-wss-no-code-required-part-3-multiple-pie-charts/
Thanks,
Charles
@AutoSponge
Thanks! This is working, but actually appears it is giving me a lower number than it should. I checked the same numbers manually and then on excel – the SP list is giving me about 6 points lower… Any ideas as to why?
I think I understand what it is doing, but not sure how to fix the code. It looks like it adding an extra 2 to divide by the total – the extra two lines being the first grouping and then the 1st total column that is the out of the box function. So it should be just dividing the actual records, but instead is dividing by lines within the grouping…
@Paul
Okay I got it… I am sure you would have gotten this in about 2 seconds, but here it is for anyone who is not js savvy yet. Just needed to add in some extra math.
.html(”Avg = “+p1+m+addCommas(x.toFixed(2)/(arrayList.length-2))+p2+”“);
Just had to add in the minus 2 on the array length. Can you confirm this is normal … not just some weird list I have going on?
I’ve added the grouping totals to the CEWP that has one calculated field (SqFt+0) however nothing is being displayed…not even an error message. Any help would be appreciated.
Hi,
I’ve successfully got this working on the All Items view, but when I’m trying to add it to a grouped view, it displays Sum=NaN.
If I try to print (i.e. alert) the value, I see that the grouping value itself is printed. In your example above, this would print Rate: $90.00 (2).
Do you know what could be wrong?
@Frank
Did you work out how to fix your Sum=Nan problem? I am having the same issue, so if you have a fix can you please let us know?
How do I modify the script if I want to total on a sub-group? I’m getting javacript errors in IE8 when I try to expand a collapsed sub-group. It just says “Loading…”
When I try to change the code to average instead of sum, the total disapears from the page. Any suggestions as to why this might be happening?
Thanks,
J.E.S.
@Jon,
I’d have to see the modifications but you may have a syntax error. Open in Firebug and check the error log.
it rocks man!
I am also having problems with getting the Average to show – just shows nothing. The script for the Total does work. Anyone figure it out? Very noob :)
Hello, When I try to use this script I can only get it to work when I set the data type of the calculated field to currency US-dollar, when I try to set it to Swedish it shows Nan, when I set it to Number it also shows Nan, when I set it to US-dollar it shows the vaule divided by 1000 so 1682 = 1.68
What could be wrong?
This one is helpful..it shows total sum but one question..
how to handle if it consist of pagination?
like 1-100 view and 101 to 200..
The total only gets all the row of the specified column that is display.but if you click the next which is 101 to 200 the total sum will change to that specific page.
Any solution on this matter?
how about if it consist of pagination like 1-100 display for page 1 and 101 to 200 display for page 2 and so on..
problem is when you cange the page the sum will also change.any solution on this matteR?
I’m hoping you can lead me in the right direction. I have a list that I need to chart. It goes like this:
product line sold
product A 100
product B 200
product A 50
I have to get the totals per product, but have them in such a way that I can do a bar chart on the totals per product. Is that possible? Any hints you can provide?
Very cool, now how can this be combined with http://www.endusersharepoint.com/2009/04/29/finally-dynamic-charting-in-wss-no-code-required-part-3-multiple-pie-charts/ to chart the totals in a % pie…
Thanks,
Charles,
Hi,
I am trying to make a total of hours with minutes.
Could you please quickly explain me the “replace” part of this line :
x += Number($(this).text().replace(/\$|,|\)/g, “”).replace(/\(/g,”-”));
this is the format of my calculated column : h:mm.
Thank you,
Geoffrey
Hello – I implemented this solution to a 2007 Sharepoint Site and it worked perfect. Then I tried in a 2010 Sharepoint site and nothing happens. Did anyone figure out how to do this in Sharepoint 2010. I do not have access to change any masterpages, sharepoint designer or add anything to the server. The solution to add a content editor webpart is perfect, why does it not work in 2010
Thanks for the help
Karen
Hello all!
This works great for me, in the standard “All itens” list page… But, I need to put this Jquery script in a page with TWO list web parts.
The list which I want to calculate some Totals is the second one in that page (from top to bottom), and in this scenario the script does nothing. If I move that list web part to the top of the page, along with the CEWP containing the script, it starts working. But, for application layout purposes, I need to place another list web part before this main list.
I’m trying to figure out what I need to modify in the Jquery script… Any help will be very appreciated!!
Thanks!!
Hello!
I want to understand why a negative value is not displayed on Total.
Eg. line 1 = -540
line 2 = 60
Total displays 480 instead of -480.
How can I fix it?
Thanks in advance.
I have figured it out. TKS!