How do I group and total a report?
Grouping is what turns a flat list into a report: sales by customer, stock by warehouse, open items by payment method. It is done in the Groups and totals tab of the Report designer.
What is needed for totals to appear?
Two things, and both are needed:
- At least one column marked as Accumulate. You mark it in the Columns tab, with the Accumulate tick box on each column. Without any column that accumulates, there is nothing to add up.
- At least one group, if you want subtotals. The grand total appears just by having columns that accumulate.
It only makes sense to accumulate columns of amounts or quantities. Ticking Accumulate on a text column doesn’t give an error, but it adds up to zero.
How do I group by a field?
In the Groups and totals tab, click + Group by field. A block appears with:
- Field — what you group by. The list has the fields of the main table, those of the detail (marked ‘Detail · …’) and the calculated fields.
- Label — the text that goes with the group’s value in the header.
- Subtotals — draws the total line when each group closes. It comes ticked.
- Repeat header — writes the group header again at the start of each page.
You can chain several groups. The first is the outermost level; the last, the innermost. Each one is removed with Remove.
In the PDF, each group produces a header with its value at the start and a subtotals line at the end. The on-screen view, the CSV and the Excel get the same subtotal line, ‘Total’ and the group’s value, in bold on screen and in the Excel; and at the end, the report total. The group header only appears in the PDF.
What is the ‘Carried forward’ at the foot of the sheet?
It is what the accumulating columns have added up from the first sheet to that one. It is used to check a long report sheet by sheet without waiting for the total.
It appears at the foot of every sheet except the last, which already has the grand total. You don’t have to turn it on: it is enough to have a column marked Accumulate.
With Totals only it doesn’t appear.
What is the ‘Balance’ column on a statement and how is it different from a total?
A total answers ‘how much does this group add up to’ and appears once, when the group closes. A balance answers ‘where does the account stand when it reaches this line’ and appears on every line, with a different value on each. It is the last column of a customer statement: 100, then 150, then 120.
The reports in Customers → Customer reports → Customer accounts have it. Two things worth knowing when you read it:
- It starts again for each account. One customer’s statement never carries over the balance of the previous one.
- It doesn’t reset each month. Even if the report shows a subtotal per month, the balance keeps running: the first movement in April starts from what was left in March.
Is the balance on the statement what the customer owes?
Only if you don’t restrict it by dates. Without a range, the balance on the last line is the real balance of the account.
If you ask for a range —‘March’—, the balance starts counting at zero on the first day of the range, so it shows what moved in March, not what the customer owes. For a customer who was carrying 5.000 €, the March statement doesn’t show them.
The Desktop ERP has an ‘include previous balance’ tick box for this; it isn’t here yet. In the meantime, there are two ways to see the real balance: run the statement without a date range, or look at the account in Accounting, which does show the previous balance and the closing balance.
Can I group by something that isn’t a field?
Yes. Click + Group by formula instead of + Group by field. Instead of a drop-down, the formula editor appears.
It is for breaks that don’t exist as a field: the month of the date, the first letter of the name, an amount band. Each different value the formula returns opens a group.
A group must have a field or a formula, exactly one. If you leave both empty, the designer marks it as an error and won’t let you save.
Like any formula in the designer, it can’t use detail fields.
How do I get one line per customer instead of one per invoice?
With the Totals only tick box, in the Totals level block.
The procedure is the one the screen itself gives: group by the field you want as the line (the customer, the product, the warehouse) and tick Totals only. The result has one line for each value of the last group, with the accumulating columns added up.
On that line, the columns that don’t accumulate and aren’t a group key come out empty: there is no single value to put when several rows have been merged into one.
Totals only needs at least one group. Without one, the designer gives the error ‘Summary mode needs at least one group’.
Like normal subtotals, Totals only is also shown in the on-screen view, the CSV and the Excel, not only in the PDF.
Can I choose the subtotals when running it, without going into the designer?
Yes, if whoever designed the report set it up for that. In the designer’s Parameters tab, a parameter can be marked It is ‘Subtotals by’: instead of asking for a value to filter by, it offers a list of groupings —Month, Customer, Payment method, Sales agent, No subtotals…— and it appears in the run window as one more drop-down, together with the other details the report asks for.
When you run it, the grouping you choose replaces the groups the design had: if you choose ‘Month’, the report comes out grouped by month even if the design had no groups. If you don’t choose anything, it comes out as usual.
There are standard reports in which the design’s group is the report, and there the grouping you choose is added instead of replacing it:
- In the summaries (Sales summary by products, Sales analysis by product and by customer) it goes on the outside: ‘Customer’ gives the products of each customer, with their subtotal, and the line is still the product.
- In the bank, cash and card statements and in the warehouse Stock movement detail it goes on the inside: ‘Month’ gives the total for each month within each account or each item, and the balance keeps running, it doesn’t go back to zero each month.
If you choose the same thing the design already groups by, it comes out as usual.
You can combine it with the Totals only tick box in the run window (see Run a report): choose ‘Customer’ in Subtotals by, tick Totals only, and you get one line per customer without opening the designer.
How do I sort the rows?
In the Sort block of the same tab, click + Sort criterion, choose the field and tick Descending if you want it the other way round. You can chain several criteria.
Sorting and groups are different things, but they don’t get on if they contradict each other: for a group to break properly, its rows have to come one after another. Sort by the same field you group by.
Detail fields can also be used for sorting, with the prefix ‘Detail · ’.
Why does a subtotal come out multiplied?
It is the warning ‘Column ‘…’: it accumulates over the main table while there is a detail’, and it isn’t a fault in the programme: it is what really happens.
When the report has a detail, there is one row for each detail line. The invoice’s total amount, which is a header figure, is repeated on each of its lines. When it is added up, it is added as many times as the invoice has lines.
The solution is to accumulate columns from the detail (the line amount), not from the header. If what you need is the document total, remove the detail and work at document level.
The designer warns you but doesn’t block it: there are cases where that behaviour is exactly what you are after.
If something goes wrong
| What you see | What has happened | What to do |
|---|---|---|
| No totals line appears | No column is marked Accumulate | Tick it in the Columns step |
| No subtotals appear, only the final total | There are no groups, or the groups have Subtotals unticked | Add the group and tick Subtotals |
| ‘Summary mode needs at least one group.’ | Totals only without a group | Add the group you want one line for |
| ‘Summary mode without any column that can be accumulated…’ | It is a warning: the line will come out with just the keys | Tick Accumulate on the amount column |
| ‘Group ‘…’: it must group by a field OR an expression (exactly one).’ | The group was left unfilled | Choose the field or write the formula |
| ‘Group ‘…’: field ‘…’ does not exist.’ | The group’s field is no longer available | Remove the group and create it again |
| ‘Sort: field ‘…’ does not exist.’ | A sort criterion points to a field that is no longer there | Remove that criterion |
| The groups repeat and break badly | The rows aren’t sorted by the group field | Add that field to Sort |
| The subtotals come out multiplied | The report has a detail and a header column is being accumulated | Accumulate the detail column |
See also: Create a custom report · Run a report · Quick guide to expressions
More about Reports
- How do I see what share of the year’s sales falls in a period?
- How do I change the design of an invoice?
- How do I create a custom report?
- Where is each report?
- How do I choose which layout a document is printed with?
- How do I export a report to Excel?
- How do I print labels?
- Running reports and formatting documents
- The standard reports in each area
- How do I run a report?