Appearance
Report builder
Also known as
- ad-hoc report
- custom report
- query builder
- dataset
- pivot
- group by
- aggregate
- filter
- self-service BI
- report designer
The report builder answers questions the standard reports do not: pick a dataset (items, invoices, purchase order lines, employees, audit log …), choose columns, filter, group and total, and see the result update as you go. You can chart grouped results, export them to CSV and save the query to run again later.
Build a query
- Go to ReportsReport builder, or select Report builder on All reports.
- Choose a Dataset. Datasets are grouped by module; you see only those your permissions allow. All its columns are selected and the Preview shows the first rows.
- Under Columns, clear the columns you do not need and use the arrows to change their order.
- Under Filters, select Add filter, then choose the column, the condition and the value. Add as many filters as you need; all must match. Without filters the builder says All rows.
- To total by group, choose one or more columns in Group by under Group and summarise, then pick an aggregate for each number column: Sum, Average, Count, Count distinct, Minimum or Maximum. Without aggregates each group shows a count.
- Under Sort, choose a column and Ascending or Descending, or keep Natural order.
The header shows the dataset and the number of rows. The preview shows up to 200 rows (first 200 of … when there are more). Grouped results show only the group columns and aggregates.
| Condition | Value to type |
|---|---|
| is, is not, contains, starts with, greater than, at least, less than, at most | One value. Dates as yyyy-mm-dd. |
| between | Two values separated by a comma: from, to. |
| is one of, is none of | Several values separated by commas: a, b, c. |
| is empty, is not empty | Nothing. |
Chart a grouped result
When the query is grouped, a Chart preview appears above the table, with the aggregates by the first group column. Choose Bar, Line or Donut.
Export or save
- Export CSV downloads the rows in the preview.
- Save as report asks for a Name and saves the query; select Save report. You see Report “…” saved and it appears under Saved reports on All reports. Reopened, the button reads Save changes.
Saved reports are kept on the server: they follow you to any computer, can be shared, and can be shown on a dashboard with the Saved report widgets. Saving needs the Manage reports permission; without it Save as report is unavailable and says so. Queries saved in this browser by an earlier version stay listed under Saved by you until you open and save them, which moves them to the server.
| Message | Meaning |
|---|---|
| Choose a dataset to start. | No dataset is selected. |
| No rows match these filters. | Loosen a filter. |
| The query failed. | A filter value does not fit its column, for example text in a date filter. |
Differences by role
The builder needs Permission: reports.view; each dataset also needs its module's permission. The builder only reads data.
