Pivot like reporting

[PeterJNCK]

Hello,
to simplfy my struggle with reporting in Airtable lets imagine this workflow:

I have Employee, Projects, and Timelog tables where employees track the time spent on specific projects. Each employee has their own hourly rate.

What I need to achieve is a monthly report for each employee that includes:

All projects they worked on in the previous month (show them only if they worked on specific project at least for one hour)
The total time spent on each project
The total cost (calculated based on their hourly rate)

apologize if this has been discussed before, but I couldn’t find a solution.
I am could achieve this by Pivot table in the dashboard section, but I need to make automations based on this results (adding Payments and so...) 

Thank you

[Mike_AutomaticN]

Hey @PeterJNCK!

You can achieve that by creating a new table called “Reports”. 
Under such table you should have the following fields:

1. Employee

2. Project

3. Duration

4. Month

You can also have an automation to run automatically on a monthly basis to fill out this data. 

Mike, Consultant @ Automatic Nation

[Mike_AutomaticN]

A more robust solution would be to have two different tables:

1. Reports

2. Report Line Items

Under Reports you could have the fields:

1. Employee (linked)

2. Start Date

3. End Date 

4. Report Line Items (linked)

Under Report Line Items you can have:

1. Project

2. Duration

3. Tímelogs (linked)

[VikasVimal]

Use Interfaces, specifically, dashboard for reporting.
Build it on the timelog table records, and use pill filters for employees and MONTHS. You'd need to use a month/year dropdown to filter correctly and have a script or automation that fills that field daily/hourly etc.

[TheTimeSavingCo · Accepted solution]

Try:
1. In Timelog, create a formula field that'll output the employee name, month, and the project name, e.g 'Jerry S - Dec 24 - Project 1'
2. Create a new table called 'Employee reports' or something and link it to Timelog
3. Create an automation that will trigger whenever a record is created in Timelog, and its action will be to paste the value from the formula field in step 1 into the linked field to the table created in step 2
  - The trigger to use here is dependent on how your data gets created, so you'll need to tweak this bit yourself
4. In 'Employee reports', create a rollup field to sum up the amount of time each employee spent on each project per month

Each record in 'Employee reports' now represents the amount of time a single employee worked on a single project for a month.  You can then create a view and filter out any records where the rollup field value is <1 hour, giving you all projects they worked on in the previous month

Link to base

[MJen · Accepted solution]

To achieve your goal the best approach is to use a structured setup across multiple tables, combined with automations and interfaces.

First, ensure your Timelog Table is set up properly. Each record should include fields for the employee (linked to the Employees table), project (linked to the Projects table), date (to track when the work was done), hours worked, hourly rate (pulled from the Employees table), and a calculated field for the cost (using a formula like `{Hours} * {Hourly Rate}`). This table serves as the core data source for all your reporting needs.

Next, create an Employee reports table to store the monthly summaries. Each record in this table will represent a specific employee’s work for a given month. Include fields such as employee (linked), month-year (calculated with a formula like `DATETIME_FORMAT({Start Date}, 'MMM-YYYY')`), total hours (a rollup field summing the hours from the Timelog table), total cost (another rollup summing the calculated cost from Timelog), and a linked field to list all the projects they worked on. This table will serve as the basis for your reports and automations.

To automate the creation of these reports, set up an Automation in Airtable. Configure it to trigger at the start of each month, using the Find Records action to locate all Timelog entries from the previous month. Then use the grouped records to populate the Employee Reports table. For each employee, calculate the total hours and costs, as well as the list of projects they worked on, and update or create a new record in the Employee Reports table.

For users comfortable with scripting, a Custom Script can be added to the automation to handle grouping and calculations more dynamically. The script would fetch all Timelog records for the previous month, group them by employee and project, calculate totals, and create or update records in the Employee Reports table. This approach offers greater flexibility and efficiency for managing complex datasets.

In the end, create an interface for your reports. Use the Interface Designer to build a dashboard where you can filter data by employee and month/year. Include visual rollups of total hours and costs, broken down by project. This interface will make it easier for your team to access and analyze reports without needing to navigate through the raw tables.

To enhance this setup, consider adding fields for payment tracking in the Employee Reports table, such as a Payment Status field, and use conditional formatting in the interface to flag any unpaid reports.

This setup should help you reach your goal.

[PeterJNCK]

Thank you all so much for your ideas! I’m going to try each one to see which best suits my needs.
Thanks again! Peter

[PeterJNCK]

Hi Adam,
just one following question, please how would you to setup automation (Action) in your base to send each month Time reports to each employee email (but only their own projects they working on actual month) based on your Employee Reports table?
Thank you!

[TheTimeSavingCo]

Hmm, assuming it's sent the next month, you could try:
1. Create a formula field in 'Employee Reports' that'll display the text 'Previous month' based on whether it was for last month or not
2. Create an automation that'll trigger on the first of day of each month, and its actions will be:
  a. Find record action to look for all the records in the 'Employees' table
  b. Repeating group action to loop through each of the found Employee records
    b.i. Find record action to look for all the records in 'Employee Reports' tied to this current Employee and also is set for 'Previous month"
    b.ii. Send email action that'll send this current Employee an email with the 'Find record' action results

Do note that this'll send every employee an email, even if they haven't worked that month.  If this is something you need to consider, then you'll need to create a lookup field in the 'Employees' table that'll help you figure out whether that Employee worked last month, and then update step 2a to look for only Employee records that worked last month