Count and Sum based on condition - Trailer record

Here should go questions about transforming XML with XSLT and FOP.
xsltnewbie
Posts: 1
Joined: Thu Oct 04, 2018 11:37 am

Count and Sum based on condition - Trailer record

Post by xsltnewbie » Thu Oct 04, 2018 11:58 am

Hi, need help with creating trailer count and total sum based on condition.
Condition: Remove Payroll results with deduction_Code is 401K-T.

Please see below XML and XSLT and desired result.

XML Code :
<?xml version='1.0' encoding='UTF-8'?>
<wd:Report_Data xmlns:wd="urn:com.workday.report/SampleReport">
<wd:Report_Entry>
<wd:Employee_ID>12345</wd:Employee_ID>
<wd:Last_Name>Duck</wd:Last_Name>
<wd:First_Name>Donald</wd:First_Name>
<wd:Payroll_Result_Lines>
<wd:Deduction wd:Descriptor="401(k) [USA]">
<wd:ID wd:type="Deduction_Code">401K</wd:ID>
</wd:Deduction>
<wd:Result_Line_Amount>277.96</wd:Result_Line_Amount>
</wd:Payroll_Result_Lines>
<wd:Payroll_Result_Lines>
<wd:Deduction wd:Descriptor="401(k) Roth [USA]">
<wd:ID wd:type="Deduction_Code">401K-R</wd:ID>
</wd:Deduction>
<wd:Result_Line_Amount>194.49</wd:Result_Line_Amount>
</wd:Payroll_Result_Lines>
<wd:Payroll_Result_Lines>
<wd:Deduction wd:Descriptor="401(k) T">
<wd:ID wd:type="Deduction_Code">401K-T</wd:ID>
</wd:Deduction>
<wd:Result_Line_Amount>94.49</wd:Result_Line_Amount>
</wd:Payroll_Result_Lines>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:Employee_ID>98765</wd:Employee_ID>
<wd:Last_Name>Mouse</wd:Last_Name>
<wd:First_Name>Mickey</wd:First_Name>
<wd:Payroll_Result_Lines>
<wd:Deduction wd:Descriptor="401(k) [USA]">
<wd:ID wd:type="Deduction_Code">401K</wd:ID>
</wd:Deduction>
<wd:Result_Line_Amount>11657.15</wd:Result_Line_Amount>
</wd:Payroll_Result_Lines>
<wd:Payroll_Result_Lines>
<wd:Deduction wd:Descriptor="401(k) T">
<wd:ID wd:type="Deduction_Code">401K-T</wd:ID>
</wd:Deduction>
<wd:Result_Line_Amount>11657.15</wd:Result_Line_Amount>
</wd:Payroll_Result_Lines>
</wd:Report_Entry>
</wd:Report_Data>

XSLT:
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:wd="urn:com.workday.report/SampleReport"
exclude-result-prefixes="xs wd"
version="2.0">

<xsl:output method="text"/>
<xsl:param name="separator" select="','"/>
<xsl:param name="linefeed" select="'&#13;&#10;'"/>


<xsl:template match="/">

<xsl:call-template name="DetailRecords"/>
<xsl:call-template name="TrailerRecord"/>

</xsl:template>

<!--Builds Detail Records -->
<xsl:template name="DetailRecords">
<xsl:for-each select="wd:Report_Data/wd:Report_Entry/wd:Payroll_Result_Lines">
<xsl:if test="wd:Deduction/wd:ID[@wd:type='Deduction_Code'] != '401K-T'">

<!-- Employee ID -->
<xsl:value-of select="parent::node()/wd:Employee_ID"/>
<xsl:value-of select="$separator"/>

<!-- First Name -->
<xsl:value-of select="parent::node()/wd:First_Name"/>
<xsl:value-of select="$separator"/>

<!-- Last Name -->
<xsl:value-of select="parent::node()/wd:Last_Name"/>
<xsl:value-of select="$separator"/>

<!-- Last Name -->
<xsl:value-of select="wd:Deduction/wd:ID[@wd:type='Deduction_Code']"/>
<xsl:value-of select="$separator"/>

<!-- Last Name -->
<xsl:value-of select="wd:Result_Line_Amount"/>

<xsl:value-of select="$linefeed"/>
</xsl:if>
</xsl:for-each>
</xsl:template>

<!--Builds Trailer Record -->
<xsl:template name="TrailerRecord">

</xsl:template>

</xsl:stylesheet>
------------------------------------------------------------
Desired Results:
12345,Donald,Duck,401K,277.96
12345,Donald,Duck,401K-R,194.49
98765,Mickey,Mouse,401K,11657.15
3, 12129.6

Martin Honnen
Posts: 52
Joined: Tue Aug 19, 2014 12:04 pm

Re: Count and Sum based on condition - Trailer record

Post by Martin Honnen » Thu Oct 04, 2018 6:42 pm

Is that the same condition you have in

Code: Select all


<xsl:if test="wd:Deduction/wd:ID[@wd:type='Deduction_Code'] != '401K-T'">
?

Seem you simply want to create a global variable

Code: Select all


<xsl:variable name="records" select="wd:Report_Data/wd:Report_Entry/wd:Payroll_Result_Lines[wd:Deduction/wd:ID[@wd:type='Deduction_Code'] != '401K-T']"/>
and then where needed you can use it with e.g.

Code: Select all

<xsl:value-of select="count($records), sum($records/wd:Result_Line_Amount)" separator="{$separator}"/>

Post Reply