Search code examples
xmlxslt-2.0

XSLT - Count distinct period date values for each employee and add up all counts for a total

I'm not even sure I phrased the question correctly. I need to get a count of how many records are displayed in a report generated by and display it in a footer row that contains various totals. The count I'm looking for is how many retro period rows are displayed in the report.

Each employee has a node in . Within each employee's node, there are child nodes which may contain zero to many payments which have associated retro pay period date nodes. An employee may have multiple nodes for the same retro pay period. In the report, each employee should have 1 line per retro pay period. So, to get a count of how many of these retro lines are supposed to be displayed in the report, I need to only count the unique/distinct retro period dates for each employee and then take all those counts and add them up for the total amount of retro rows displayed at the end in the footer row.

Example XML:

<Report_Entry>
  <Worker Descriptor="Mick Mouse (00001)">
    <ID type="Employee_ID">00001</ID>
  </Worker>
  <Payroll_Result_Lines_within_Date_Range>
  </Payroll_Result_Lines_within_Date_Range>
</Report_Entry>
<Report_Entry>
  <Worker Descriptor="Don Duck (On Leave) (00002)">
    <ID type="Employee_ID">00002</ID>
  </Worker>
  <Payroll_Result_Lines_within_Date_Range>
    <Retro_Prd_End>2022-12-31</Retro_Prd_End>
  </Payroll_Result_Lines_within_Date_Range>
  <Payroll_Result_Lines_within_Date_Range>
    <Retro_Prd_End>2022-12-31</Retro_Prd_End>
  </Payroll_Result_Lines_within_Date_Range>
</Report_Entry>
<Report_Entry>
  <Worker Descriptor="Adam Ant (00003)">
    <ID type="Employee_ID">00003</ID>
  </Worker>
  <Payroll_Result_Lines_within_Date_Range>
    <Retro_Prd_End>2023-02-15</Retro_Prd_End>
  </Payroll_Result_Lines_within_Date_Range>
  <Payroll_Result_Lines_within_Date_Range>
    <Retro_Prd_End>2023-02-15</Retro_Prd_End>
  </Payroll_Result_Lines_within_Date_Range>
  <Payroll_Result_Lines_within_Date_Range>
    <Retro_Prd_End>2023-02-28</Retro_Prd_End>
  </Payroll_Result_Lines_within_Date_Range>
  <Payroll_Result_Lines_within_Date_Range>
    <Retro_Prd_End>2023-03-15</Retro_Prd_End>
  </Payroll_Result_Lines_within_Date_Range>
</Report_Entry>

In the example, the count I'm expecting is 4 (Mick has 0, Don has 1 unique Retro_Prd_End, and Adam has 3).

The following hasn't worked: 1.

<xsl:variable name="CountERSRetroLines" select="count(distinct-values(concat(Report_Data/Report_Entry/Worker/ID[@type='Employee_ID'],'|',../Payroll_Result_Lines_within_Date_Range/Retro_Prd_End)))" />

<xsl:variable name="CountERSRetroLines" select="count(distinct-values(Report_Data/Report_Entry/Worker/ID[@type='Employee_ID'][distinct-values(../Payroll_Result_Lines_within_Date_Range/Retro_Prd_End)]))" />
Solution

  • An XPath 2.0 solution:

    sum(//Report_Entry ! count(distinct-values(./Payroll_Result_Lines_within_Date_Range/Retro_Prd_End)))

    Using the Simple Mapping Operator ! to iterate over each of the Report_Entry, count their Payroll_Result_Lines_within_Date_Range distinct values. And calculate the sum() of those counts.