Build a custom report
Use the report builder to choose a source, filter it, pick columns or summarise it, and see the result live before you save. Or have Ask AI draft a report for you.
When no built-in report answers your question, build your own. The report builder lets you choose what each row of the report is, narrow it down, choose what to show or how to summarise it, and see the result against your live data as you go.
You need Reports: Create to build a new report, and Reports: Edit to change a saved one.
Open the builder
Select Create Custom Report at the top of the Reports menu, or on the Saved Reports page. The builder fills the whole screen:
- Data explorer on the left lists the tables you can report on and their fields.
- The middle holds the six steps that define the report.
- Live preview on the right shows the result.
At the top are the report's name, a switch between Build and Ask AI, Export and Save report. Select Saved reports at the top left to leave.
A new report starts on Quotes, showing the first five quote fields, up to 50 rows.
Build a report
Name it
Select Untitled report at the top and type a name.
Choose the source
In Data explorer, choose the table each row of the report should be, for example Jobs or Invoices. Choosing a source in the explorer starts the report afresh on that table.
Add filters
In Filters, select Add filter, choose a field, a condition and a value. Add more filters as needed, and choose Match all or Match any.
Choose columns
Select a field in Data explorer to add it as a column, or use Add column in Columns.
Summarise, if you want totals
In Summarise, choose a field under Group by to get one row per group, with calculations such as a count or a sum.
Set the order and size
In Sort & limit, choose what to sort by and the Row limit.
Check the preview, then save
Check the result in Live preview and select Save report. See Saved reports.
The preview runs against your live data shortly after each change, so you see the effect of every filter and column straight away.
Choose a source
The source is the record each row of the report represents. You can start a report from any of these, grouped as they are in the builder:
| Group | Sources |
|---|---|
| Sales & quoting | Quotes, Quote items, Acceptance sessions, Invoices, Invoice items |
| Jobs & work | Jobs, Job items, Job financials, Job costs, Schedule segments |
| Labour | Timesheets, Employees |
| Scheduling | Bookings, Meetings |
| People | Contacts, Sites, Contact groups |
| Purchasing | Suppliers, Purchases, Purchase items, Scanned documents |
| Catalogue | Products |
| Finance | Chart of accounts, Ledger transactions, Bank accounts, Bank statements, Bank statement lines |
| Records | Assets |
| Reference | Job categories, Lead sources, Payment terms |
The explorer shows how many records the source has, and a note on some sources. For example, Jobs counts main jobs only, with the parts a job is split into rolled up into it, and Contacts holds both businesses and the people inside them.
Use related data
Under Related data, the explorer lists the tables linked to the source, such as a quote's contact or a job's category. Expand one to see its fields, and select a field to add it as a column. Each link is marked one (it matches one record) or many (it can match several).
You don't set up links separately: the report follows a link when you use one of its fields. Linked data in this report, under Source, lists the links in use.
Spot empty fields
A field that nothing in your company has ever filled in is marked empty in the explorer. A report on it would come back blank, so choose another field.
Filter the records
Filters narrows the records before anything is counted. Each filter reads Where field condition value. With Match all, a record must meet every filter; with Match any, it must meet at least one.
The conditions depend on the kind of field:
| Field | Conditions |
|---|---|
| Text | is, is not, contains, does not contain, starts with, is empty, is not empty |
| Status | is, is not, is one of, is empty, is not empty |
| Amounts and numbers | =, ≠, >, ≥, <, ≤, is empty, is not empty |
| Dates | in the last, is after, is before, is empty, is not empty |
| Yes or no | is true, is false |
- in the last offers 7 days, 30 days, 90 days, 6 months, 12 months and This financial year.
- For a status, pick the value from the list. For is one of, separate the values with commas.
- Type amounts in dollars, and dates as YYYY-MM-DD.
With no filters, the report covers every record of the source.
Choose columns
Each column shows its field, the link it comes through (for example, Contact › Name) and its type. Select × on a column to remove it. Add column reaches the source's own fields and fields on linked tables.
Summarise
To turn a list into totals, choose a field under Group by. The report then has one row per value of that field, and starts with a count of records in each. Add up to two more groupings with then by…. When you group by a date, choose by day, by week, by month, by quarter or by year.
Under Calculations, select Add calculation and choose:
- Count or Count distinct, over every record or over a field;
- Sum, Average, Minimum or Maximum, over an amount or number field.
Give each calculation a Column heading if you want a different name in the result. Once a report is summarised, it shows its groupings and calculations instead of the columns you picked.
Sort and limit
In Sort & limit, choose a column, grouping or calculation to sort by, and High to low or Low to high. With nothing chosen, a list shows the newest records first and a summary follows the first grouping.
Row limit sets how many rows the report returns: 25, 50, 100, 250, 500 or 1000.
Choose how it looks
Visualise sets how the saved report and its dashboard widget are drawn. Table works for any report. The chart options, such as Bar, Line and Pie, need the report to be summarised first. In the builder's preview and on the saved report's page, charts are drawn as bars; a dashboard widget draws the chart type you chose.
Read the preview
Live preview has three tabs:
- Result: the rows or groups the report returns, with how many there are.
- Chart: the first calculation for each group, top 10 groups first. It needs a summarised report.
- Plan: the report in plain words, step by step: its source, linked data, conditions, columns or groupings, calculations, order and row limit.
Under the preview, What this report says describes the report in one sentence, and notes under the result point out anything that affects how to read it.
Draft a report with Ask AI
Ask AI drafts a report from a plain-English request, which you then check and save yourself. It can read the same tables as the builder and check which fields your company fills in, but it can't save, share or change anything.
Switch to Ask AI
Select Ask AI at the top of the builder.
Ask for a report
Describe the report you need, for example “Invoiced value by month for the last year”, and select Ask. Or pick one of the example requests.
Review the proposal
The assistant shows its steps, then a proposed report with its plan and a preview of the result. Use the suggestions under it, such as Group it by month, to ask for a change.
Open it in the builder
Select Open in builder. The draft loads into Build, where you can change anything before you save it.
Keep requests to a sentence or two. If the assistant can't turn a request into a report, it says so; name the records you want listed and what you want measured about them, and try again.
Troubleshooting
Limits
A report can have up to 25 columns, 20 filters, 3 groupings and 6 calculations, and return up to 1,000 rows.