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, &quot;$#,##0.;-$#,##0.&quot;)"/>
 </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.
Capture3