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]

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