How do I create a custom report?
The Report designer lets you put together your own report without any technical knowledge: you choose the table, drag the columns and save. The report stays in Reports → Reports to run whenever you need it.
What do I need before I start?
Nothing more than access to the Reports group in the menu. The designer only offers the tables your role can read, so what you see depends on your permissions.
It helps to be clear about two things beforehand: which table the data comes from and which columns you want to see. Everything else can be changed later.
How do I open the designer?
Open the Reports group in the menu and click Report designer. You can also get there from Reports → Reports with the New report button, and from the record of a report already saved with Edit.
At the top right there are three actions that are always to hand: Save, Preview and Back (to the screen you came in from). Below them, the title of the report, the area it appears in and the six steps, each with its icon, across the full width of the screen.
Click a step to go to it, or go through them with Previous and Next at the foot of the step, which also tells you which step you are on (‘Step 3 of 6’). Until you choose the main table, the rest of the steps are locked.
At the very bottom there is a Report preview that always stays visible and rebuilds itself every time you change something. When you open a report already saved, it is filled in from the start.
Step 1: which table does the report come from?
The Main table tab shows a card for each available table, with its name, a description and how many fields it has. Click the one you want.
The tables on offer are:
| Table | What it contains |
|---|---|
| Sales documents | Sales quotes, orders, delivery notes, invoices and credit notes |
| Purchase documents | Supplier quotes, purchase orders, goods receipts, received invoices and purchase credit notes |
| Business partners | Customers, suppliers and sales agents |
| Items | The item catalogue |
| Stock | How much there is of each item in each warehouse |
| Instalments | The open items of receipts and payments |
| Journal lines | The journal movements |
| Batches | The batches with their expiry date |
| Price lists | The price lists |
| Payment methods | The payment methods master |
| Stock movements | Warehouse incoming and outgoing movements, with their document |
| Warehouses | The warehouses master |
| VAT register | The register: one row per VAT rate on each invoice |
| Treasury accounts | Cash, banks and card terminals |
| Cash counts | The cash closings issued, with balance and discrepancy |
| Direct debit and payment batches | The bank batch operations |
| Item families | The families, with their parent family |
| Stock by batch and serial number | How much is left of each batch or serial number in each warehouse |
Sales and purchases are two different tables even if the document is the same paper: each one only brings the documents of its own side and calls the business partner by its name —Customer in sales, Supplier in purchases—. They can’t be mixed in one report, and each one is shown according to your permission: someone who only has Purchases sees the purchases table, and vice versa.
The tables on offer depend on your permissions: only those of the areas you can view appear.
Changing table resets the design: columns, detail, filters, groups and sort order are lost. If you had already started, the designer asks for confirmation first with ‘Yes, switch’ or Cancel.
When you choose the table, the header fills in Appears in by itself: the reports area where the report will appear —sales documents → Sales, purchase documents → Purchases, instalments → Receipts and treasury, journal lines → Accounting, items or stock → Warehouse…—. It is a suggestion: change it in the drop-down if you want it in another area, or choose Only in ‘Custom’ so that it doesn’t appear on any module page.
If you change table, the area follows it as long as it is the one that was suggested (from sales documents to items, it goes from Sales to Warehouse). If you had changed it yourself to another one, yours stays.
Step 2: one line per document or one per detail line?
The Report tables tab decides the level of the report. There are two options:
- One row per ‘…’ — one row per record, without breaking it down. It is the document summary.
- One row per detail line — one row for each line. It is what you want if the report is about products sold and not just invoices.
When you choose the detail level, the Available details list appears and you have to click Add on the one that applies. A report allows only one detail: to change it, first Remove the one that is there.
In the column on the right, Related data shows which other tables you can bring columns from without doing anything: for example, in Sales documents the customer’s name, Tax ID (NIF), town / city and email are already there (in Purchase documents, the supplier’s). They are available directly in the Columns step.
When you add the lines detail, besides the fields of the line itself, two fields of the item come along: its Item code and its Item description. Careful with the description: it is the one on the item record, not the description typed on the line, which may be different. On free-text lines, which have no item, both columns come out empty. The line’s Warehouse code, its Delivery date and the Quantity delivered also come along: on an order, what has already been delivered with delivery notes; what is still to be delivered is the quantity ordered minus this.
Going back to ‘one row per document’ removes the detail, and the designer asks you to confirm first.
What if the two tables aren’t related?
In the same tab, the collapsed block Advanced: join by fields lets you join any table as a detail by matching a field of the main table with one of its own fields, even if there is no natural relationship.
You have to choose Detail table, Main table field and Detail field, tick the box ‘I understand the cost and want to join by fields’ and click Join by fields.
The screen’s own warning says it: it joins by values, not by index, and it can be slow on large tables. Only use it when there is no other way.
Step 3: how do I set up the columns?
The Columns tab has two panels. On the left, Available fields, grouped into Main, Details and Calculated. Click a field and it is added as a column.
On the right, Selected columns. Each one can be adjusted:
- Label — the title that is printed.
- Width — Narrow, Normal, Wide or Very wide. It is a relative weight: the real width is shared out across the paper.
- Format — how the value is written. For a number: 1.234, 1.234,56, 1.234,56 € or 12 %. For a date: 31/12/2026, 31/12/2026 14:30 or December 2026. With (automatic), a date comes out as 31/12/2026 and an amount with thousands separator and two decimals (21.895,22), or with three or four if it has them, like a price of 1,459. A whole number —a document number, a number of units— comes out as it is. Totals and subtotals come out with two decimals.
- Alignment — Automatic, Left, Centre or Right.
- Accumulate — marks the column so that it adds up in the subtotals and in the total.
The ↑ and ↓ arrows change the order of the columns, and ✕ removes it.
If you need a format that isn’t in the list, choose Other… and type the code in Technical format.
How do I make a calculated column?
There are two ways and they are used for different things.
For a calculation that only appears in one column, click + Formula column in the Columns step. Type the Label and the Formula, and click Add.
For a calculation you want to reuse in several places, go to the Calculated fields and filters tab and use the Calculated fields block: enter a Name (for example TotalConIva) and its formula, and click Add calculated field. From then on you can use it as [TotalConIva] in any other formula, and also add it as a column.
The formula editor validates as you type and suggests the available fields. The syntax is explained in the quick guide to expressions.
Formulas can’t use detail fields. Only fields of the main table, its related data and other calculated fields. Detail columns can be shown, but they don’t go into calculations.
The standard reports do come with calculations made with detail fields, such as the Outstanding on each order line or the Commission on each collected bill. If you open one in the designer, those columns appear and can be removed, moved or renamed, but the calculation itself can’t be changed from the screen.
Step 4: how do I filter the rows?
The Calculated fields and filters tab has two ways of filtering, and it is worth knowing how they differ.
Range filters is what you should use whenever you can. You choose a field, a From and a To, and click Add range. The filter goes to the database, so only the rows that are needed are fetched.
Formula filter lets through the rows where the formula is true, for example [Total] > 100. It is evaluated in memory, on the rows that have already been fetched. It is much more flexible, and much more costly: narrow things down first with a range filter. For “equal to”, both = and == work: [Almacen.Codigo] = "ALM01" and [Almacen.Codigo] == "ALM01" filter the same.
Ranges can be left half-filled: only From, only To, or both.
Sales and purchase documents bring, in addition to their own fields, three that come from their detail: Pending receipt (or Pending payment), what is still to be collected on their instalments; Collected (or Paid), what has already been collected; and Number of lines. They are used like any other field, also in a range filter: Pending receipt from 0,01 leaves only the invoices that still owe something. A document without instalments is worth 0 in both.
They also bring where they come from and what has come out of them: Delivery note lines (how many lines come from a delivery note; 0 is a direct invoice), the Source document number (the invoice a credit note corrects, for example), Invoice that includes it (on a delivery note), Delivery note that fulfils it (on an order) and Corrected by (on an invoice, its corrective invoice or its credit notes). On the detail lines, the Source document number is the delivery note or the order each line comes from.
If instead of fixing the filter here you would rather the report asked for it each time it is run, that is what the parameters in step 6 are for.
Step 5: how do I group and add up?
The Groups and totals tab is the one that turns the flat list into a report: it groups the rows, draws subtotals at each break and decides the final order. It is also where the Totals only tick box is, which gives one line per customer, per product or per whatever you group by.
It is explained separately, because it is the most used part and the one that raises the most questions: Group and total a report.
Step 6: how do I make the report ask me for the dates?
In the Parameters tab you define what the run window will ask for each time.
The short way: ‘Add standard range’. The drop-down offers the questions that the standard reports ask about that same table —Date from / to, Customer from / to (with lookup), Payment method, *Sorted by*…— already worked out: you choose one, click Add and the pair of parameters appears with its field, its lookup and its names. Only those the table can honour are offered; the ones you have already added disappear from the list.
The long way: ‘New parameter’. Fill in the block and click Add:
- Name — the identifier, for example
Desde. It is the one used in formulas. - Label — what whoever runs the report will see.
- Type — Text, Number, Date or Boolean. It decides which control appears.
- Field (optional) — if you link it to a field of the main table, the value filters by it on its own. If you leave it at — no field (symbol) —, it is available as a symbol in formulas.
- End — only appears if you have chosen a field. Equal, From (lower limit), To (upper limit) or Empty / filled, which doesn’t compare a value but whether the field is filled in.
- Required — if ticked, the report doesn’t run without that detail.
Each parameter already added can be fine-tuned on its card:
- Lookup — the master used to search for the value: customers, suppliers, sales agents, items, payment methods, treasury accounts, carriers, warehouses, price lists or ledger accounts.
- Default value — what the window opens with. Empty, a fixed value, or a formula starting with
=(for example=Today()), which is calculated when it opens. - Drop-down options — one per line,
valor=etiqueta. With options, the parameter is asked for in a drop-down instead of being typed. - It is ‘Sorted by’ — the parameter doesn’t filter: it chooses which field the report is sorted by. Its options are fields of the table (
Fecha=Fecha,Numero=Número); if you want to sort by several, separate them with commas in the value. - It is ‘Subtotals by’ — the parameter doesn’t filter: it chooses what the report is grouped and subtotalled by when it is run, replacing the groups of the design itself. Its options are groupings, one per line:
mes:Fecha=Mes,campo:Cliente.Nombre=Cliente,trimestre:Fecha=Trimestre,anio:Fecha=Año, and an empty one to offer ‘No subtotals’. Whoever runs the report sees it as one more drop-down, together with the other details asked for (see Can I choose the subtotals when running it?).
To ask for a range by hand you need two parameters on the same field: one with end From (lower limit) and another with To (upper limit). The standard range does it for you.
A parameter can’t have the same name as a field of the table or a calculated field: the designer rejects it and says so.
How do I test it and save it?
The table at the bottom shows the columns and the first rows of the current design. If the result is long, it warns you that it only shows the first rows. The Preview button also opens the report as a PDF in another browser tab, just as it would be printed.
The preview uses the same values the report opens with when it is run: the dates of the active financial year, the code ranges from start to end and the default values of each parameter. A required parameter with no default value doesn’t filter in the preview.
In the header there are two badges that tell you the state of the design. ready to save means there are no errors. A red badge with the number of errors means something is missing: click it and the list opens out, one error per line. Warnings don’t prevent saving.
If the design has no errors but the data can’t be read, Could not read the data appears above the table, with the reason.
Enter a Report title —it is required and it is how it will appear in Reports → Reports— and click Save. A confirmation message appears with the number it has been given.
If you leave with Back while you have unsaved changes, the designer warns you and offers Leave without saving or Stay here.
What happens when I save it?
The report goes into the catalogue and appears straight away, for anyone with permission on its tables, in two places: in Reports → Reports, in the tab of the area given in Appears in, and on that module’s reports page (Sales reports, Receipt reports…), in the More reports group. If you saved it with Only in ‘Custom’, it only appears in that tab.
The report is single and shared: if you edit it later, it changes for everyone. There isn’t a copy per user.
Saving doesn’t run anything or leave any report made: only the design is kept.
If something goes wrong
| What you see | What has happened | What to do |
|---|---|---|
| ‘The report needs a title.’ | The Report title is missing | Enter it in the header |
| ‘Choose the main entity of the report.’ | No table has been chosen | Go back to the Main table step |
| ‘Add at least one column.’ | The design has no columns | Add them in the Columns step |
| ‘Column ‘…’: it must have a field OR an expression (exactly one).’ | A formula column was left empty | Write the formula or remove the column |
| ‘Column ‘…’: field ‘…’ does not exist.’ | The field was used and is no longer available | Remove that column and add it again |
| ‘Calculated field ‘…’: clashes with a real field of the entity.’ | The name is already used by a field of the table | Give it another name |
| ‘Cycle between calculated fields: …’ | Two calculations refer to each other | Undo the circular reference |
| ‘Summary mode needs at least one group.’ | Totals only without any group | Add a group, or untick Totals only |
| ‘A report allows only one detail (child). Remove the current one before adding another.’ | There is already a detail | Click Remove on the one that is there |
| ‘Parameter ‘…’ clashes with a field of the entity.’ | The parameter name already exists | Change it |
| ‘Column ‘…’: it accumulates over the main table while there is a detail…’ | It is a warning, not an error | See Group and total a report |
| ‘Column ‘…’: width is not positive; 1 will be used when printing.’ | The saved width isn’t valid | Choose one from the list |
| ‘The query returned no rows.’ in the preview | The filters don’t let anything through | Remove filters and try again |
| ‘Could not read the data: …’ above the preview | The design is correct but the query fails for the reason given | Fix what it says; if you can’t make sense of it, tell support, with the text |
| ‘There are no tables available for your role.’ | Your role can’t read any table in the catalogue | Ask for the permissions |
See also: Group and total a report · Run a report · Quick guide to expressions
More about Reports
- How do I group and total a report?
- How do I see what share of the year’s sales falls in a period?
- How do I change the design of an invoice?
- 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?