How to Create a Summary Report in Airtable

Reporting on business information is a vital part of company operations. “How do I create a report in Airtable?” is one of the common questions we see from Airtable users. 

First, the answer is, “Yes, you can create reports!” You can create summary reports from your Airtable data, whether you need to summarize income, projects, sales, or information such as employee vacation hours or inventory totals.

In this tutorial, you’ll learn how to create summary reports in Airtable, how to automate record links to a report, and how to generate a document or presentation to share the information with others.

Summary Report of Budget Items for a Project

This same method can be used to create other types of summarized data, such as Invoices, Estimates, Orders, Contracts, or Form Entries. I’ll give you the basics of creating a report in your own Airtable base, then we’ll get into the details with an example Airtable base.

Related: How to Display Data and Filtered Records from Airtable on Your Forms

The Basics of How to Create a Report in Airtable

We’ll get into the details of how to set up your own report but here are the basics for creating a report in Airtable.

1. Create a new table in your base and name it Reports

2. In the Reports table, add a Linked record field related to the table that contains the data you want to summarize.

If you want to report on all Leads/Deals, then link to the table where your Leads/Deals data is stored. 

3. Add Rollup fields with a Rollup field formula to calculate the data from your linked table.

Rollup fields allow you to look up data from Linked tables and join, summarize, or count that information.

4. Create a single record in your Reports table and add any records from your Linked table that need to be included on the Report

5. Your Report data will be automatically calculated

Once you have a Linked record field and a few Rollup fields set up, you’ll be able to create your own Reports any time you need!

Reporting on Project Management in Airtable

For this example, we’re going to report on Tasks from a Project Management base. You can add our Project Management Airtable template to your workspace to follow along and understand how everything is linked together.

In the base, we have a table for Projects, Tasks, Clients, and Reports. The Projects table tracks the main Project the team is working to complete. Tasks are individual tasks for a Project that each team member needs to complete. These two tables are linked together to associate specific tasks with projects. 

We’re going to create a Report displaying all Tasks for a related Project, the total number of tasks per project, the total hours for all tasks per project, and the total amount billed for all tasks per project. Then, we’ll create a document automatically with all the report data.

Related: How to Automatically Add a Set of Tasks to Each New Project Using Airtable Automations

Setting up the Tasks Table in your Airtable base

Each Task in the Tasks table has a Linked record field to the Projects table to relate every task to a Project. Each task contains an Hours field (Number) to enter in the hours spent on a specific task. There’s also an Hourly Rate field (Currency) for how much we bill the client per hour. 

Then, we have a Total/Task field (Formula) that calculates the total we will bill the client as Hours worked multiplied by the Hourly Rate. We also have a Status field (Single-select) to label whether the task is Upcoming, In Progress, or Completed. 

All of this information will be used in our Report to calculate the total hours and total charge per task. 

Setting up the Reports Table in Your Airtable Base

In the Reports table, we want to view all Tasks related to a specific Project, display the total hours spent, the total amount of tasks, and the total billed for tasks.

The fields in the Reports table are:

Report Name – Formula field (Primary field) with formula:

DATETIME_FORMAT(Date, 'MMMM - YYYY') & " - " & "Task Report" & " - " & {Project(s)}

Date – Date field

All Tasks – Linked to Tasks table with Allow linking to multiple records enabled

Project – Rollup Field to Tasks table > Project field

Total Projects – Rollup field with formula: COUNTALL(ARRAYUNIQUE(values))

Total Hours – Rollup field with formula: SUM(values)

Total Tasks – Rollup field with formula: COUNTA(values)

Total Billed – Rollup field with formula: SUM(values)

Create Report – Single-select field with options: Create and Processed

The Rollup Field formula calculates your data from other tables. We used the SUM(values) formula to sum hours and dollar values because they are rolling up a Number field and a Currency field from the Tasks table. We then used the COUNTA(values) formula because it’s rolling up a Formula field from the Task Name in the Tasks table.

Here are all available formulas for Rollup fields:

You can further refine your Reports with Views from your Linked tables. In your Report fields, you can add the filter Limit record selection to a view to only allow Tasks from the Completed Tasks view. This ensures that only Tasks that have been labeled as Complete can be added to the Report. 

Adding Airtable Records to a Report

Now that the Reports table is set up, we’re ready to create a Report. Each record in the Report table is 1 Report. Whichever Task records you add to the linked record field will be added to the Report.

Add any Tasks into a single Report record that need to be included in the Report. Since we want to create 3 different Reports – one for each Project, we’ll create 3 new records in the Reports table.

Add all Tasks related to the Project Batman Project. As each Task is added, the fields Total Projects, Total Hours, Total Tasks, and Total Billed begin to calculate totals automatically.

You can do this for each Project to create a new report. You can also create a Report for multiple Projects by adding Tasks from multiple Projects.

You now have a Report of your Projects, the total hours, total tasks, and total billed to the client! This will help you summarize any data in your Airtable base.

Automatically Generate a Report Document

If you need to share your Reports with others, you can create a document with your Report details and send it to anyone. Using On2Air Docs, you can create branded report documents for your company. 

See how AirOps Consulting Automatically Generates Documents using Airtable and On2Air to Save 3-4 Hours Every Week

Check out this tutorial on how to create automated documents with Airtable data. We’ll show you how to use the customer data you have stored in Airtable to automatically create a document in Google Docs, turn it into a PDF, and upload the PDF back into your Airtable base in an Attachment field.

– How to Create a Sales Contract PDF in Google Docs using Airtable

After the Google Docs and PDF documents are back in your Airtable base, you can email them to your colleagues or clients.

 – How to Send an Email Automatically from Your Base Using Airtable Automations

Automate Airtable Linking for Report Summaries

Sometimes, you need to automatically link specific records to a Report – without having to think about it or do it manually. You can do this with Airtable Automations!

Watch this video from Gareth at GAP Consulting to see how you can set up an automation to add records to your Report automatically.

Making Airtable Reports a Breeze

Reports in Airtable are as important as the data in any table. You need to be able to summarize all your company information. It’s as simple as an extra Reports table and some Rollup fields. 

This method can be used for something as common as invoices for any business or if you’re an Enterprise company with vast amounts of data in Airtable that needs to be summarized.