How to create a payroll report with unique employee names

[H_sam]

I am working on a payroll system wherein a report is generated for a pay period with a unique employee list. I have the following tables:
Employees - Email (Key), Employee Name, Timesheet Log (REF_ROWS (“Timesheet”, “Name”)), …
Timesheet - Log ID (Key), Name (Ref Employee), Pay Period ID(Ref), …
Pay Period - ID (Key), Start Date, End Date, Related Timesheets, …

How do I create a report for the Timesheet entries of employees created withing the pay periods with a unique list of employees?

The report would be something like this,
Employee Name: XYZ
Total Hours: 127
Total Salary: 312
Total Absent: 0

Employee Name: XYZ
Total Hours: 127
Total Salary: 312
Total Absent: 0
etc…
This is my expression that I have added to a Virtual column in [Pay Period], to get a list of entries within the period. How to also get the unique employees list?

SELECT(Timesheet[Log ID],
AND
([Date/Time In]>=[_THISROW].[Start Date],
[Date/Time In]<=[_THISROW].[End Date]

))

Would really appreciate any help in this. Thanks.

[Marc_Dillon]

You’ll probably need to use nested START expressions, like Timesheets within Employees. And use FILTER() to generate the List of key values.

help.appsheet.com

Template Start Expressions

Using Start expressions in your workflow templates.

help.appsheet.com

FILTER()

Select rows of table or slice

[H_sam · Accepted solution]

Thanks for putting me on the correct path. I am still learning to use expressions, but I have managed to get a list of Unique Employee Names that match the Timesheet entries using the below start expression.

<<Start: Select(Employees[Email], IN([Email],Select(Timesheet[Name], AND([Date/Time In] >= [_THISROW].[Start Date], [Date/Time In] <= [_THISROW].[End Date]), TRUE)))>><<[Employee Name]>>

I’m failing to understand how I can calculate the sum only for each listed employee name.
This is what I get,

Thanks.

[Marc_Dillon]

You need to add another condition to your SUM(SELECT()) to match the employee in each START iteration.

[H_sam]

Thanks Marc, I’ve got it!
Here is the final expression for the Total Hours (Overtime) for each employee within the respective pay period.

<<SUM(Select(Timesheet[Number of Hours (Overtime)],
AND
([Date/Time In]>=[_THISROW].[Start Date],
[Date/Time In]<=[_THISROW].[End Date],
[Name]=[_THISROW-1].[Email])))>>