0

I would like to sum some values using XSLt V 1.0 but they are treated as strings. A sample of the xml is below as is my sample xslt. Should I change the data type in the underlying SQL view? I have tried various number data types but I need to retain the decimal or money format as this is supposed to be a total of dollars. Is there a way of using perhaps a variable in XSLT that will allow me to cast this string as a number? I have tried to convert using number() but I still get NaN. Don't know too much about xslt and would be very grateful for any help or if you can point me to relevant resource.

     <?xml version="1.0" encoding="utf-8"?>
    <xml>
      <object>
        <objectid>183382</objectid>
        <objectnumber>Test00001237</objectnumber>
        <lccs>
          <lcc>
            <groupname>Costs: Acquisition planning</groupname>
            <userfieldgroupid>9</userfieldgroupid>
            <userfieldname>Concept development hours</userfieldname>
            <userfieldid>42</userfieldid>
            <fieldvalue>1</fieldvalue>
            <depreciationlife>25</depreciationlife>
            <costs>46</costs>
          </lcc>
          <lcc>
            <groupname>Costs: Acquisition planning</groupname>
            <userfieldgroupid>9</userfieldgroupid>
            <userfieldname>Concept assessment hours</userfieldname>
            <userfieldid>43</userfieldid>
            <fieldvalue>.25</fieldvalue>
            <depreciationlife>25</depreciationlife>
            <costs>11.5</costs>
          </lcc>
        </lccs>
      </object>
    </xml>

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:js="urn:custom-javascriptscript" exclude-result-prefixes="msxsl js">
<xsl:output method="html" omit-xml-declaration="yes" doctype-public="-//W3C//DTD HTML 4.01 Transitional//EN" doctype-system="http://www.w3.org/TR/html4/loose.dtd" indent="yes"/>
<xsl:template match="/xml/object">
<html>
<head>
</head>
<body>
    <table>
    <tr style="font-weight:bold" >
    <td>Item</td>
    <td>$</td>
    </tr>
    <xsl:for-each select="lccs/lcc"> 
    <xsl:choose>
    <xsl:when test="normalize-space(userfieldid)='42'">
    <tr>
    <td>
    <xsl:value-of select="normalize-space(fieldvalue)"/>
    </td>
    <td>            
    <xsl:value-of select="normalize-space(number(costs))"/>
    </td>
    </tr>
    </xsl:when> 
    <xsl:when test="normalize-space(userfieldid)='43'">
    <tr>
    <td>
    <xsl:value-of select="normalize-space(fieldvalue)"/>
    </td>
    <td>                                
    <xsl:value-of select="normalize-space(number(costs))"/>
    </td>
    </tr>   
     <tr>
    <td>Subtotal</td>
    <td><xsl:value-of select="sum(//costs)"/></td>
    </tr>       
    </xsl:when>             
    </xsl:choose>       
    </xsl:for-each>             
    </table>
     </body>
</html>
</xsl:template> 
 </xsl:stylesheet>
Mathias Müller
  • 22,203
  • 13
  • 58
  • 75
user3471259
  • 215
  • 1
  • 15
  • Irrespective the issue you're having: if it is currency, [floating point arithmetics isn't the way to go anyway](http://stackoverflow.com/questions/14741009/perl-for-loop-going-haywire/14741188#14741188). – Jens Erat Mar 29 '14 at 07:30
  • Please post code that would allow us to reproduce your problem. Applying your stylesheet to your input does **not** produce a NaN result anywhere. – michael.hor257k Mar 29 '14 at 07:42
  • As a sidenote, both your subtotals calculate the same value, at least one of the `//costs` should be a relative path. – Tobias Klevenz Mar 29 '14 at 09:01
  • Thanks very much for your responses. I have checked the xslt and I still see the NaN result. Have reentered the code. I would add a pic to show the NaN result that I get but don't have enough points. Perhaps the problem is the "browser" which is in a database application I am using. It generates the xml from my sql view and I transform it by uploading my xslt to create html pages for viewing within the application. I am sorry but don't know what it is. I also don't know how to render this in another browser such as IE to check if it gives correct result there. I see I had two same subtotals – user3471259 Mar 30 '14 at 03:29
  • "*I have checked the xslt and I still see the NaN result.*" That's possible, but the problem is not with your code: http://xsltransform.net/3Nqn5Yh – michael.hor257k Mar 30 '14 at 08:37
  • Hi thanks very much again for your help and I am glad to see that it is not my code at fault. Can you suggest what might be happening here and why I persistently get the NaN? I need to solve this issue as I am developing a report that will rely on this data being summed etc. – user3471259 Mar 31 '14 at 00:06
  • Sorry, but I don't have a clue. Literally. You haven't given as a clue how to reproduce this problem. I don't know how to solve a problem I cannot reproduce. – michael.hor257k Mar 31 '14 at 00:56
  • Thanks so much for your response in any case. I am afraid I am in the dark! I will try to get better tools to help me test and render the xml/xslt outside my database application. – user3471259 Mar 31 '14 at 04:57
  • Hi more information -I am using an IE control in a database application to render a data view. The underlying data source is SQL and the costs data type is decimal. I suspect the issues is with the xpath and that my result is the individual costs summed with themselves. If I use I get: Result - Item $ 1 46 0.25 11.5 Subtotal NaN NaN If I just use directly on costs I get the numbers, but not summed! so back to where I started. Result- Item $ 46 0.25 11.5 Subtotal 46.00 11.50 – user3471259 Apr 03 '14 at 23:34

1 Answers1

1

I need to retain the decimal or money format as this is supposed to be a total of dollars.

In general, it's best for input data that needs to be treated numerically to contain only digits, decimal point and minus sign. You can always reformat the result as decimal/currency at the output. If you are unsure about the currency used, include it in a separate element or as an attribute.

michael.hor257k
  • 113,275
  • 6
  • 33
  • 51
  • Thanks very much for this advice. I understand that I can reformat the data as currency. I was trying -a bit desperately -to "fix" my problem by changing the underlying data type as I am more comfortable with sql than xslt. – user3471259 Mar 30 '14 at 03:31
  • There are practically no data types in XML, unless you use an additional schema document - and even then you would need a schema-aware processor (read: XSLT 2.0) to take advantage of it. – michael.hor257k Mar 30 '14 at 08:32