Need help creating a summary report template

Hi all, I could really use some help in creating a summary report template. I have one table where timekeeping information is kept, another table with employee information. I need my template to pull a unique list of employee names from the timekeeping table based on the dates that the report creator specifies, I then need the template to sum the hours for each employee according to a very specific template so that it can be used with some old software.

I know how to collect the user input and generate the start expression required to generate the unique list of employee names, but I’m struggling to understand how I can sum each row in the template. My start expression is:
<<Start: Select(employee information[key], IN([full name],Select(activities[full employee name], AND([Date] >= [_THISROW].[Start Date], [Date] <= [_THISROW].[End Date]), TRUE)))>>

This gives me my unique list of employee names, but my template needs to also sum the hours for multiple different entries on the timekeeping table “activities” and total them up next to each employee name. Template below:

My sum expression would be something like the below but it’s missing a mechanism to match up with only the selected employee for each line:
<<sum(Select(activities[activity total], [Date] >= [_THISROW].[Start Date], [Date] <= [_THISROW].[End Date]))

I’m failing to understand how I can align this with the start expression and make the sum only be for each listed employee name. I hope I explained this well enough. I wish there were more examples of how to generate complex summary templates that pull data from multiple sources. All I’m trying to do is generate payroll reports. I would also love to be able to generate something similar in an .xls file or similar. Any help would be greatly appreciated!

Is it so that the total is not calclated for this one week like all hours from columns [MON]+[TUE]+ and so on?

Try:

SUM(
  SELECT(
    activities[activity total],
    AND(
      ([Date] >= [_THISROW].[Start Date]),
      ([Date] <= [_THISROW].[End Date]),
      ([full employee name] = [_THISROW].[full name])
    )
  )
)

I finally got this working by learning how to properly access a parent record using [_THISROW-1] from the start expression guide. Thanks for the help!

<<SUM(SELECT(activities[activity total],AND([Date]>=[Start Date],[Date]<=[End Date],[full employee name]=[_THISROW-1].[full name])))>>

2 Likes