xsltxslt-1.0muenchian-grouping

How can I create a Grand Total Summary AFTER the Muenchian Grouping/Summing is finished

I have been trying, to no avail, to figure out how to do a Grand Total of grouped and summed values using Muenchian Grouping.

Here is my grouping key:

<xsl:key name="kRcdGroup" match="Export/Record" use="concat(generate-id(..), AccountNo, Description, vb:GetOverrideCode(DeptOverride,LedgerCode))"/>

The vb function just returns one or the other of the two values passed to it.

I created this variable as a summing medium:

<xsl:variable name="vkeyGroup" select="key('kRcdGroup', concat(generate-id(..), AccountNo, Description, vb:GetOverrideCode(DeptOverride,LedgerCode))) "/>

This is a pipe delimited text output and I am doing the summing in a for-each loop:

<xsl:for-each select="Export/Record[generate-id() = generate-id(key('kRcdGroup',concat(generate-id(..), AccountNo, Description, vb:GetOverrideCode(DeptOverride,LedgerCode)))[1])] ">

The output is working just fine up to this point. However, the client requires a footer to be added. They want the summary of all those grouped/summed values.

This output is used to upload data to a database, so it has to be in this ridiculous format. 01 is the header, 02 is the detail output and 99 is the footer. (NOTE: There should not be a blank line after the header or before the footer, I just had to do that to make in more readable.)

01|CA|PAYR 2082027||
 
02|1|1|||23|0736|0704010000|0736131554|PAYR||||0|0704010000||||V0|13864.08|CAD||||CAN Flex CASH_0001|||CP|||||CAN Flex CASH||40|0736|0736131554|||||||||||||||||||||||||||||||||||
02|1|2|||23|0736|0701000000|0736131554|PAYR||||0|0701000000||||V0|149480.33|CAD||||CAN Bonus_0002|||CP|||||CAN Bonus||40|0736|0736131554|||||||||||||||||||||||||||||||||||
02|1|3|||23|0736|0700793000|0736131554|PAYR||||0|0700793000||||V0|74941.42|CAD||||CAN TXB RRSP_0003|||CP|||||CAN TXB RRSP||40|0736|0736131554|||||||||||||||||||||||||||||||||||

99|238285.83

So here is a couple of nodes of the xml:

<Export>
    <Record>
        <AccountNo><![CDATA[0700103000]]></AccountNo>
        <Onsite></Onsite>
        <Amount>      47916.63</Amount>
        <PayDate>15062023</PayDate>
        <PayDateCA>06152023</PayDateCA>
        <PPStartDate>20230601</PPStartDate>
        <PPEndDate>06/15/2023</PPEndDate>
        <PPEndDateCA>20230615</PPEndDateCA>
        <Description><![CDATA[CAN Regular                                   ]]></Description>
        <Category>Earning</Category>
        <Desc><![CDATA[CAN Regular]]></Desc>
        <EmpNumber>6001237</EmpNumber>
        <EmpFirstName><![CDATA[Elia]]></EmpFirstName>
        <EmpLastName><![CDATA[DiLoreto]]></EmpLastName>
        <DeptName>0736131554</DeptName>
        <DeptLedgerCode></DeptLedgerCode>
        <OnSiteName><![CDATA[0736131554 - Toronto Office - 0736]]></OnSiteName>
        <SiteCode><![CDATA[]]></SiteCode>
        <DeptOverride></DeptOverride>
        <LedgerCode><![CDATA[0736131554]]></LedgerCode>
        <Constant1>ERN</Constant1>
        <Constant2></Constant2>
        <Constant3></Constant3>
        <Constant4></Constant4>
        <Constant5></Constant5>
        <Filler1><![CDATA[                         ]]></Filler1>
        <Filler2><![CDATA[                  ]]></Filler2>
        <LegalEntity>Deutsche Bank AG CA</LegalEntity>
        <EmpType>FT</EmpType>
    </Record>
    <Record>
        <AccountNo><![CDATA[0700793000]]></AccountNo>
        <Onsite></Onsite>
        <Amount>       8833.12</Amount>
        <PayDate>15062023</PayDate>
        <PayDateCA>06152023</PayDateCA>
        <PPStartDate>20230601</PPStartDate>
        <PPEndDate>06/15/2023</PPEndDate>
        <PPEndDateCA>20230615</PPEndDateCA>
        <Description><![CDATA[CAN TXB RRSP                                  ]]></Description>
        <Category>Earning</Category>
        <Desc><![CDATA[CAN TXB RRSP]]></Desc>
        <EmpNumber>6001237</EmpNumber>
        <EmpFirstName><![CDATA[Elia]]></EmpFirstName>
        <EmpLastName><![CDATA[DiLoreto]]></EmpLastName>
        <DeptName>0736131554</DeptName>
        <DeptLedgerCode></DeptLedgerCode>
        <OnSiteName><![CDATA[0736131554 - Toronto Office - 0736]]></OnSiteName>
        <SiteCode><![CDATA[]]></SiteCode>
        <DeptOverride></DeptOverride>
        <LedgerCode><![CDATA[0736131554]]></LedgerCode>
        <Constant1>MEM</Constant1>
        <Constant2></Constant2>
        <Constant3></Constant3>
        <Constant4></Constant4>
        <Constant5></Constant5>
        <Filler1><![CDATA[                         ]]></Filler1>
        <Filler2><![CDATA[                  ]]></Filler2>
        <LegalEntity>Deutsche Bank AG CA</LegalEntity>
        <EmpType>FT</EmpType>
    </Record>
