SharePoint - Summing Calculated Columns By Groups (DVWP)

I had a problem… okay.. okay.. so I have many problems… but let’s focus on one in particular or this blog post would never end… okay? Thank you….

So, I had an electronic timesheet where users entered hours for each day of the week. It also had a “Week Total” column which was a calculated column of the sum. The calculated column looked like this:

image

Pretty easy.. nothing spectacular. So, what’s the problem?

WELL……………….. There is a row in the timesheet for each task a person worked on in a given week. So, if you worked on 4 tasks, you would have 4 rows of data, and 4 week totals for that week:

image

This is all fine and dandy, but I want to know what the total was for the entire week. Yes.. I realize the answer is 24 from my example… I mean, I know how to add! I just want SharePoint to display it for me for the executives (we all know, they have math problems).  You may be thinking, hey genius (in a sarcastic tone of course), why don’t you just go to the view and total on the “Week Total” field. What a brilliant idea! Why didn’t I think of that… let’s go to the view and do just that….

image

Ohhhhhh… you can’t total on a Calculated Column.. it’s not even an option…  Yeah… I had the same moment. So, what do you do?

Well… what do you think I did?

  1. 1) Googled “SharePoint total calculated column”
  2. 2) Said it couldn’t be done
  3. 3) Took a nap
  4. 4) Asked the question on twitter?

The correct answer of course is number 4… followed by number 3… although I may have told my boss number 2 so that I look more brilliant than I am? It’s safe to say I did NOT try to find the solution on my own doing step 1… that would be just WAY to easy… So, anyway, I posted the question on Twitter and it turns out several people had suggestions from using jQuery to using DVWPs. I tend to be a big fan of the DVWP except for the disgusting process of deploying them to another farm.. ugh… just shoot me…. so, that is the solution I went with. Laura Rogers (@WonderLaura) has a super duper easy to follow video on the subject over at EndUserSharePoint.com:

SharePoint: Displaying Calculated Column SUMS in a View (Screencast)

Laura’s video was very easy to follow and was ALMOST exactly what I needed. She does a great job walking you through every step of summing up a calculated field which was PART of my problem. The other part was my list is grouped by date!

image

So, I wanted to see for a given week, the summed “Week Total” of hours. Laura got me on the right track with her video and I dug a little deeper into the DVWP to accomplish my task. So, here are the steps you follow:

1. Click on the "chevron” (I didn’t know it was actually called that until I heard Laura say it).. I always call it the “little-button-in-the-top-right-corner-with-the-greater-than-sign”.. but “chevron” is much shorter. So, click on the chevron, click on “Sort and Group”. Then Add the field you want to group by, in my example it is the “Monday Date” of the timesheet entry. Make sure to check the check boxes for “Show Group Header” AND “Show Group Footer”. Click “OK”.

image

The view now shows the count of each grouped set of data:

image

Interesting, this looks very similar to Laura’s video… right? So, let’s take a look at the code for the Count:

Count : <xsl:value-of select="count($nodeset)" />

Wow, also very similar… except in Laura’s video it looks like:
Count : <xsl:value-of select="count($Rows)" />

So.. the only difference is that instead of $Rows we have $nodeset. It turns out the $nodeset will go through each Row in the group just like $Rows goes through each row in the entire view. So, using the exact same logic as in Laura’s blog except replacing $Rows with $nodeset we get the functionality of being able to sum up the values for a group. So, I want to replace “Count: #” with the total hours, this is done using the following changes to the above code:

Week Total : <xsl:value-of select="sum($nodeset/@Monday)+sum($nodeset/@Tuesday)
+sum($nodeset/@Wednesday)+sum($nodeset/@Thursday)+sum($nodeset/@Friday)
+sum($nodeset/@Saturday)+sum($nodeset/@Sunday)" />

Our final output has the summed hours for each group!

image

So… long story short… follow Laura’s blog, then group your list, then replace “$Rows” with “$nodeset”. One caveat, this will not work if you group by a person field. For some reason the person field does not go through each row in the group. I haven’t dug into this much yet. Maybe if I find some time… whatever that is…

Anyway, Laura did all the work, I just took it one small step forward… as always, feel free to leave any additional insights you may have. We’re all learning here!

posted @ Tuesday, March 30, 2010 10:50 PM
Print

