Why a simple ERP report is never simple
“Sales by rep, by customer group, with subtotals at every level” takes one sentence to ask and four separate skills to answer. AxBIC’s new Suggest a Report builds that report from the result already on your screen — and sends no row, no figure and no customer name anywhere to do it.
One question, four skills
A report an ERP user describes in one sentence usually needs four different kinds of knowledge, and they rarely sit in one person.
Skill | What it means for one report | Without it |
|---|---|---|
The schema | Which of several hundred tables holds the fact, and what the column is called. In SAP Business One the customer code is CardCode on OCRD and an invoice line lives in INV1, not OINV. ABM, Sage X3 and MYOB Exo each name the same business fact their own way. | Convincing numbers from the wrong table — a credit note counted as a sale. |
SQL | Joining a header to its lines without multiplying the header’s values, aggregates inside CTEs, window functions for a running balance, an end date that actually includes the last day. | Totals inflated by the number of lines on each invoice. It passes a glance. |
Accounting | Knowing what the answer should be before you see it: what “the end of May” means when the period closes on another date, which subledger entries have not reached the general ledger, how an aged trial balance rebuilds an open-item ledger at a point in time. | A report that is technically correct and accounting-wrong, which is worse than no report, because somebody acts on it. |
Layout | Which columns belong in a group heading and which repeat on every line, where each subtotal sits, what to freeze in Excel, portrait or landscape. | A data dump: accurate, four thousand rows long, read once. |
The aged trial balance is the honest example. The first time somebody writes one that agrees with the general ledger, it is a day’s work — and most of that day is the fourth column of this table, not the second.
The queue is the real cost
Because those four skills rarely sit in one person, report requests queue — behind the one analyst who can write them, or behind a consultant’s day rate and a two-week lead time.
The visible cost is the wait. The quieter cost is that people stop asking. A manager who wants sales by rep by customer group for this month, and knows that is a three-week request, looks at last month’s report instead and makes the decision anyway. The report nobody requested never appears in a backlog, so nobody counts it — and it is usually the more expensive of the two.
Suggest a Report
AxBIC already answers a question asked in plain English: it writes the SQL and runs it. Suggest a Report takes the step after that and lays the result out as a finished report.
- There are three ways to reach it, depending on where you are:
- With a result on screen, press Report Builder and choose Suggest a Report — the red choice at the top of the chooser.
- Or press backslash in the question box and choose Suggest a Report from the menu that appears.
- Or open History, select a question somebody has already asked and marked correct, and press Suggest a Report.
All three end in the same place: the Report Builder, with its five steps already filled in — grouping, display columns and group headers, subtotals and calculations, sort order, page layout. Check it, change what you want to change, press Save.
Nothing is saved for you. A report in the saved list is one the scheduler can email on a timetable and a dashboard can show, and a report nobody has read should not get there. While AxBIC is working the Ask button reads Reporting… and a wait dialog sits in front of the window, with a Cancel button that means it.
Multi-level grouping, without the week
The report that takes longest by hand is the one with groups inside groups: sales reps, then customer groups within each rep, then customers within each group — each level with its own heading row and its own subtotals.
AxBIC works that out from the result itself. It looks for columns that are not numbers and that repeat often enough to be a heading rather than a line: twelve customers across 480 rows is a report, three hundred and ten item codes across 480 rows is a list with a heading on every line. Then it decides the order, offering labels before dates and dates before document numbers — because sales grouped by customer and then by month is a customer report, and the same two columns the other way round are a daybook.
It works out what adds up as well, and puts a subtotal under every level with a grand total at the end. If nothing in a result groups usefully, or nothing in it is a number, it says so rather than offering a report that is neither.
None of the manual work disappears — it stops being compulsory. Expert mode still shows every setting on one screen and the Wizard still asks for them a step at a time, so a suggestion is a draft you edit, not a black box you accept. From there the report exports to Excel with the database name on top and the headings frozen, or goes on a schedule, or onto a dashboard with drill-through to the rows underneath.
What leaves your database, exactly
No row, no figure and no customer name. That is the claim, and it is worth being specific about what it means, because “we send only metadata” is said by a lot of software that does not.
To propose a layout, AxBIC sends three things: the question’s SQL with its values taken out, the column names, and a count of how many different values each column held. Where your statement said
WHERE CardCode = 'C1234' AND DocDate >= '2026-07-01'
what is sent says
WHERE CardCode = ? AND DocDate >= ?
Numbers go the same way. Comments are dropped rather than blanked, because a note somebody wrote in a query is not part of its shape. What remains is table and column names — which the AI already receives as the schema with every question you ask. Suggesting a report therefore sends nothing that asking the question did not.
What comes back is checked before you see it. Every column the suggestion names must be one the query actually returns, every setting must be one the Report Builder knows, and a total must be on something that is a number. A suggestion that fails any of those is discarded and the arrangement AxBIC worked out by itself is used instead: a plainer report, and a correct one.
And if your site has no AI key configured at all, Suggest a Report still works. The arrangement AxBIC derives from the result’s shape needs no key and no network — it is grouped and totalled sensibly and named after the question that produced it. The status bar tells you which of the two you were given, so a suggestion that quietly stopped being the AI’s does not look the same as one that still is.
What we are not claiming
One caveat, stated plainly. The rule for how often a column has to repeat before it is worth a heading was chosen by judgement, not measured against a library of real result sets. It gets a sales-by-customer report right. On a result shaped in a way we have not seen, it will sometimes group by the wrong column.
So treat the first few suggestions as a draft to correct rather than a report to file — which is why AxBIC opens the Report Builder instead of saving anything. And tell us when one is obviously wrong: which column it grouped by, and which you would have chosen, is exactly what we need to improve it.
Suggest a Report is in AxBIC 2.1.0.119 and later. To see it against your own data, get in touch.