BizTalk Mapper – resolving decimal sum and separator issues, Keyed Cumulative Sum continued

As a continuation of my Keyed Cumulative Sum post, where I did a key based summation using an xslt call template in a BizTalk map I got two main points of feedback worth repeating and showing.

Questions

1. If I use Sum on a decimal number it gives me all sort of weird decimals in the Sum although it shouldn’t; ie a sum of 1.1 and 1.1 could result in 2.199999999999. How do I solve that?
2. What if I am in Sweden (or Germany or some other country) and I want to use a comma (,) as a decimal separator instead of a period (.). How can I do that?

Answers

1. You can use the xslt format-number function to remove erroneous decimal digits that are the result of using Sum on a decimal number.
2. You can use the xpath translate function to replace a period with a comma. However this does not conform to an xsd:decimal, so make sure that the target schema does not have this field defined as a decimal if you wish to be compliant.

This will make the outlook of the xslt instead look like this (code is edited to improve readability):

<xsl:template name="OutputSum">
  <xsl:param name="param1" />
  <xsl:param name="param2" />
  <xsl:element name="Compensation_Amount">
    <xsl:variable name="SumOfNodes" select="//row[Compensation_SubCode=$param1 and   
                                            Compensation_Level=$param2]/Compensation_Amount)"/>
    <xsl:variable name="FormattedSum" select="format-number($SumOfNodes,'0.00')"/>
    <xsl:value-of select="translate($FormattedSum, '.', ',')" />
  </xsl:element>
</xsl:template>

The use of several variables are for maintainability only, and are not required. You could jam it all into the value-of select statement if you really want to.

/Johan

Blog Post by: Johan Hedberg