ERP Report Automation: Six Excel Reports Rebuilt as Live Data Across 52 Branches

ERP report automation means replacing the Excel files your ERP exports on a schedule with live queries against the same data, so any version of the report can be pulled for any branch and any date range on demand. The file stops being the report. The question it answered becomes the report.

In July 2026 we rebuilt all six of the Excel reports a 52-branch jewellery group ran out of Odoo 14: SKU sales, category pivots, category-by-branch sales, a monthly sheet, stock-limit alerts, and production against sales. It took three tranches over two days, 20 and 21 July. The first plan assumed only four of the six were reproducible. The first live version was accurate and too slow to use. Both are worth knowing before you start your own.

What is ERP report automation, and how is it different from ERP automation?

ERP automation covers workflows inside the ERP: approvals, invoice capture, purchase orders. ERP report automation covers what comes out of it. It pulls transaction, stock and production data into a reporting store, computes each report as a query, and serves the result on a screen, in a digest, or as an answer to a typed question.

Most of what ranks for this search comes from ERP and finance software vendors describing benefits. None of it takes a real report apart: which tables it reads, what the export hid, and what breaks when the arithmetic moves from a desktop into a database.

Which six ERP reports did we rebuild, and where does each one's data live?

All six came from three places in Odoo: invoice line items, stock records, and manufacturing orders. Once we mapped each report to its source model, the build became three data feeds rather than six report projects. The mapping below is the order we built them in.

• MASTER (SKU sales by branch): invoice product lines in account.move.line, joined to the product record for SKU, name and category.
• PIVOT (sales, returns and net by category): the same invoice lines, customer invoices and refunds netted against each other.
• Category-by-branch (chains, bangles, necklaces, studs): the SKU-by-branch query, filtered to the product groups the sheet tracked.
• Monthly sheet, sales side: invoice lines rolled up by day and category.
• LIM (stock limit against stock present, per branch and SKU): on-hand quantities from stock.quant compared with the min and max on each reorder rule in stock.warehouse.orderpoint, flagged EXCESS, LOW or OK.
• Production report, plus the monthly sheet's production side: manufacturing orders from mrp.production, counted on completed orders by completion date.

Nothing in the ERP was changed, and the group's own ERP developer was not needed: read access through Odoo's external XML-RPC API covered every model.

Why did the first plan cover only four of the six reports?

Because the existing connector synced invoice headers, not invoice lines. A header tells you a branch sold something worth a certain amount on a certain day. It does not tell you which SKU, how many, or in what category, and four of the six reports are built from exactly that detail.

Before writing any code we checked the gap against the live ERP rather than the documentation. That check changed the scope in both directions. The group had no point-of-sale module; every showroom sale is invoiced with product lines, which meant the line detail existed for all sales. There were 278,900 product lines on customer invoices since 1 April. Stock limits and present quantities were in the orderpoint and quant models, and 18,297 manufacturing orders sat in the production module. All six reports were reproducible, not four.

Scope ERP report automation from the data, not from the export. A report that looks impossible may just read a table nobody has connected yet.

How do you rebuild an ERP sales report from invoice lines?

Sync every product line on customer invoices and refunds, attach SKU and category from the product record, and derive the branch. Then write each report as a database function over that one table, so the MASTER sheet, the pivot and the category-by-branch view are three queries on the same rows rather than three files.

Two details did most of the work. The first was categories. The ERP held 85 live product categories, including silver variants of gold lines. The Excel reports grouped them into eight: bangle, chain, necklace, locket, stud, ring, jimikki and idol. We wrote one normalising function, validated it against all 85 categories, folded silver into its base group, and sent everything else to OTHER. Every report now calls that function, so a new category added in the ERP cannot land in a different group on different screens.

The second was the branch. Invoice lines do not carry a clean branch field, but invoice numbers encode it. We split branch and document type from the invoice number with the same rule the existing finance sales report already used, so the new reports and the old one could not disagree about which showroom made a sale.

Before the full backfill we ran a one-day scoped sync. It returned 2,823 lines, with SKU, name, category, branch, quantity and amount verified against the ERP. Only then did we start the historical load.

How do you automate a stock and reorder report when zero-stock rows disappear?

Stamp every stock sync with one snapshot time and have the report read only the latest snapshot. In Odoo, a stock.quant row disappears when the quantity reaches zero. If you only ever update and insert, a sold-out item keeps its last positive quantity in your copy forever, and the reorder report says you have stock you do not have.

The snapshot pulled 132,135 on-hand rows with a positive quantity and 5,182 reorder rules. Branch came from the location name: the 227 internal locations follow a BRANCH/sub-location pattern, so the prefix is the showroom. The stock-against-limit function compares present quantity with the rule's minimum and maximum and returns EXCESS, LOW or OK per branch and SKU. Rows that vanished in the ERP simply are not in the newest snapshot, so they drop out of the report without a single delete.

