#### 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:

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:

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….

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) Googled “SharePoint total calculated column”
- 2) Said it couldn’t be done
- 3) Took a nap
- 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!

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”.

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

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)" />

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!

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!

- Share This Post:
- Short Url: http://wblo.gs/aA4

## Comments on this entry:

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

Thanks!

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

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)

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)

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)

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)

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)

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

"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)

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

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

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?