Re: [xsl] Calculate balance

Subject: Re: [xsl] Calculate balance
From: Jeni Tennison <jeni@xxxxxxxxxxxxxxxx>
Date: Mon, 11 Mar 2002 14:08:52 +0000
Hi Shabbir,

> I want to put calculated value in the Blance column of the table. I
> am expalining it in the natural laguage
>
> x=openingbalance
> balance[1]=x-debit[1]+credit[1]
> balance[2=balance[1]-debit[2]+credit[2]
> balance[3]=balance[2]-debit[3]+credit[3]
> ...
> ...
>
> can any one give me the solve.

Well, given that you're currently on a row element, you can work out
the difference between this balance and the previous balance by
looking at the amount element. You could use:

  <xsl:choose>
    <xsl:when test="amount/@type = 'D'">
      <xsl:value-of select="amount * -1" />
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="amount" />
    </xsl:otherwise>
  </xsl:choose>

[You could use a one-line path:

   amount * (((@type = 'C') * 2) - 1)

but it's a bit difficult to understand what it's doing, so I won't in
these examples.]

To work out the balance for a row, then, you can look at whatever the
balance was for the previous row, and add the difference that you've
calculated for this row to it. I'd use a template in 'balance' mode
for working out this balance:

<xsl:template match="row" mode="balance">
  <xsl:variable name="previousBalance">
    ...
  </xsl:variable>
  <xsl:variable name="difference">
    <xsl:choose>
      <xsl:when test="amount/@type = 'D'">
        <xsl:value-of select="amount * -1" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="amount" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:variable>
  <xsl:value-of select="$previousBalance + $difference" />
</xsl:template>

You can work out the previous balance, you can apply templates to the
immediately preceding row in balance mode. If there isn't a preceding
row (i.e. this is the first row) then you can look at the
openingbalance element instead:

<xsl:template match="row" mode="balance">
  <xsl:variable name="previousBalance">
    <xsl:choose>
      <xsl:when test="preceding-sibling::row">
        <xsl:apply-templates select="preceding-sibling::row[1]"
                             mode="balance" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="../../openingbalance" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:variable>
  <xsl:variable name="difference">
    <xsl:choose>
      <xsl:when test="amount/@type = 'D'">
        <xsl:value-of select="amount * -1" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="amount" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:variable>
  <xsl:value-of select="$previousBalance + $difference" />
</xsl:template>

Then to work out the balance for a particular row, just apply
templates to it in 'balance' mode:

  <tr>
    <td><xsl:value-of select="ledgerdate"/></td>
    <td><xsl:value-of select="voucher"/></td>
    <td><xsl:value-of select="costcenter"/></td>
    <td><xsl:value-of select="lccode"/></td>
    <td><xsl:value-of select="description"/></td>
    <xsl:call-template name="amountplace"/>
    <xsl:apply-templates select="." mode="balance" />
  </tr>

This will get a bit time-consuming if there are lots of rows. You
could make it into a tail-recursive template to save a bit of time:

<xsl:template match="row" mode="balance">
  <xsl:param name="balance" select="../../openingbalance" />
  <xsl:variable name="difference">
    <xsl:choose>
      <xsl:when test="amount/@type = 'D'">
        <xsl:value-of select="amount * -1" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="amount" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:variable>
  <xsl:choose>
    <xsl:when test="preceding-sibling::row">
      <xsl:apply-templates select="preceding-sibling::row[1]"
                           mode="balance">
        <xsl:with-param name="balance"
                        select="$balance + $difference" />
      </xsl:apply-templates>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$balance + $difference" />
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

Or if performance is really an issue, you could take a different
approach in which you step through the rows one by one, with a
template that applies templates to the next row and passes on the
balance from each step. Let us know if you want to see an example of
how that would work.

Cheers,

Jeni

---
Jeni Tennison
http://www.jenitennison.com/


 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list


Current Thread