Aggregating data from another field for each row of output
This topic provides examples of how to use the summary_function attribute within a summary_field tag to aggregate data from another field for each row of output. These aggregate functions are supported: sum, first, last, min, max, count, average, and list.
Example 1: Print sum of WRKD_MINUTES on each row
In this example, the cumulative sum of WRKD_MINUTES is printed on each row.
This is the full XML definition:
<payroll_export plugin="com.workbrain.app.export.payroll.basic.BasicPayrollExportPlugin">
<data>
<field name='emp_name'/>
<field name='tcode_name'/>
<field name='htype_name'/>
<field name='wrks_authorized'/>
<field name='retro'/>
<summary_field name='test' summary_function='sum' field='wrkd_minutes'/>
</data>
<logic>
<match_output>
<match field="wrks_authorized" value="Y" />
<match field="tcode_name" value="%" />
<match field="htype_name" value="REG" />
<output_row>
<map field="earn_code" value="1" />
</output_row>
</match_output>
<match_output>
<match field="wrks_authorized" value="Y" />
<match field="tcode_name" value="%" />
<match field="htype_name" value="OT1.0" />
<output_row>
<map field="earn_code" value="4" />
</output_row >
</match_output>
<match_output>
<match field="wrks_authorized" value="Y" />
<match field="tcode_name" value="%" />
<match field="htype_name" value="OT1.5" />
<output_row>
<map field="earn_code" value="2" />
</output_row >
</match_output>
<match_output>
<match field="wrks_authorized" value="Y" />
<match field="tcode_name" value="%" />
<match field="htype_name" value="OT2.0" />
<output_row>
<map field="earn_code" value="5" />
</output_row >
</match_output>
<match_output>
<match field="tcode_name" value="UAT" />
<match field="htype_name" value="UNPAID" />
</match_output>
<match_output>
<output_row>
<map field="earn_code" value="E" />
</output_row >
</match_output>
</logic>
<format>
<header>
</header>
<body>
<string field='emp_name' />
<constant>,</constant>
<string field='earn_code'/>
<constant>,</constant>
<number field='wrkd_minutes' format='########0.00' divide='60' null='***'/>
<constant>,</constant>
<string field='test' />
<new_line/>
</body>
<footer>
</footer>
</format>
</payroll_export>This table shows a sample of the Payroll Export file output:
| emp_name | earn_code | wrkd_minutes | test |
|---|---|---|---|
| 3152 | 1 | 8.00 | 480.0 |
| 3152 | 1 | 8.00 | 960.0 |
| 3152 | 1 | 8.00 | 1440.0 |
| 3152 | 1 | 8.00 | 1920.0 |
| 3152 | 1 | 8.00 | 2400.0 |
| 3152 | 1 | 8.00 | 2880.0 |
| 3152 | 1 | 8.00 | 3360.0 |
| 3180 | 1 | 8.00 | 3840.0 |
| 3180 | 1 | 8.00 | 4320.0 |
| 3180 | 1 | 8.00 | 4800.0 |
| 3180 | 1 | 8.00 | 5280.0 |
| 3180 | 1 | 8.00 | 5760.0 |
| 3180 | 1 | 8.00 | 6240.0 |
| 3180 | 1 | 8.00 | 6720.0 |
Example 2: Print maximum of WRKD_MINUTES on each row
In this example, the maximum value of WRKD_MINUTES is printed on each row.
The same XML definition is used as in Example 1, except the aggregate function is max:
<summary_field name='test' summary_function='max' field='wrkd_minutes'/>This table shows a sample of the Payroll Export file output:
| emp_name | earn_code | wrkd_minutes | test |
|---|---|---|---|
| 4024 | 1 | -8.00 | -480 |
| 4024 | E | 10.00 | 600 |
| 4024 | 1 | -8.00 | 600 |
| 4024 | 1 | 2.00 | 600 |
| 4130 | 1 | -8.00 | 600 |
| 4130 | E | 11.00 | 660 |
| 4130 | 1 | -8.00 | 660 |
| 4130 | E | 1.00 | 660 |
Example 3: Print first value of WRKD_MINUTES on each row
In this example, the first value of WRKD_MINUTES is printed on each row.
The same XML definition is used as in Example 1, except the aggregate function is first:
<summary_field name='test' summary_function='first' field='wrkd_minutes'/>This table shows a sample of the Payroll Export file output:
| emp_name | earn_code | wrkd_minutes | test |
|---|---|---|---|
| 5102 | 1 | -8.00 | -480 |
| 5102 | E | 10.00 | -480 |
| 5103 | 1 | -8.00 | -480 |
| 5103 | 1 | 2.00 | -480 |
Example 4: Print average of WRKD_MINUTES on each row
In this example, the average value of WRKD_MINUTES is printed on each row.
The same XML definition is used as in Example 1, except the aggregate function is average:
<summary_field name='test' summary_function='average' field='wrkd_minutes'/>This table shows a sample of the Payroll Export file output:
| emp_name | earn_code | wrkd_minutes | test |
|---|---|---|---|
| 5100 | 1 | -8.00 | -480.0 |
| 5100 | E | 10.00 | 60.0 |
| 5100 | 1 | -8.00 | -120.0 |
| 5100 | 1 | 6.00 | 0.0 |
| 5101 | 1 | -8.00 | -96.0 |
| 5101 | E | 4.00 | -40.0 |
Example 5: Print count of WRKD_MINUTES on each row
In this example, the count of WRKD_MINUTES is printed on each row.
The same XML definition is used as in Example 1, except the aggregate function is count:
<summary_field name='test' summary_function='count' field='wrkd_minutes'/>This table shows a sample of the Payroll Export file output:
| emp_name | earn_code | wrkd_minutes | test |
|---|---|---|---|
| 5218 | 1 | 8.00 | 1 |
| 5218 | 1 | 8.00 | 2 |
| 5218 | 1 | 8.00 | 3 |
| 5218 | 1 | 8.00 | 4 |
| 5218 | 1 | 8.00 | 5 |
| 5218 | 1 | 8.00 | 6 |
Example 6: Print WRKD_MINUTES values on each row
In this example, the WRKD_MINUTES values are printed on each row.
The same XML definition is used as in Example 1, except the aggregate function is list:
<summary_field name='test' summary_function='list' field='wrkd_minutes'/>This table shows a sample of the Payroll Export file output:
| emp_name | earn_code | wrkd_minutes | test |
|---|---|---|---|
| 5216 | 1 | -8.00 | -480 |
| 5216 | E | 10.00 | -480,600 |
| 5216 | 1 | -8.00 | -480,600,-480 |
| 5216 | E | 8.00 | -480,600,-480,480 |
| 5217 | 1 | -8.00 | -480,600,-480,480,-480 |
| 5217 | 1 | 2.00 | -480,600,-480,480,-480,120 |