Count and Sum based on condition - Trailer record
Posted: 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="' '"/>
<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
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="' '"/>
<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