</Export>

The Amount value in the for-each loop have their absolute values summed up with a neg (40) or pos (50) flag indicating the sign.

<xsl:choose>
            <xsl:when test="sum($vkeyGroup/Amount) > 0">
                <xsl:value-of select="format-number(sum($vkeyGroup/Amount),'#.00')"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="format-number(sum($vkeyGroup/Amount)*-1,'#.00')"/>
            </xsl:otherwise>
        </xsl:choose>

I have tried creating a second Key to sum up the currency values by the pay date which is the same on every record but because the currency value may be positive or negative it sums differently when grouping by pay date. I need to be able to summarize the absolute value of the Amount field so that it matches the total of the values in the for-each loop.

I reached out to Jeni Tennison but have not heard back yet - I'm under the gun to get this taken care of. Any help/examples would be greatly appreciated. I'm learning through all your help but I still have a long way to go. Next, I need to understand how to use templates:).

Many thanks, Greg

Solution

  • I am still struggling to understand what is the logic that needs to be applied here.

    Maybe this simplified example can help:

    XML

    <entries>
    <entry>
        <group>A</group>
        <amount>-50</amount>
    </entry>
    <entry>
        <group>A</group>
        <amount>25</amount>
    </entry>
    <entry>
        <group>B</group>
        <amount>80</amount>
    </entry>
    <entry>
        <group>B</group>
        <amount>-20</amount>
    </entry>
    <entry>
        <group>C</group>
        <amount>15</amount>
    </entry>
</entries>

    XSLT 1.0 + EXSLT node-set() extension function

    <xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:exsl="http://exslt.org/common"
extension-element-prefixes="exsl">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:key name="entry-by-group" match="entry" use="group" />

<xsl:template match="/entries">
    <!-- first pass -->
    <xsl:variable name="subtotals">
        <xsl:for-each select="entry[count(. | key('entry-by-group', group)[1]) = 1]">
            <subtotal group="{group}">
                <xsl:variable name="subtotal" select="sum(key('entry-by-group', group)/amount)"/>
                <!-- output absolute value -->
                <xsl:value-of select="translate($subtotal, '-', '')"/>
            </subtotal>
        </xsl:for-each> 
    </xsl:variable>
    <!-- final output -->
    <output>
        <xsl:copy-of select="$subtotals"/>
        <total>
            <xsl:value-of select="sum(exsl:node-set($subtotals)/subtotal)"/>
        </total>
    </output>
</xsl:template>

</xsl:stylesheet>

    Result

    <?xml version="1.0" encoding="UTF-8"?>
<output>
  <subtotal group="A">25</subtotal>
  <subtotal group="B">60</subtotal>
  <subtotal group="C">15</subtotal>
  <total>100</total>
</output>

    This seems to follow the process you describe, although I fail to see how this could be useful.

    Note: If you are using a Microsoft processor, you may have to change this:

    xmlns:exsl="http://exslt.org/common"

    to:

    xmlns:exsl="urn:schemas-microsoft-com:xslt"

    (Or, more conventionaly, declare xmlns:msxsl="urn:schemas-microsoft-com:xslt" and change all exsl prefixes to msxsl.)