Skip to content

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:

TableWhat it contains
Sales documentsSales quotes, orders, delivery notes, invoices and credit notes
Purchase documentsSupplier quotes, purchase orders, goods receipts, received invoices and purchase credit notes
Business partnersCustomers, suppliers and sales agents
ItemsThe item catalogue
StockHow much there is of each item in each warehouse
InstalmentsThe open items of receipts and payments
Journal linesThe journal movements
BatchesThe batches with their expiry date
Price listsThe price lists
Payment methodsThe payment methods master
Stock movementsWarehouse incoming and outgoing movements, with their document
WarehousesThe warehouses master
VAT registerThe register: one row per VAT rate on each invoice
Treasury accountsCash, banks and card terminals
Cash countsThe cash closings issued, with balance and discrepancy
Direct debit and payment batchesThe bank batch operations
Item familiesThe families, with their parent family
Stock by batch and serial numberHow 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:

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.

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:

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:

Each parameter already added can be fine-tuned on its card:

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 seeWhat has happenedWhat to do
‘The report needs a title.’The Report title is missingEnter it in the header
‘Choose the main entity of the report.’No table has been chosenGo back to the Main table step
‘Add at least one column.’The design has no columnsAdd them in the Columns step
‘Column ‘…’: it must have a field OR an expression (exactly one).’A formula column was left emptyWrite the formula or remove the column
‘Column ‘…’: field ‘…’ does not exist.’The field was used and is no longer availableRemove 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 tableGive it another name
‘Cycle between calculated fields: …’Two calculations refer to each otherUndo the circular reference
‘Summary mode needs at least one group.’Totals only without any groupAdd a group, or untick Totals only
‘A report allows only one detail (child). Remove the current one before adding another.’There is already a detailClick Remove on the one that is there
‘Parameter ‘…’ clashes with a field of the entity.’The parameter name already existsChange it
‘Column ‘…’: it accumulates over the main table while there is a detail…’It is a warning, not an errorSee Group and total a report
‘Column ‘…’: width is not positive; 1 will be used when printing.’The saved width isn’t validChoose one from the list
‘The query returned no rows.’ in the previewThe filters don’t let anything throughRemove filters and try again
‘Could not read the data: …’ above the previewThe design is correct but the query fails for the reason givenFix 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 catalogueAsk for the permissions

See also: Group and total a report · Run a report · Quick guide to expressions

More about Reports