Airtable Reporting: Dashboards in Interface Designer vs Pushing Data to BI

Airtable is where the work happens, so it is also where people expect the numbers to come from. Then someone asks for "a dashboard" and you discover that Airtable's charting is deliberately simple, that half the metrics you need do not exist as fields yet, and that the finance team wants the same figures next to data that lives in Xero and Stripe.

This tutorial walks through building reporting on Airtable properly: shaping the data so metrics are computable, building dashboards in Interface Designer, and knowing the point at which you should stop and push the data to Looker Studio, a spreadsheet, or a warehouse instead.

Step 0: write the metric definitions down first

Before you build anything, get someone to finish these sentences:

• "Pipeline value is the sum of ___ for deals where ___, as at ___."
• "Utilisation is ___ divided by ___, for the period ___."
• "A project counts as late when ___."

Nine out of ten "the dashboard is wrong" complaints are actually two people using two definitions. Put the agreed wording in a Metric definitions table or in the interface's own description text so it travels with the chart. Include the grain (per record, per week, per person) and the cut-off rule for dates.

Step 1: make the metric a field

Airtable charts summarise fields. They do not compute business logic. So every metric needs to exist as a field somewhere before it can be charted.

Flags as formulas. Do not ask a chart to work out lateness. Add a formula field:

IF(
  AND({Due date}, OR({Status} != "Done", {Completed date} > {Due date})),
  IF({Status} = "Done", "Late (delivered)", "Late (open)"),
  "On time"
)

Now "late projects by owner" is a grouped bar chart, not an argument.

Bucketing for the x-axis. Charts group by a field, so create the grouping field. A month bucket:

DATETIME_FORMAT({Created}, 'YYYY-MM')

YYYY-MM sorts correctly as text, which MMM YYYY does not. For fiscal periods, keep a small Calendar table with one record per month, its quarter and fiscal year, and link records to it — far easier than nesting fiscal logic into every formula.

Aggregates as rollups. Money and counts should roll up the chain: Line Items.Total → Invoices.Amount (rollup SUM) → Clients.Revenue YTD (conditional rollup SUM, filtered to this year). Conditional rollups are the most underused reporting tool in Airtable — they let you keep "this year", "won only", or "billable only" variants side by side on the parent record.

Point-in-time numbers need a snapshot table. Rollups always tell you about now. If the question is "what did the pipeline look like at the end of each month?", no formula can answer it, because Airtable does not keep history. Add a Snapshots table with Date, Metric, Segment, Value, and a scheduled automation that writes one row per metric per period. Ten minutes of setup that saves you a year later.

Step 2: build the dashboard in Interface Designer

Interfaces are where Airtable-native reporting lives. A layout that works for most operational dashboards:

1. Top row: number elements. Three to five headline figures, each pointed at a filtered view — open pipeline, revenue this month, overdue tasks, utilisation. Big numbers answer 80% of the questions before anyone scrolls.
2. Middle: two or three charts. Bar for comparison across a category, line for trend over your month-bucket field, pie only for two or three slices and never for more.
3. Bottom: the record list the charts are made of. People do not trust a number they cannot click into. A grid or list element with the same filters turns "that looks wrong" into "ah, these three records have no owner".

Two settings do most of the work:

• Filters at the page level rather than per element, so a viewer changing the date range moves the whole dashboard consistently.
• User-based filters (filter where Owner is the current user) so one dashboard serves everybody and each person sees their own book of work. Combine that with view permissions so a team lead's version shows their whole team.

Give the interface its own filtered views rather than reusing the views the ops team edits daily. Someone re-sorting a grid should never change a chart.

Step 3: know Airtable's charting limits before you promise anything

Be honest about the edges early. In practice you will hit these:

