Trying to create an email report of total weekly hours by employee with a catch

[Matthew_R]

Hi!

I have a time sheet app that collects time sheet info from our construction sites and I want a report to go to payroll weekly that shows them totals of each employee's hours. (ie - Mark 30 hours, Tom 40 hours, etc) Right now the report groups entries by their project name in order by date, but that is part of the catch. Our collection method uses a checkbox list of employees so foremen can fill out the information quickly for a team. (Ie - they can check all of them as having started at 7am and ending at 3pm of a list, rather than entering each employee by name) That is a great, speedy approach for data entry. But now I'm trying to figure out how to separate those listed names, which are under one record, so they can split up into the hours by employee report like I mentioned. The records are essentially a start time, end time, and a list of applicable employees who share those times. (They enter separated by a comma) Any thoughts or direction on how this could be broken up to list a total hours by employee each week? Hoping there is some language to use in a custom template in a bot, but maybe that's not so easy? Appreciate any help!

[Marc_Dillon]

If you use a START expression to iterate across a list of employees, you can add up their sum with something like this:

SUM( SELECT(
  time_table[duration] ,
  IN( [_THISROW] , [employees_list] )
) )