Summing Calculated Columns in DVWPs
Every blog post I’ve read says it can’t be done, but now I beg to differ! Today I came up with a way to sum calculated columns in SharePoint Data View Web Parts (DVWPs). It has been bugging me for a long time that I couldn’t do this, and it especially bugged me that everyone said that you just can’t do it. I don’t like the word “can’t” when it comes to programming. Usually it just means “I can’t figure it out”.
My gut told me that I just wasn’t thinking about the XSL the right way, and that it had nothing to do with SharePoint, and that hunch turned out to be right. Instead of trying to find the answer in a SharePoint context, I decided to just look in an XSL context. Sure enough, I turned up an old post with the answer. (It’s the SECOND EXAMPLE. I fiddled with the first one and couldn’t figure it out.)
Here’s the dealie-bob. What you need to do is create a new recursive template in your DVWP. In my example, I wanted to do a pretty standard sales pipeline calculation. For each potential Project in our Pipeline list we might sell, there is a Value (USD we could earn for the project). Each Project is in a particular Stage in the sales process. Each Stage has an associated Likelihood. We maintain the Likelihood for each Stage in a second list called Opportunity Stages so that we can change those values separately if need be.
So, for each Project, the Adjusted Value = Value * Likelihood, and we want to sum that calculated amount at the bottom of the list. In my footer, I have a table detail element like so:
<td align="right"> <xsl:variable name="TotalAdjustedValue"> <xsl:call-template name="TotalAdjustedValue"> <xsl:with-param name="Rows" select="$Rows"/> </xsl:call-template> </xsl:variable> <xsl:value-of select="format-number($TotalAdjustedValue, "$#,##0.;-$#,##0.")"/> </td>
The TotalAdjustedValue template looks like this:
<xsl:template name="TotalAdjustedValue">
<xsl:param name="Rows"/>
<xsl:choose>
<xsl:when test="$Rows">
<xsl:variable name="FirstRow" select="$Rows[1]"/>
<xsl:variable name="TotalAdjustedValue">
<xsl:call-template name="TotalAdjustedValue">
<xsl:with-param name="Rows" select="$Rows[position()!=1]"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="Likelihood" select="/dsQueryResponse/Opportunity_Stages/Rows/Row[@Title = $FirstRow/@Stage]/@Opportunity_x0020_Likelihood"/>
<xsl:value-of select="($FirstRow/@Value * $Likelihood) + $TotalAdjustedValue"/>
</xsl:when>
<xsl:otherwise>0</xsl:otherwise>
</xsl:choose>
</xsl:template>
You could pass any nodeset into the TotalAdjustedValue template, so you could use it to calculate subtotals for groups as well. For instance, if you use the ddwrt:NameChanged function to show subtotals for each Client, you could call it like this:
<xsl:variable name="TotalAdjustedValue">
<xsl:call-template name="TotalAdjustedValue">
<xsl:with-param name="Rows" select="$Rows[@Client_x0020_Name = current()/@Client_x0020_Name]"/>
</xsl:call-template>
</xsl:variable>
UPDATE 2009-06-09: As requested in the first comment below, here’s a screenshot of how this might end up looking (client names redacted). Note that the totals don’t equal the sum of the rows above, as I snipped out only a few rows.
Hi Marc, great article! I’m using WSS3 with SharePoint Designer 2007 and the info above lead me to change my calculations to be handled within the DVWP and everything looks great. One question…I have a column named Qty (numeric), a column named Value (currency) and a column named Total (currency, calculated by multiplying thw two previous columns).
How would I get a total value sum of all of the “Total” calculated values? I’ve tried every solution I’ve dug up on the web and keep getting either NaN or a blank result…
Brian:
Sorry – I missed your comment until now. See my series of posts on creating groups in DVWPs manually: Showing Subtotals in a DVWP, Sorted by the Subtotals http://sympmarc.com/?s=Showing+Subtotals+in+a+DVWP%2C+Sorted+by+the+Subtotals+
I think that there are some things there that may help.
M.
M.
Hi Marc,
I am quite new to sharepoint designer and have been pulling my hair out trying to figure out why the sums of my calculated number fields are not displayed when the value is over 1000. Any advice would be be greatly appreciated.
Liz
Liz:
It’s probably the commas. If you are in SharePoint 2010, there’s a raw format you can use that has no commas. Add a period after the column name, e.g., “@MyColumnName.”.
M.
Hello, I am trying to divide a grouped column total by the full total. Can anyone tell me how to do that?
Wendi:
The basic idea is that you sum the column for the two rowsets and then divide. To get the sum of the group, use a filter in square brackets on the entire $Rows rowset. It’ll look something like:
sum($Rows[@groupingcolumnname = curent()/@groupingcolumnname]/@columntototal)
M.
Hi Marc,
I have a list, and now I want to show sum and average for one columns Gruop by Survey number
think you have 2 Columns
“Survey Number” and “Total computer”
GrouBy Survey Number for each Group I want to show Sum of “Total Computer”
SN001 25
SN001 75
SN002 20
SN002 30
SN001 Sum = 100 Percentage = 100%
Item 1 Percentage = 25%
item 2 Percentage = 75%
SN002
Sum = 100 Percentage = 100%
Item 1 Percentage = 40%
item 2 Percentage = 60%
How can I do that please help me
Guru
Guru:
I can’t write the code for you to do this, but it looks like the techniques in this post should help.
M.
Hi Marc,
Is there any other way instead of changing the Currency column to Number field for summing the column values. Actually i wanted to pass the sum value to Fusion charts from the node-set. I’m getting count as it is easy to get. But when it tried for currency field it is taking it as text field and its giving output as NaN. So any suggestions using xslt functions can we get the sum of currency field.
You can definitely sum currency fields. It’s impossible to tell you exactly what to do, but you may need to parse out the values in your XSL.
M.
even if i parse the column values one by one i cannot sum it with the xslt function, as it requires node-set as parameter for calculation. i tried to get an expression for sum, but of no use.
this is the expression i used: even i put the period after the variable.
can you suggest better expression for this?
Sorry, but I didn’t get the XSL; it doesn’t make it through to WordPress.
I’d suggest that you post the full details to one of the public forums. Ping me the link if you’d like me to take a look.
M.
Marc,
Is there anyway to Summing Calculated Columns without having to rely on SPD? Company policy doesn’t allow users to use SPD.
Thanks,
Kathy
Kathleen:
Not that I can think of. Export to Excel?
M.
Darn, thanks Marc.