Automated reporting: your company’s figures, gathered in one place for you

In many companies, the Monday morning report gets made on Friday afternoon: someone exports from the stock management software, the sales software and the production records, pastes everything into an Excel file and corrects by hand whatever does not tie up. By the time it reaches you, the figures are already old, and the answer to “why has the margin fallen?” takes another week to arrive. An automated reporting project removes the exporting and pasting and leaves time for the question itself.

The hard part is not the charts. It is getting different programs, bought at different times, to speak in the same way about the same product, the same buyer and the same day.

Three programs, three answers to the same question

You ask how much was sold last month. Sales give you the value of orders, accounts the value invoiced, and the warehouse what went out through the gate. All three are correct and different, because each program counts something else: with or without VAT, with or without returns, at the order date or the delivery date.

The same happens with stock (at what time of day?) and with margin (against which cost: the last purchase price or the average cost?). Meetings end up being about whose figure is right, not about what needs doing.

Then there are the codes. The same item has one code in the stock management software and another in production, and the same buyer appears three times, spelt slightly differently.

Shared definitions of the figures

The first thing handed over is not a dashboard but a dictionary: for each indicator, one sentence saying what it includes, what it excludes, which program it comes from and which date it is reported against. We write it with the people who use the figure, and management approves it: that is what makes people trust the report.

The dictionary is then turned into calculations done once, in the data warehouse. If the definition of margin changes, you change it in one place and every report follows.

This is also where we settle who sees what. A branch manager sees their own figures, not the salaries or margins of the whole company.

What automated reporting involves, from extraction to alert

The architecture is the same whether you have two programs or eight: data is extracted from the sources, cleaned and combined in a data warehouse, and the reports read only from the warehouse. The programs you work in are not modified, because we access them read-only, and we schedule large extractions outside peak hours so as not to slow them down.

Take a manufacturing company with two warehouses and a field sales team. The usual components would be:

• Connectors: one for each program, through an API (the interface the program provides for exchanging data), by reading directly from the database or through automatically exported files.
• Incremental loading: each run brings in only new or changed records.
• Mapping tables: item codes, buyers, branches, units of measure.
• Quality checks: totals compared with the source, missing values, duplicates; the report is not published if a check fails.
• Dashboards: a few screens per role, from which you can drill down from the total to the individual document.

Scheduled refreshes and alerts on deviations

How often the data is refreshed is a business decision. For most management reports a refresh every night is enough; for stock or shift-based production one every hour may be needed. Real time costs far more and rarely changes a decision.

A good alert has a threshold, a recipient and an action. “Stock of the item has dropped below the minimum level” goes to purchasing. “Scrap on a line is above the usual average” goes to the production manager. We start with a small number of alerts, chosen by you, because nobody reads ten messages a day.

We also alert on the system itself: if a source has not responded, the report says so visibly, next to the title.

The order of work and the limits of a data warehouse

We usually use PostgreSQL as the data warehouse (a relational database with no licence costs), Python scripts run on a schedule for extraction and a visualisation tool such as Metabase or Power BI, chosen according to the licences you already have.

The limits are stated from the start. An old program with no API and no access to its database can only be read through exports, which means more work. And the data warehouse does not repair data that was entered wrongly: if goods receipts are recorded late, the stock report will be just as late.

• The initial conversation: what decisions you take on the basis of the figures and what questions go unanswered today.
• The first indicator: just one, taken from source to screen, to test the whole chain.
• The expansion: indicator by indicator, each compared with the manual report until the figures agree.
• The handover: the dictionary, the data warehouse schema and training for the person who will maintain the reports.

This is a typical project description: it shows how we usually approach this kind of work and does not present a project carried out for a particular client. Every real project starts from your company’s situation, and the stages, timescales and price are agreed after the initial discussion.