Search code examples
xsltxslt-grouping

XSLT Multiple Grouping

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.

   <?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>

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

Revised XSLT

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="3.0"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="#all">
    
    <xsl:output method="text" />
    <xsl:variable name="vPipeDelimiter" select="'|'" />
    <xsl:variable name="vLineFeed" select="'&#13;&#10;'" />    
    <xsl:variable name="FirstDepositDate">
        
        <xsl:choose>
            <xsl:when test="Report/Deposit_Data[DepositDate != ''][1]/DepositDate != ''">
                <xsl:value-of select="Report/Deposit_Data[DepositDate != '']/DepositDate"/>    
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="format-date(current-date(),'[Y0001]-[M01]-[D01]')"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:variable>
    
    <xsl:template match="/">
        
        <xsl:call-template name="header"/>
        <xsl:call-template name="Deposit_Data" />
        
        
    </xsl:template>    
    
    <!--Header Template-->
    <xsl:template name="header">
        
        <!-- MemberID --><xsl:text>MemberID</xsl:text>
        <xsl:value-of select="$vPipeDelimiter"/>
        <!-- DepositDate--><xsl:text>DepositDate</xsl:text>
        <xsl:value-of select="$vPipeDelimiter"/>
        <!-- DepositType --><xsl:text>DepositType</xsl:text>
        <xsl:value-of select="$vPipeDelimiter"/>
        <!-- Amount --><xsl:text>Amount</xsl:text>
        <xsl:value-of select="$vPipeDelimiter"/>       
        
        <xsl:value-of select="$vLineFeed"/>            
        
    </xsl:template>
    
    
    <xsl:template name="Deposit_Data">
        <xsl:for-each-group select="Report/Deposit_Data" composite="yes" group-by="MemberID, DepositDate">
            <xsl:variable name="key" select="current-grouping-key()"/>
            <xsl:for-each-group select="current-group()/DepositInfo" group-by="DepositType">
                <!-- MemberID --><xsl:value-of select="../MemberID"/>
                <xsl:value-of select="$vPipeDelimiter"/>    
                <!-- MemberID --><xsl:value-of select="../DepositDate"/>
                <xsl:value-of select="$vPipeDelimiter"/>    
                
                
                <xsl:choose>
                    <xsl:when test="DepositType = 'CHK'">                       
                        <xsl:value-of select="'ACCT1',  sum(current-group()/Amount)" separator="|"/>
                    </xsl:when>
                    <xsl:when test="DepositType = 'SAV'">
                        <xsl:value-of select="'ACCT2',  sum(current-group()/Amount)" separator="|"/>
                    </xsl:when>
                    <xsl:when test="DepositInfo/DepositType = 'CD'">
                        <xsl:value-of select="'ACCT3',  sum(current-group()/Amount)" separator="|"/>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:text/>
                    </xsl:otherwise>
                </xsl:choose>                
              
                <xsl:text>&#10;</xsl:text>        
            </xsl:for-each-group>
        </xsl:for-each-group>
    </xsl:template>
    
</xsl:stylesheet>
Solution

  • Using XSLT 3, you could use

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="3.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" composite="yes" group-by="MemberID, DepositDate">
      <xsl:variable name="key" select="current-grouping-key()"/>
      <xsl:for-each-group select="current-group()/DepositInfo" group-by="DepositType">
        <xsl:value-of select="$key, current-grouping-key(), sum(current-group()/Amount)" separator="|"/>
        <xsl:text>&#10;</xsl:text>        
      </xsl:for-each-group>
    </xsl:for-each-group>
  </xsl:template>

</xsl:stylesheet>

    Or

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="3.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" composite="yes" group-by="../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>