Sum of column based on statement

Question

I have a table in a Sharepoint 2010 data view with a column that is calculated as follows:

<td>
<xsl:variable name="CurrentYear" select="ddwrt:FormatDateTime(string(ddwrt:Today()),1033,'yyyy')"/>
<xsl:variable name="YearOfWork" select="$thisNode/@Year"/>
    <xsl:choose>
     <xsl:when test="number($YearOfWork) = number($CurrentYear) - 1">
       <xsl:value-of select="number(@_x004a_an116)"/>
     </xsl:when>
     <xsl:when test="number($YearOfWork) + 1 = number($CurrentYear) - 1">
       <xsl:value-of select="number(@_x004a_an1)"/>
     </xsl:when>
     <xsl:when test="number($YearOfWork) + 2 = number($CurrentYear) - 1">
       <xsl:value-of select="number(@_x004a_an1)"/>
     </xsl:when>
     <xsl:otherwise>
       <xsl:value-of select="number(0.00)"/>
     </xsl:otherwise>
    </xsl:choose>
</td>

At the bottom of the column I would like to get the total of all the values in the column. Is there a way to do this?

Thanks in advance!

Answer 1

You can pre-process the input into a node-set and apply multiple transforms to it. This does require an extension, but it is widely available.

I used this input as an example:

<vals>
    <val Year="2016" _x004a_an116="100" _x004a_an1="150"/>
    <val Year="2015" _x004a_an116="200" _x004a_an1="250"/>
    <val Year="2014" _x004a_an116="300" _x004a_an1="350"/>
    <val Year="2013" _x004a_an116="400" _x004a_an1="450"/>
    <val Year="2012" _x004a_an116="500" _x004a_an1="550"/>
</vals>

Then this stylesheet to pre-process it into the $vals variable then output both the rows and a final total:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:exsl="http://exslt.org/common"
                exclude-result-prefixes="exsl">

    <xsl:output indent="yes" omit-xml-declaration="yes"/>

    <xsl:template match="/vals">
        <xsl:variable name="CurrentYear" select="2016"/>
        <xsl:variable name="vals">
            <xsl:for-each select="val">
                <tr>
                    <td><xsl:value-of select="@Year"/></td>
                    <td>
                        <xsl:variable name="YearOfWork" select="number(@Year)"/>
                        <xsl:choose>
                            <xsl:when test="$YearOfWork = $CurrentYear - 1">
                                <xsl:value-of select="@_x004a_an116"/>
                            </xsl:when>
                            <xsl:when test="$YearOfWork + 1 = $CurrentYear - 1">
                                <xsl:value-of select="@_x004a_an1"/>
                            </xsl:when>
                            <xsl:when test="$YearOfWork + 2 = $CurrentYear - 1">
                                <xsl:value-of select="@_x004a_an1"/>
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="0.00"/>
                            </xsl:otherwise>
                        </xsl:choose>
                    </td>
                </tr>
            </xsl:for-each>
        </xsl:variable>

        <table>
            <xsl:copy-of select="$vals"/>
            <xsl:call-template name="sum">
                <xsl:with-param name="rows" select="exsl:node-set($vals)"/>
            </xsl:call-template>
        </table>
    </xsl:template>

    <xsl:template name="sum">
        <xsl:param name="rows"/>
        <tr>
            <td>Total</td><td><xsl:value-of select="sum($rows/tr/td)"/></td>
        </tr>
    </xsl:template>

</xsl:stylesheet>