• No joins across unlinked tables. A chart draws from one table (plus its lookups and rollups). If the metric needs data from two unrelated tables, you must link them or pre-aggregate.
• Limited chart types. Bar, line, pie, scatter, plus number and summary elements. No waterfalls, cohort grids, funnels with drop-off percentages, or dual-axis combos.
• No calculated measures at chart level. You cannot type a ratio into the chart; the ratio has to be a field.
• No history. Covered above — snapshots or nothing.
• Record-count ceilings. Very large tables slow interfaces down; pre-aggregate into a summary table rather than charting hundreds of thousands of rows live.
• Sharing. Interface sharing follows base collaboration and share-link rules. Read-only external viewers are possible, but plan-dependent — check before you promise the client a login.

Step 4: when to push the data out

Move reporting out of Airtable when any of these are true:

Signal	Where to go
Finance wants Airtable numbers next to Stripe/Xero/ads data	Looker Studio or a BI tool over a warehouse
You need cohort, funnel, or period-over-period analysis	BI tool
The audience is external and large	Published dashboard in a BI tool, not an interface share link
Someone just wants to pivot it themselves	Google Sheets or Excel
Board pack, same layout monthly	Sheets/Slides template fed on a schedule

The pragmatic middle path is a summary table. Keep the raw records in Airtable, run a nightly automation or script that writes one row per metric per period into a Reporting table, and point both the interface charts and the external tool at that. Everything downstream reads small, clean, pre-aggregated data, and your dashboards stop dragging.

Getting the data out

• Google Sheets. Simplest route: a scheduled automation, or a sync/connector that keeps a sheet mirroring a view. Then Looker Studio reads the sheet. Cheap, good enough for a lot of teams, and easy to hand over.
• Looker Studio direct. Community and partner connectors for Airtable exist; they are convenient but you inherit their refresh limits and API rate limits. Point them at a narrow view, not the whole table.
• Warehouse (BigQuery, Snowflake, Postgres). For anything serious: an ELT tool or your own scheduled job hitting the Airtable REST API, writing raw tables, and modelling on top. This is also how you get real history — every load is a dated snapshot.
• Rate limits matter. The API is limited per base per second; page through with offset, request only the fields you need, and pull from a filtered view. Our Airtable API quickstart covers tokens and scopes, and the scripting guide covers batching without tripping limits.

A worked example: agency utilisation

Question: "What percentage of each consultant's available hours were billable last month, by client?"

1. Grain: one Time Entry per person per day per project, with Hours and a Billable checkbox.
2. Fields: Month formula (DATETIME_FORMAT({Date}, 'YYYY-MM')); Billable hours formula (IF({Billable}, {Hours}, 0)).
3. On People: conditional rollups for Hours this month and Billable hours this month, plus a Capacity number field and a Utilisation formula (IF({Capacity}, {Billable hours this month} / {Capacity}) formatted as a percent).
4. Snapshot: a monthly automation copying each person's utilisation into Snapshots so the trend survives.
5. Interface: number element for team utilisation, bar chart of utilisation by person, line chart from Snapshots for the twelve-month trend, and a grid of time entries underneath for drill-down.

Everything on that dashboard is a field. That is the whole trick.

Common mistakes

• Charting a view that people re-filter daily — the numbers change and nobody knows why.
• Percentages built on tiny denominators; add a count element next to every rate.
• Blank dates silently excluded from date-grouped charts. Add a Missing date flag field and show the count.
• Duplicate records inflating sums. Fix data quality before you build charts on top of it.
• One dashboard for six audiences. Build one page per role instead; the underlying fields are shared anyway.

Where to stop

Airtable reporting is excellent for operational dashboards: what is late, who owns what, what is in the pipeline right now, is the team above or below capacity. It is not a BI platform, and trying to make it one produces a base full of brittle rollups. Draw the line at the point where the question stops being "what is happening in this process?" and starts being "how did this trend across the business over two years?"

BaseBrainers builds Airtable dashboards and the reporting pipelines behind them — see Airtable reporting and analytics, or tell us what you are trying to measure and we will tell you honestly whether it belongs in Airtable or downstream of it.