Using SUM in XSL concatenates value instead of adding it in Excel template

Question

I am having an issue where the fields I am trying to sum is getting concatenated in XSL. I have tried the solutions given at the following threads but none of them is working for me. The requirement is to create the excel template for Report where the summation field needs to be performed using IF condition i.e. when PEOPLEGROUP is in 'CF1000' or 'CF1100' then output should be the summation of HEADCOUNT field but for some reason the output is displaying side by side rather sum.

Given below is the sample code and the expected value is 508 but it gives me 193315.

Sample data:

<?xml version="1.0" encoding="UTF-8"?>
<DATA_DS>
   <G_1>
      <PG_BUSINESSUNITNAME>Conventional</PG_BUSINESSUNITNAME>
      <G_2>
         <PG_DEPARTMENTLEVEL1>Conventional Field</PG_DEPARTMENTLEVEL1>
         <G_3>
            <PG_DEPARTMENTLEVEL2>Conventional Field Operations - Reporting</PG_DEPARTMENTLEVEL2>
            <G_4>
               <S_0>0</S_0>
               <PG>00001.SVOPR</PG>
               <S_5>3.00000001521557E14</S_5>
               <PEOPLEGROUP>SVOPR</PEOPLEGROUP>
               <HEADCOUNT>3.0</HEADCOUNT>
            </G_4>
         </G_3>
         <G_3>
            <PG_DEPARTMENTLEVEL2>Field Operations</PG_DEPARTMENTLEVEL2>
            <G_4>
               <S_0>0</S_0>
               <PG>00001.CF1000</PG>
               <S_5>3.00000001521557E14</S_5>
               <PEOPLEGROUP>CF1000</PEOPLEGROUP>
               <HEADCOUNT>193.0</HEADCOUNT>
            </G_4>
            <G_4>
               <S_0>0</S_0>
               <PG>00001.CF1100</PG>
               <S_5>3.00000001521557E14</S_5>
               <PEOPLEGROUP>CF1100</PEOPLEGROUP>
               <HEADCOUNT>315.0</HEADCOUNT>
            </G_4>
            <G_4>
               <S_0>0</S_0>
               <PG>00001.CF1200</PG>
               <S_5>3.00000001521557E14</S_5>
               <PEOPLEGROUP>CF1200</PEOPLEGROUP>
               <HEADCOUNT>23.0</HEADCOUNT>
            </G_4>
         </G_3>
      </G_2>
   </G_1>
</DATA_DS>

The Sample code is given below

<xsl:for-each select=".//G_4">
    <xsl:variable name="ALB" select="'CF1000 CF1100'" />
    <xsl:variable name="PGALB" select="PEOPLEGROUP" />
    <xsl:if test="contains(concat(' ', $ALB, ' '),concat(' ',$PGALB, ' '))">
        <xsl:value-of select="sum(HEADCOUNT)" />
    </xsl:if>
</xsl:for-each>

Any help will be appreciated.

The context node on each iteration is a `G_4`. Your XSL will output the "sum" of one value, namely its own `HEADCOUNT`. The result is the concatenation of the individual values. To use `sum()` the context would have to be one level higher, i.e. `G_3`, and the argument to `sum()` would need to be a filtered nodeset. I don't have time right now to write a real answer. — Jim Garrison, Feb 01 '18 at 22:48
Thank you Jim Garrison for your response. I am trying this scenario in Excel Template, could you please suggest how this can be done in excel template.Below is the code I am using in Excel Template XDO_?XDOFIELD4? — , Feb 02 '18 at 16:04

Daniel Haley · Answer 1 · 2018-02-02T23:03:10.533

Like @jim-garrison said in a comment, the context is G_4 and each G_4 only has one HEADCOUNT so there isn't anything to sum. You just get a concatenation of each individual value.

Instead, consider creating an xsl:key and then doing the sum on the HEADCOUNT of the key.

Example...

XML Input

