Employee hours worked report

[Wallace_Service]

Hi! I am worked on a timesheet report for weekly hours worked for a group of field technicians. I have created a slice that filters the punch in/punch out times for each employee. A report prints every Monday showing the work times for each day the previous week. This all works. I have a virtual column in the timesheet table that calculates the total hours worked for each employee the previous week. This also outputs the correct info. This issue I have is that that “Total hours worked” prints out in the report the same number of times as the number of rows in the report. So, for example, if the employee had 5 days of punch in and out times the previous week, the total number of hours for those 5 days will print out 5 times. Is there a way to print out the sum once instead of printing to match the number of rows in the report?

Thanks!

Justin

[Phil]

Can you show us the Report template you are using?

[Wallace_Service]

Hi Phil, yes sir, here is the link:

docs.google.com

Rob G. hours worked report

Below is last weeks hours worked report for Rob Grawberger. <<Start:FILTER(Total hours worked in past week for Rob, true)>> Date: <<[Date]>> Time in: <<[Time in]>> Time out: <<[Time out]>> Total hours: <<[Number of hours]>> <<End>> ...

Thanks,

Justin

[Phil]

Everything between the <<Start>> and <<End>> is repeated for each record returned by the <<Start>> expression. That is why you are seeing the total repeated for each record. You need to remove it from its current location to prevent it from being displayed for each record…

I am not sure how you are computing the total, but essentially you want to put “Total hours:” and the expression that computes that total after the <<End>>.

[Wallace_Service]

Hi Phil, thanks for the feedback. I tried this now but if I move the [Total hours past week Rob] after the <> the report won’t send at all.

[Total hours past week Rob] is a virtual column in my Timesheet table calculated via:

SUM(SELECT(Timesheet[Number of hours], AND(([Employee name]=“Rob Grawberger”), ([Date] > (TODAY() - WEEKDAY(TODAY()))), ([Date] <= (TODAY() - WEEKDAY(TODAY())) + 7))))

I pull the virtual column into my Total hours worked in past week for Rob slice that I’m creating the report from.

Not sure if my structure has something to do with this?

Thanks,

Justin

[Phil · Accepted solution]

Can you try the following.

 <<Start:FILTER(Total hours worked in past week for Rob, true)>> 
 Date: <<[Date]>> Time in: <<[Time in]>> Time out: <<[Time out]>> 
 <<End>>

 Total hours: <<SUM(SELECT(Timesheet[Number of hours], AND(([Employee name]=“Rob Grawberger”), ([Date] > (TODAY() - WEEKDAY(TODAY()))), ([Date] <= (TODAY() - WEEKDAY(TODAY())) + 7))))>>

I am not very good with expressions, but I think your expression will compute the total correctly if you put it in the Report template (as shown above). If so, and if you don’t use that virtual column elsewhere, it would be better to compute the total in the Report and eliminate the virtual column from the slice.

[Wallace_Service]

Thank you Phil! This worked like a charm, I’ve removed the virtual column from the slice as well! Thanks so much!