SharePoint: Displaying Calculated Column SUMS in a View (Screencast)
Author: Laura Rogers, Birmingham, AL
SharePoint 911
Have you ever noticed that in SharePoint views, when selecting to view “Totals”, calculated columns aren’t listed as an option for column totals? In this screencast, Laura Rogers demonstrates this dilemma, and shows how you CAN create a view that displays calculated column totals. This is done by creating a Data View Web Part in SharePoint Designer. Also, if you’ve noticed that calculated columns don’t ever show up in a data view web part, Laura shows the fix for this, too.
EUSP is sponsoring a series of Live Online Workshops, Make the Most of SharePoint Data View Web Parts with Laura Rogers as host. If you need hands-on experience in creating solutions with the data view web part, you will enjoy attending these sessions.
Author: Laura Rogers, Birmingham, AL
SharePoint 911
- New Article Series: Laura Rogers on Data View Web Part Basics
- Data View Web Part, The Basics - Insert a DVWP on Your Page
- 6 Minute Screencast: Insert a Data View Web Part onto a SharePoint Page
- Data View Web Part, The Basics - Folders
- 3 Minute Screencast: Use DVWP to display all files, even those stored in folders!
- Data View Web Part, The Basics - Add a Hyperlink
- Data View Web Part, The Basics - Keep it Clean
- Data View Web Part, The Basics – Multiple Edit Forms
- 3 Minute Screencast: Data View Web Part, The Basics – Multiple Edit Forms
- Live Online Workshop - Data View Web Part Solutions: Part 2
- 5 Minute Screencast: SharePoint Web Services for Non-Developers
- 3 Minute Screencast: Merging SharePoint Document Libraries
- 2 Minute Screencast: Permissions Dashboard
- 4 Minute Screencast: Join View of SharePoint Lists
- Get it Together: Groups and Totals in the Data View Web Part (Screencast)
- SharePoint: Open Links in a New Browser Window (Screencast)
- How to Create a SharePoint Data View Web Part “Rollup” (Screencast)
- SharePoint: "New" Icon (Screencast)
- Display a SharePoint List on Another Site (Screencast)
- Customize the "no items" Display Text in SharePoint (Screencast)
- SharePoint: Displaying Calculated Column SUMS in a View (Screencast)
HS! Nice one Laura!
Really nice.
Gonna have to try with calculated column & Christophe Text to HTML script!
Combined with an expand/ collapse function, that could beinterresting.
Your screencasts are always a pleasure to follow.
Greg
Great great great!
Thanks, Laura, I am in the midldle of doing a project where this info has come in a very timely manner.
This is excellent Laura. Good work and I love the Holiday Inn Express joke =))
@EUSP: I just noticed a typo in Laura’s name in this sentence:
EUSP is sponsoring a series of Live Online Workshops, Make the Most of SharePoint Data View Web Parts with Luara Rogers as host
Hi Matt,
I’ve fixed the typo, thanks!
Your tips are small but they make real difference for endusers. Great, Keep it up
Hi Laura,
I have a different situation here where I am doing multiplication of the two columns in calculated column and trying to sum calculated column. In that case your solution did not really help. Any idea on how to get sum of calculated column with multiply two columns.
Thank you
I have been using another solution to get the Sum of Calculated fields. I have created a new xsl:template that i can reuse
The way to use is for example
I can write an article and publish if it needs more explanation on how to use this
The code didn’t appear when i posted, Let me try this
<xsl:template name="SumOfCalculatedField"> <xsl:param name="Rows"/> <xsl:param name="FieldName"/> <xsl:param name="result" select="0"/> <xsl:choose> <xsl:when test="$Rows"> <xsl:variable name="currentValue" select="$Rows[1]/@*[name() = $FieldName]"/> <xsl:call-template name="SumOfCalculatedField"> <xsl:with-param name="Rows" select="$Rows[position() > 1]"/> <xsl:with-param name="result" select="$result + number(translate(substring-after($currentValue,'$'),',',''))"/> <xsl:with-param name="FieldName" select="$FieldName"/> </xsl:call-template> </xsl:when> <xsl:otherwise><xsl:value-of select="$result"/></xsl:otherwise> </xsl:choose> </xsl:template> a sample for using this template would be, <xsl:call-template name="SumOfCalculatedField"> <xsl:with-param name="Rows" select="/dsQueryResponse/Rows/Row"/> <xsl:with-param name="FieldName" select="'Total_x0020_Indirect_x0020_Benef'"/> </xsl:call-template>Not sure if this might help someone else but you can also do this easily with a workflow. This was the method I used way back as I like to avoid editing pages if I can help it in SharePoint Designer. This way you can just use as many views as you want and can use all out of the box web interface options.
http://sharepointkb.wordpress.com/2008/07/22/total-calculated-columns-in-sharepoint/
Great post and methodology for accomplishing the same sort of thing Laura :)
Richard Harbridge
You are the greatest Laura!
Nice!
Laura,
Do you have an example of where you do these totals with grouping? (For example group by department (assuming you have multiple rows per department)
Thanks
Dale,
Take a look at my post here:
http://sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?ID=70
I have a reference in there, to another post that shows how to do it with groups.
In the column of items that I’m attempting to sum, when there are blank cells, the sum becomes “NaN”. When there are values in each of the cells, no problems. Any ideas?
Also, is there an easy way to display these totals in a location other than at the base of the list? I’d like to create a table that displays this data so the user doesn’t have to scroll to the bottom of the list.
Thanks!
Laura,
Great post, I needed to accomplish this in a grouped DVWP and was unable to get it to work however I noticed that if you format the calc colums in $format (in the list colum setup) the DVWP will not display the results as you indicated, however if you format the calc colums in a plain # format (again in the list colum setup) then create your DVWP the results will show up.
Next I follwed your steps for getting the sum of the rows and then changed the format to $ via the chevron in the DVWP and presto it worked.
Anyone having the NaN or 0 try this out and see what happens.
Converting an excel form into SP and I have numerous choice fields whioch I need to sum on the SPlist. any suggestions?
Hello,
While I found your screencast to be very helpful, I was wondering if you knew of a way to sum the calculated columns grouped by a particular user.
For example, you have a list of various items that each have an user associated with them, as well as a calculated column. We would then create a new view that would group up all of the items by their user and sum up that calculated column.
I have been looking around and I cannot find anything related to the issue and am quite lost on how to do it. Any help would be greatly appreciated. Thank you.
Nevermind, it appears I didn’t read your comments closely enough! I will try out the link you suggested. Thanks!
Hi Laura – Thank you for your video. It was an excellent help to me.
I have successfully created the column sums within my groups for my data view. However, I would like to go one step further and create an overall sum for the entire data view (a total of all the sums).
I’ve tried copying and modifying the grouping sum code (removing the nodeset code and replacing it with $Rows), but I’m not getting good results.
Any suggestions would be greatly appreciated.
Thank you!!
Cheers Laura!
I am trying to create a league table for Formula Student at Liverpool at the moment. And ran into a bit of a problem.
I was just wondering, is it possible to use the calculated values, for examples, the sum of the columns and plug it into another list? Or use it to sort the columns is ascending order?
Any help or suggestions would be greatly appreciated
Thanks!
Hi,
Thanks for your hard work. I’m trying to do the same in SharePoint 2010. I got to the part where I should insert the sums row but can’t find this option for the XSLTViewWebPart. Can you help?
Thanks,
Martin
Hi Martin,
It appears as though Microsoft has removed this in 2010 XSLT list view, because I can’t find it either. :-(
Laura,
This was very helpful considering how much of a novice I am and I still got through it understanding what you were doing. In fact I even spotted a problem with your calculations. Your final calculated sum is the division of one sum by another, but what is really needed here, in most cases, is a sum across rows.
I’m doing a calculation that is not the same when done as a sum calculation vs. the sum of the row calculations. In this case it is Hours * Rate in each row, and then a sum of the rows that I need. The rates can not be summed and then multiplied by the sum of the hours to get the number I need.
So what is the formula (or the code) to create a sum across rows in this XSLT code?
Tom
Hi Laura,
Thank you for your earlier response. I found another option that might be helpful to you and your blog readers. When editing the 2010 XSLTListViewWebPart from the ribbon go under the ‘Design’ tab. To the left-most part you should see two check boxes. One of them says ‘Totals Row’. This works similarly to the row you used in the video. Unfortunately my calculations involve dates and I don’t know how to do it in XSLT.
If you have an idea I’ll be glad to hear it.
Regards,
Martin
You rocks Laura !…..
Thanks for sharing this cool stuff…
Rao.
is this video dead?
Hi Chris,
No the video is working fine. You might want to check and make sure it’s not being blocked.
Thanks,
Natasha
Hi laura,
I have problem with a column of a doc lib that is a calculated column with this formula:
=CONCATENATE(”“)
Thi show a printer icon and print a report in reporting services with parameters, and i made this with the help of path to sharepoint experience.
The problem is this code is hosted in a data view web part and it doesn’r render the code and give me a bad text result.
I have moss 2007 and i ask you how can i translate or modify this code to be rendered correctly?
Thanks in advance
Daniele
Daniele,
Sorry I don’t know how to do that. Since I don’t know how to do it, I’d go about it another way:
create a hyperlink field in your list
create a workflow that constructs the proper hyperlink, and have the workflow write it to that new hyperlink field.
Laura maybe you can help me with this. I have a list that I am able to group by Assigned To and then by Project Designation. I am able to get a count for the total number of task assigned to that person by using this code . I am able to get the Total (sum of hours) from all users. I am able to get the sums for all the task (sum of hours) under each of the Project groups under that user. However I am unable to get the sum of all those task that are assigned to that person. I know that there has to be some kind of code that I can use (like the count) to get the sum of the column for each person but I do not know it and can’t seem to find it.
Hi Laura,
i am attempting to create calculated colums, and am using Sharepoint Designer 2010. However, when I select ‘Show column summary’ a just get an empty row appear at the bottom of the web part, with nothing in it. Cannot see ‘count’ in any of the columns, hence am not sure where to edit the formula in the coding.
Could you maybe help me with this?
Kind Regards,
Amin
For SP 2010 – i found a solution on MSDN
http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/3ee3d099-621d-42c6-b0c2-15522f757194/
Instead of creating a DVWP and picking your list, you will create a Data View but select “Empty Data View”. Then you can select your source and the columns you want to display. At this point the options for Grouping have the footer exposed. If you are not grouping then the footer is exposed on the “design” tab.
Once the footer is exposed you can now drag the column you want to sum and then right click on it and Edit formula shows. From here you can do a Sum function.
Hope this helps.
Here’s my screencast on how to create a data view web part versus XSLT list view web part in 2010: http://www.youtube.com/watch?v=r2eODYHp73A
I have this weird problem and cannot figure this one out.
Using your design just mdofied for my need, I have two columns that need to be summed up then one is divided by the other then multiplied by 1000000.
I keep getting different errors no matter how a format it.
The two columns that are summed.
Sum Column 1 Works as expected
Sum Column 2 Works as expected.
Column 3 which contains the rows for calculation of column 1 divided by column 2 muliplied by 1000000
So now I need to take the sums of each and do the same column 1 sum divided by column 2 sum multiplied by 1000000 to get the sum to display in column 3 sum cell.
Make sense, need help terribly.
but when I try and finish the sum for Column 3 I get errors.
((sum($Rows/@Orig_x002e__x0020__x002b__x0020_0)) div (sum(($Rows/@Orig_x002e__x0020__x002b__x0020_))"$#,##0.;(#,##0.)"’)” />
I get expected Token errors, found item errors, invlaid number of arguments,missing expressions none of which make any sense the format should be correct.
This was the last attempt
Thanks for any help or direction
Sry Didn’t post
////
Nice Job, loved the hotel joke! I have been looking for something like this, and you can get your totals by groups as long as it is grouped that way.
Laura,
You just saved me! I have been looking everywhere on how to create a column on an external content type list in SharePoint 2010, at least with this process I am now able to create views of the calculations. Laura, have you tried graphing on a formulated column? Since the data is not stored wonder if I will run into problems?
thanks,
Nick
Graphing? The chart web part can connect to BCS data, which you can try out.