<DATA_DS>
    <G_1>
        <PG_BUSINESSUNITNAME>Conventional</PG_BUSINESSUNITNAME>
        <G_2>
            <PG_DEPARTMENTLEVEL1>Conventional Field</PG_DEPARTMENTLEVEL1>
            <G_3>
                <PG_DEPARTMENTLEVEL2>Conventional Field Operations - Reporting</PG_DEPARTMENTLEVEL2>
                <G_4>
                    <S_0>0</S_0>
                    <PG>00001.SVOPR</PG>
                    <S_5>3.00000001521557E14</S_5>
                    <PEOPLEGROUP>SVOPR</PEOPLEGROUP>
                    <HEADCOUNT>3.0</HEADCOUNT>
                </G_4>
            </G_3>
            <G_3>
                <PG_DEPARTMENTLEVEL2>Field Operations</PG_DEPARTMENTLEVEL2>
                <G_4>
                    <S_0>0</S_0>
                    <PG>00001.CF1000</PG>
                    <S_5>3.00000001521557E14</S_5>
                    <PEOPLEGROUP>CF1000</PEOPLEGROUP>
                    <HEADCOUNT>193.0</HEADCOUNT>
                </G_4>
                <G_4>
                    <S_0>0</S_0>
                    <PG>00001.CF1100</PG>
                    <S_5>3.00000001521557E14</S_5>
                    <PEOPLEGROUP>CF1100</PEOPLEGROUP>
                    <HEADCOUNT>315.0</HEADCOUNT>
                </G_4>
                <G_4>
                    <S_0>0</S_0>
                    <PG>00001.CF1200</PG>
                    <S_5>3.00000001521557E14</S_5>
                    <PEOPLEGROUP>CF1200</PEOPLEGROUP>
                    <HEADCOUNT>23.0</HEADCOUNT>
                </G_4>
            </G_3>
        </G_2>
    </G_1>
</DATA_DS>

XSLT 1.0

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:key name="headcount" 
    match="G_4[contains(concat(' ','CF1000 CF1100',' '),concat(' ',PEOPLEGROUP,' '))]" use="generate-id(ancestor::G_3)"/>

  <xsl:template match="/*">
    <xsl:for-each select="//G_3">
      <sum><xsl:value-of select="sum(key('headcount',generate-id(current()))/HEADCOUNT)"/></sum>      
    </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>

Output

<sum>0</sum>
<sum>508</sum>

I've never tried to use XSLT in an Excel template before, so I'm not sure what the limitations are. Maybe try something more simple like this (this would replace your existing xsl:for-each):

<xsl:for-each select="//G_3">
  <xsl:variable name="ALB" select="'CF1000 CF1100'" />
  <sum>
    <xsl:value-of select="sum(.//G_4[contains(concat(' ', $ALB, ' '),concat(' ',PEOPLEGROUP, ' '))]/HEADCOUNT)"/>        
  </sum>
</xsl:for-each>

Thank you Daniel Haley for your response. I am trying this scenario in Excel Template, could you please suggest how this can be done in excel template.Below is the code I am using in Excel Template XDO_?XDOFIELD4? — , Feb 02 '18 at 16:03
@mudireddydeepakreddy - please see my edit. hopefully this helps. — Daniel Haley, Feb 02 '18 at 16:35
Thank you Daniel Haley for your quick responses and suggestions. It looks working but the result is coming as the summation of all HEADCOUNT where PEOPLEGROUP is in 'CF1000', 'CF1100' (Irrespective of PG_DEPARTMENTLEVEL2) but it needs to be differentiated at PG_DEPARTMENTLEVEL2. Let us say from same XML If I do summation of PEOPLEGROUP is in 'CF1000','CF1100','SVOPR' then it should show result as 3 for PG_DEPARTMENTLEVEL2(Conventional Field Operations - Reporting) and 508 for PG_DEPARTMENTLEVEL2(Field Operations) but showing 511 for both departments. Any thoughts on this please — , Feb 02 '18 at 22:37
@mudireddydeepakreddy - I think in that case you would need to sum the `G_4` from each `G_3`. Please see my update. Again, I don't know how this works in Excel so I'm not sure how to test it. — Daniel Haley, Feb 02 '18 at 23:04
Thank you Daniel Haley for your help. The result is almost near to our requirement but not 100%. The output should print as 508 rather it is printing as 50800050000000000000000000000000000000000000000000000000000000(5.080005E+61). Could you please suggest — , Feb 05 '18 at 17:49

Using SUM in XSL concatenates value instead of adding it in Excel template

1 Answers1