2 dimensional grouping and sumation in xslt

Question

My input xml is like

<Reports>
  <Report>  
    <ReportHeader>
      <Name>ABC</Name>
      <ReportNo>123</ReportNo>
    </ReportHeader>
    <ReportLine>
      <ReportNo>123</ReportNo>
      <LineGroup>XYZ</LineGroup>
      <LineAmount>10</LineAmount>
    <ReportLine>
    <ReportLine>
      <ReportNo>123</ReportNo>
      <LineGroup>PQR</LineGroup>
      <LineAmount>20</LineAmount>
    <ReportLine>
    <ReportLine>
    <ReportNo>123</ReportNo>
      <LineGroup>XYZ</LineGroup>
      <LineAmount>30</LineAmount>
    <ReportLine>
  </Report>
  <Report>
    <ReportHeader>
      <Name>DEF</Name>
      <ReportNo>456</ReportNo>
    </ReportHeader>
    <ReportLine>
      <ReportNo>456</ReportNo>
      <LineGroup>IJK</LineGroup>
      <LineAmount>40</LineAmount>
    <ReportLine>
    <ReportLine>
      <ReportNo>456</ReportNo>
      <LineGroup>XYZ</LineGroup>
      <LineAmount>50</LineAmount>
    <ReportLine>
    <ReportLine>
      <ReportNo>456</ReportNo>
      <LineGroup>IJK</LineGroup>
      <LineAmount>60</LineAmount>
    <ReportLine>
  </Report>
</Reports>

My output xml is like

<NewReport>
  <Header>
    <Name>ABC</Name>
    <HeaderNo>456</HeaderNo>
  </Header>
  <Line>
    <LineGroup>XYZ</LineGroup>
    <Amount>40</Amount>
  </Line>
  <Line>
    <LineGroup>PQR</LineGroup>
    <Amount>20</Amount>
  </Line>
</NewReport>
<NewReport>
  <Header>
    <Name>DEF</Name>
    <HeaderNo>456</HeaderNo>
  </Header>
  <Line>
    <LineGroup>IJK</LineGroup>
    <Amount>100</Amount>
  </Line>
  <Line>
    <LineGroup>XYZ</LineGroup>
    <Amount>50</Amount>
  </Line>
</NewReport>

The XSL I am using is

<xsl:key name="KLinesByGroup" match="/Reports/Report/ReportLine" use="LineGroup"/>
<xsl:key name="KLinesByReportNo" match="/Reports/Report/ReportLine" use="ReportNo"/>
<xsl:template match="/">
  <xsl:for-each select="/ns2:Reports/ns2:Report">
     <xsl:variable name="HeaderReportNo"><xsl:value-of select="ReportHeader/ReportNo"/></xsl:variable>
     <Header>
       <Name><xsl:value-of select="ReportHeader/Name"/></Name>
       <HeaderNo><xsl:value-of select="ReportHeader/ReportNo"/></HeaderNo>
       <xsl:apply-templates select="key('KLinesByReportNo', $HeaderReportNo)[1]" mode="reportno-mode"/>
     </Header>
  </xsl:for-each>
</xsl:template>

<xsl:template match="ReportLine" mode="reportno-mode">
  <xsl:apply-templates select="key('KLinesByReportNo', ReportNo)[generate-id() = generate-id(key('KLinesByGroup', LineGroup)[1])]" mode="group-mode"/>
</xsl:template>

<xsl:template match="ReportLine" mode="group-mode">
  <Line>
    <xsl:value-of select="sum(key('KLinesByGroup', LineGroup)/LineAmount)"/>
  </Line>
</xsl:template>

But the output is not what I am expecting. The output I am getting is adding all the amounts at group level or line level, but not at line and group level. Can any one please help.

Thanks

Answer 1

