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!

Solved Solved
1 5 1,228
1 ACCEPTED SOLUTION

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])))>>

View solution in original post

5 REPLIES 5

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

Steve
Participant V

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])))>>

HI,
I am developing a payroll system with a similar structure; however, I am not getting how you are collecting user input and generating a unique list of employee names. Could you please share some insight on your table structure?
Would really appreciate the help. Thanks

Permission to ask, date column in the picture you show Does the sheet actually have dates for each column, or does it have dates for just one column?


Well, I wonder if the date of the actual column. Do you separate each day? If not separate each day I want to know how you make it horizontal like that.

Top Labels in this Space