Mapping

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s