Comments on this entry:

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Matt B. at 3/30/2010 11:06 PM
Gravatar
With all of this hard work done for me, I can now revisit a date-time totaling problem I had months ago. I knew you could do it, just didn't know how. Now, thanks to you and Laura, I absolutely know how...

Thanks!

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Richard Harbridge at 3/31/2010 11:03 AM
Gravatar
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/

Awesome post though with lots of great detail,
Richard Harbridge

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Richard van der Veer at 4/9/2010 2:19 PM
Gravatar
Question.

Adding Columns works fine. Just as it said.
Week Total : <xsl:value-of select="sum($nodeset/@Monday)+sum($nodeset/@Tuesday)
+sum($nodeset/@Wednesday)+sum($nodeset/@Thursday)+sum($nodeset/@Friday)
+sum($nodeset/@Saturday)+sum($nodeset/@Sunday)" />

But what to do if you want to multiply the columns in the and display it in the same grouping.

I've got an order administration. Grouped on Order ID. One order can hold 1 or more Items.
At the footer of the group I want the total Order cost.

I tried this but get errors
Total cost : <xsl:value-of select="sum($nodeset/@Number_of_Item) * sum($nodeset/@Item_Price ) />

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Richard at 4/9/2010 7:20 PM
Gravatar
I don't think its possible in a simple way. Anybody an idea how to realize this list an grouping is SharePoint Designer 2007? The (overall) Total is optional. But the SubTotal is a must-have

Grp....Name....Unts....Price....Amount
Or1....Poko......0002...0010...0020
Or1....Bike.......0020...0001...0020
SubTotal...0040

Or2.....Pent......0006...0010....0060
Or2.....Gumf.....0002...0015....0030
Or2.....Poko......0001...0010....0010
Or2.....Sekl.......0001....0100....0100
SubTotal....0200

Or3.....Sekl......0001.....0100....0100
SubTotal....0100

Total.........0340

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Matt Bramer at 6/21/2010 9:37 PM
Gravatar
To answer your question Richard, you'd want your XSL to look like this:

SubTotal : <xsl:value-of select="sum($nodeset/@Units) * ($nodeset/@Price)" />

and for Total:

Total : <xsl:value-of select="sum(/dsQueryResponse/Rows/Row/@Units) * (/dsQueryResponse/Rows/Row/@Price)" />

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by sullisnyc at 8/2/2010 2:45 PM
Gravatar
I've used a similar solution but am running into issues when paging. The totals are bound by paging issues.

This is what I applied:
http://techtrainingnotes.blogspot.com/2008/11/sharepoint-group-by-on-more-than-2.html

Any thoughts?

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Radha at 9/9/2010 3:49 PM
Gravatar
Is there anyway to get this total value of a column in sharepoint list into SSRS(2005). Detail Info: I have 100 questions(columns) that needs a total for survey questions.

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Enrique at 4/6/2011 10:29 AM
Gravatar
Hello

"this will not work if you group by a person field. For some reason the person field does not go through each row in the group. I haven’t dug into this much yet. Maybe if I find some time… whatever that is"

Did you found out how to do this with the Person or Group field??? (Pleeassee say yes) i could really use this

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Izergill at 5/23/2011 4:25 AM
Gravatar
Thanks a lot!!!! This was very helpufl!

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by NK at 7/7/2011 8:44 AM
Gravatar
how do you create column monday day. whats the logic behind it.

# re: SharePoint - Summing Calculated Columns By Groups (DVWP)

Left by Jim Kirk at 12/13/2011 7:17 AM
Gravatar
The most ridiculous thing in this world is for Microsoft to have all this money, software engineers... and not include an easy way sum SUM a calculated Column in INFOPATH/Sharepoint.

Designing a CAR with no room for a driver seat.. .we'll just push it from behind. What are these people retarded? Is there some PATENT for summing a column and MS didn't want to pay the Licencing? WTF..

Hey Bill G., it's time to fire some people, don't you think?

Anyone know a work around for INFO PATH?

I want to make a simple Itemized list of Good and Service; Using a Growing Table.
Each Row Calculates its subtotal by multiplying the price of the item by the QTY.

INFOPATH won't sum the column. Is the a way to create a Variable or write the values to a temp file, then sum those values or variable?
SHIFT command?

Your comment:



(not displayed)

 
 
 
 
 

Live Comment Preview:

 
«August»
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456