When the inventory screen went live on 21 July it replaced an "awaiting stock feed" placeholder with 61 overstocked lines and 267 below reorder, visible without anyone running a report.

What did the live production report show?

Across every product group, the business sold far more units than it produced: most of what the showrooms sell is bought in and traded, not made in-house. The Excel production report already compared sales with production till date by category. The live version runs that comparison for any date window, on demand.

We wrote that function as a full outer join on product group, so a group with sales and no production, or production and no sales, still shows up. An inner join would have quietly dropped exactly the rows that make the point. The production side counts only completed orders, by completion date, and syncs on the same window as the invoice lines so the two sides of the comparison always cover the same days.

Why was the first live version too slow, and what fixed it?

The category reports aggregated around 286,000 invoice lines on every request. They took 4 to 8 seconds, and sales against production took about 16, which ran past the 8 to 9 second statement limit on the database API. The fix that mattered was a nightly rollup table, not a faster query.

We tried the cheap fixes first, in order. A stored category column with an index came first. A 30-second timeout scoped to those report functions stopped the failures without raising the limit for the whole database. Neither made the pages pleasant to open. The real fix was a materialised view of daily sales by branch, product group, document type and day: roughly 20,000 rows instead of 286,000, refreshed each night straight after the 2:30am IST ERP sync, so it costs no freshness. Category reports dropped to between 0.2 and 1.6 seconds.

One report stayed on the base table on purpose. SKU-by-branch sales needs SKU granularity, which a daily category rollup cannot give, so it runs in about 10 seconds under the 30-second cap. We would rather state that trade-off than hide a slower report behind a rollup that answers a different question.

If your reports sit in the same place, this is the work our data and reporting automation service starts with: map every scheduled export to its source tables, then decide which ones need a rollup before anyone builds a screen.

How does a plain-English question get answered from ERP data?

The report functions are granted to a read-only database role that the group's AI agent queries through. When the managing director asks which items in a showroom have fallen below reorder level, the agent calls the stock-against-limit function for that branch and returns the rows, naming the source. Its standing rule is to state no figure that did not come from a query.

This is why we built the reports as functions rather than as dashboard tiles. A tile answers the one question it was drawn for. A function with a branch, a date range and a status filter answers the Excel report, every variation of it, and the version someone thinks of while standing in a showroom. The full build, including connectors to the group's other business systems, is written up in the Parakkat Group command centre case study.

Should ERP reports stay in Excel?

Excel can stay as a place to read numbers. It should not be where they are calculated. Raymond Panko of the University of Hawaii, reviewing field audits of real business spreadsheets, found errors in at least 86% of the spreadsheets examined by the more rigorous recent audits. In a survey he cites, 42% of spreadsheet developers named their chief executive as the highest-level user of the numbers.

His explanation is the part that matters for ERP reports: "spreadsheet modeling is incredibly unforgiving of errors." A wrong reference in a pivot does not look like an error. It looks like a slow month for bangles. (Source: Panko, Spreadsheet Errors: What We Know. What We Think We Can Do, EuSpRIG 2000.)

Our position is narrower than "get rid of spreadsheets". Keep the sheet if people like reading it. Move the category rule, the branch rule and the netting of returns into one place in the database, where every screen, sheet and agent answer uses the same logic. If different teams already disagree on what those numbers mean, settle that first; we wrote about how in our guide to reporting automation across multiple systems.

Frequently asked questions

How long does ERP report automation take?

For us, six Excel reports from one Odoo instance took three tranches over two days of build, because the ERP connector and database already existed from earlier work. Starting from nothing, add the time to build the connector and backfill history. The slow part is rarely the SQL. It is checking each number against the ERP before anyone relies on it.

Do we need our ERP vendor or developer to be involved?

Usually not, provided you can get read access. We read every model for this build through Odoo's standard external API and needed nothing from the group's ERP developer. If your ERP offers no API or read replica, a scheduled structured export works as a fallback, at the cost of freshness.

Can the Excel reports still be emailed out?

Yes. Once each report is a query, producing the same sheet on a schedule is the easy part. The difference is that the sheet becomes a copy of a number calculated elsewhere, so two people forwarding different versions of the file can no longer disagree about the figures in it.

Which ERPs does this work with?

We have built it on Odoo. The approach depends on read access to line-level data, not on the brand: invoice lines, stock on hand, reorder rules and production orders exist in some form in most ERPs. What changes per ERP is the connector and the quirks, such as Odoo dropping stock rows at zero.

If your team still waits on scheduled ERP exports to know what is selling and what needs reordering, talk to us about your reports. Bring the Excel files and we will map each one to the ERP tables it reads.