This transformation:

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

 <xsl:key name="kLine" match="ReportLine"
  use="concat(../ReportHeader/Name, '+',
              ../ReportHeader/ReportNo, '+',
              ReportNo, '+',
              LineGroup
              )"/>

 <xsl:template match="node()|@*">
  <xsl:copy>
   <xsl:apply-templates select="node()|@*"/>
  </xsl:copy>
 </xsl:template>

 <xsl:template match=
    "ReportLine
      [not(generate-id()
          =
           generate-id(key('kLine',
                           concat(../ReportHeader/Name, '+',
                                  ../ReportHeader/ReportNo, '+',
                                  ReportNo, '+',
                                   LineGroup
                                  )
                          )
                          [1])
           )
      ]"/>

  <xsl:template match="LineAmount">
   <Amount>
     <xsl:value-of select=
     "sum(key('kLine',
              concat(../../ReportHeader/Name, '+',
                     ../../ReportHeader/ReportNo, '+',
                     ../ReportNo, '+',
                     ../LineGroup
                     )
             )
              /LineAmount
         )"/>
   </Amount>
  </xsl:template>

  <xsl:template match="ReportLine/ReportNo"/>

  <xsl:template match="/*"><xsl:apply-templates/></xsl:template>
  <xsl:template match="Report">
   <NewReport><xsl:apply-templates/></NewReport>
  </xsl:template>

  <xsl:template match="ReportHeader">
   <Header><xsl:apply-templates/></Header>
  </xsl:template>

  <xsl:template match="ReportNo">
   <HeaderNo><xsl:apply-templates/></HeaderNo>
  </xsl:template>

  <xsl:template match="ReportLine">
   <Line><xsl:apply-templates/></Line>
  </xsl:template>
</xsl:stylesheet>

when applied on the provided XML (corrected from several malformed tags) document:

<Reports>
  <Report>
    <ReportHeader>
      <Name>ABC</Name>
      <ReportNo>123</ReportNo>
    </ReportHeader>
    <ReportLine>
      <ReportNo>123</ReportNo>
      <LineGroup>XYZ</LineGroup>
      <LineAmount>10</LineAmount>
    </ReportLine>
    <ReportLine>
      <ReportNo>123</ReportNo>
      <LineGroup>PQR</LineGroup>
      <LineAmount>20</LineAmount>
    </ReportLine>
    <ReportLine>
    <ReportNo>123</ReportNo>
      <LineGroup>XYZ</LineGroup>
      <LineAmount>30</LineAmount>
    </ReportLine>
  </Report>
  <Report>
    <ReportHeader>
      <Name>DEF</Name>
      <ReportNo>456</ReportNo>
    </ReportHeader>
    <ReportLine>
      <ReportNo>456</ReportNo>
      <LineGroup>IJK</LineGroup>
      <LineAmount>40</LineAmount>
    </ReportLine>
    <ReportLine>
      <ReportNo>456</ReportNo>
      <LineGroup>XYZ</LineGroup>
      <LineAmount>50</LineAmount>
    </ReportLine>
    <ReportLine>
      <ReportNo>456</ReportNo>
      <LineGroup>IJK</LineGroup>
      <LineAmount>60</LineAmount>
    </ReportLine>
  </Report>
</Reports>

produces the wanted, correct result:

<NewReport>
   <Header>
      <Name>ABC</Name>
      <HeaderNo>123</HeaderNo>
   </Header>
   <Line>
      <LineGroup>XYZ</LineGroup>
      <Amount>40</Amount>
   </Line>
   <Line>
      <LineGroup>PQR</LineGroup>
      <Amount>20</Amount>
   </Line>
</NewReport>
<NewReport>
   <Header>
      <Name>DEF</Name>
      <HeaderNo>456</HeaderNo>
   </Header>
   <Line>
      <LineGroup>IJK</LineGroup>
      <Amount>100</Amount>
   </Line>
   <Line>
      <LineGroup>XYZ</LineGroup>
      <Amount>50</Amount>
   </Line>
</NewReport>

Explanation:

Proper use of the Muenchian Grouping method with composite keys and of the identity rule.