Search code examples
xsltxslt-2.0xslt-grouping

XSLT Grouping for version 2.0

would like to know how to go about creating multiple groupings in XSLT. The following XML should be grouped by memberID, deposit date, and deposit type. The total amount for each member should be summed by date and deposit type. I had posed this question already and the answer was in version 3.0. I would need a solution using XSLT version 2.0 as the system I'm working with has limitations on version 3.0. Thank you,.

<?xml version="1.0" encoding="UTF-8"?>
<Report>
<Deposit_Data>
 <MemberID>12345</MemberID>
    <MemberFirstName>Mickey</MemberFirstName>
    <MemberLastName>Mouse</MemberLastName>      
         <DepositDate>2023-11-22</DepositDate>
         <DepositInfo>
             <DepositType>CHK</DepositType>
                 <Amount>50.00</Amount>
         </DepositInfo>
         <DepositInfo>
             <DepositType>SAV</DepositType>
                 <Amount>30.00</Amount>
         </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>12345</MemberID>
    <MemberFirstName>Mickey</MemberFirstName>
    <MemberLastName>Mouse</MemberLastName>      
            <DepositDate>2023-11-22</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>5.00</Amount>
            </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>12345</MemberID>
    <MemberFirstName>Mickey</MemberFirstName>
    <MemberLastName>Mouse</MemberLastName>      
            <DepositDate>2023-12-05</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>25.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>78910</MemberID>
    <MemberFirstName>Donald</MemberFirstName>
    <MemberLastName>Duck</MemberLastName>      
            <DepositDate>2023-11-22</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>30.00</Amount>
            </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>78910</MemberID>
    <MemberFirstName>Donald</MemberFirstName>
    <MemberLastName>Duck</MemberLastName>      
            <DepositDate>2023-12-05</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>20.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>78910</MemberID>
    <MemberFirstName>Donald</MemberFirstName>
    <MemberLastName>Duck</MemberLastName>      
            <DepositDate>2023-12-05</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>5.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
</Deposit_Data>
</Report>

The end result should be:

   MemberID|DepositDate|DepositType|Amount
   12345|2023-11-22|CHK|60
   12345|2023-11-22|SAV|35
   12345|2023-12-05|CHK|25
   12345|2023-12-05|SAV|10
   78910|2023-11-22|CHK|10
   78910|2023-11-22|SAV|30
   78910|2023-12-05|CHK|25
   78910|2023-12-05|SAV|20

I tried running with a for-each-group based on the date and deposit type which didn't work. Please see below for the XSLT.

 <xsl:template match="Deposit_Data">
    
    <xsl:choose>
        
        <xsl:when test="exists(DepositInfo)">
            
            <xsl:for-each-group select="." group-by="DepositDate">
    
                <xsl:variable name="DepositDateOfCurrentGroup" select="current-grouping-key()"/>
                
                <!-- MemberID --><xsl:value-of select="MemberID"/>
                <xsl:value-of select="$vPipeDelimiter"/>
                <!-- DepositDate --><xsl:value-of select="format-date($DepositDateOfCurrentGroup,'[Y0001][M01][D01]')"/>
                <xsl:value-of select="$vPipeDelimiter"/>        
                
                
                <!-- DepositType -->                   
                <xsl:choose>
                    <xsl:when test="DepositInfo/DepositType = 'CHK'">
                        <!-- Amount --><xsl:text>ACCT1</xsl:text>
                    </xsl:when>
                    <xsl:when test="DepositInfo/DepositType = 'SAV'">
                        <!-- Amount --><xsl:text>ACCT2</xsl:text>
                    </xsl:when>
                    <xsl:when test="DepositInfo/DepositType = 'CD'">
                        <!-- Amount --><xsl:text>ACCT3</xsl:text>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:text/>
                    </xsl:otherwise>
                </xsl:choose>
                <xsl:value-of select="$vPipeDelimiter"/>
                     
                
                
                <xsl:choose>
                  
                    <xsl:when test="exists(DepositDate = $DepositDateOfCurrentGroup and DepositInfo/DepositType = 'CHK')">
                        <xsl:value-of select="format-number(sum(DepositDate[DepositDate = $DepositDateOfCurrentGroup]/Amount),'#######0.00')"/>                                 
                    </xsl:when>
                    <xsl:when test="exists([DepositDate = $DepositDateOfCurrentGroup and DepositInfo/DepositType = 'SAV'])">
                        <xsl:value-of select="format-number(sum(DepositDate[DepositDate = $DepositDateOfCurrentGroup]/Amount),'#######0.00')"/>                                 
                    </xsl:when>
                    <xsl:when test="exists(DepositDate = $DepositDateOfCurrentGroup and DepositInfo/DepositType = 'CD')">
                        <xsl:value-of select="format-number(sum(DepositDate[DepositDate = $DepositDateOfCurrentGroup]/Amount),'#######0.00')"/>                                 
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:text>0.00</xsl:text>
                    </xsl:otherwise>
                </xsl:choose>   
               
                         
                                                  
                <xsl:value-of select="$vLineFeed"/>
                
              
    
            </xsl:for-each-group>
        
        </xsl:when>
        
        <xsl:otherwise>                   
              
            
        </xsl:otherwise>
        
    </xsl:choose>
    
</xsl:template>

The end result has no grouping:

MemberID|DepositDate|DepositType|Amount|
12345|20231122|CHK|0.00
12345|20231122|CHK|0.00
12345|20231205|CHK|0.00
78910|20231122|CHK|0.00
78910|20231205|CHK|0.00
78910|20231205|CHK|0.00
Solution

  • Based on the answer for xslt 3.0 you mentioned, it looks like composite="yes" is not supported by version 2.0. But there's a workaround using string-join(), just need to pass the first three fields in round brackets.

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="2.0"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  exclude-result-prefixes="#all">

  <xsl:output method="text" />

  <xsl:template match="/">
    <xsl:for-each-group select="Report/Deposit_Data/DepositInfo" 
         group-by="string-join((../MemberID, ../DepositDate, DepositType), '|')">
      <xsl:value-of select="current-grouping-key(), sum(current-group()/Amount)" separator="|"/>
      <xsl:text>&#10;</xsl:text>        
    </xsl:for-each-group>
  </xsl:template>

</xsl:stylesheet>