ERP Analytics

Define measures and dimensions over your datastores, build reports, and schedule their distribution.

Measures and Dimensions

ERP Analytics never runs hand-written queries. Every figure is a measure (what is counted, from which datastore, how it is added up) and every breakdown is a dimension (how it is grouped). Define each once and every report, dashboard card and scheduled e-mail built on it agrees; correct the definition and they all correct together.

Where to find it

Architect Panel → ERP - Trading & Analytics:

Measures — the figures reports can total
Dimensions — the groupings figures can be broken down by
Analytics — the console, which offers New measure and New grouping to anyone allowed to add them

Admin Panel → Reporting:

ERP Analytics — the console, where measures and groupings are chosen

The starter pack

A starter set of measures and dimensions ships with the platform, shared by every tenant. It covers revenue, cost of sales, gross profit and margin, operating expenses and profit, receivables and payables, collection and payment days, the balance sheet, cash, VAT analysis, purchase spend, inventory value, stock movements by value and quantity, hours logged, billable hours, billable utilisation and unbilled time. Each measure's description states its assumptions. A tenant measure with the same key replaces the shipped one for that tenant, so you can adjust a definition without editing the original.

Defining a measure

Open Measures and add a row (or click New measure on the Explore tab).
Enter a Measure Key (letters, digits and underscores, for example ops_scrap_value) and a Name people will recognise, and a Description saying what it means.
Choose the Source Datastore the rows come from, for example Stock Movements.
Choose the Aggregate: Total (SUM), Number of rows (COUNT), Number of different values (COUNT DISTINCT), Average (AVG), Lowest (MIN), Highest (MAX), or Calculated from other measures (formula).
Enter the Value: a column, or a simple sum over columns such as qty * unit_cost. Columns may follow a linked field with a dot.
Narrow the rows with Filter Column and Filter Value, or with Conditions for more than one condition.
Set the Date Column that places each row in time. Leave it empty for a figure that is only true today, such as stock held now.
Tick Is a Balance for money owed or stock held (shown as at the end of each period), and set Higher is Better so KPI cards colour a rise correctly.
Choose the Format: Money, Number, Whole number, Percentage, Hours (the value is stored in minutes) or Days. Set a Row Limit, and leave Enabled ticked.

A Formula either post-processes an ordinary measure (for example value / 60) or, for a calculated measure, combines other measures by key, such as a margin percentage.

Defining a dimension

Open Dimensions and add a row (or New grouping on Explore).
Enter a Dimension Key and Name.
Choose the Dimension Type: Flat list, Hierarchy, or Date by day, month, quarter or year.
Enter the Key Column of the measure's datastore to group by (write @date to group by each measure's own date).
If the key is a code, name Labels From, its Source Key Column and the Label Column to show. For a hierarchy, name the Parent Column.
Set Applies To to one datastore, or leave it blank to offer the grouping to any measure whose datastore has the key column.

What goes wrong

A column error reads as a sentence, never a database error: the column does not exist, you cannot read it, or it is encrypted.
A measure gives the same figure for every period: it has no Date Column, so it is a figure for today.
Hours look sixty times too big: the value is in minutes but the format is not Hours.
A grouping is not offered: its Applies To names a different datastore, or the measure's datastore lacks the key column.

Worked example

A production manager wants scrap value by item. An architect copies the idea of the starter pack's stock measures: measure ops_scrap_value, Source Datastore Stock Movements, Total (SUM) of the movement value, a condition that the movement type is scrap, Date Column the movement date, Money, Higher is Better off. With the existing Item and Month dimensions it appears on Explore immediately, and the production manager saves "Scrap by item, last 90 days" as a report.

Recommendations

Put the definition in the measure, never in each report.
Name measures for the business and explain them in the description.
Override, do not edit, a shipped measure by saving a tenant measure with the same key.
Add measures on demand; unused ones still confuse the person choosing.

Reports, Schedules and Dashboards

A saved report keeps a question from Explore (its measures, groupings, filters, period and chart) so anyone allowed can run it again with one click. A schedule e-mails a saved report as a CSV or Excel file every day, week or month. Reports saved as KPI cards also form the ERP Analytics Dashboard.

Where to find it

Architect Panel → ERP - Trading & Analytics:

Analytics — the console: Save as report on Explore, and the Saved reports and Schedules tabs
Reports — the saved report definitions
Report Schedules — every schedule with its last and next run

Admin Panel → Reporting:

ERP Analytics — the same console

Saving a report

Build and run the question on Explore, then click Save as report….
Enter a Name and, optionally, a Description.
Choose Show as: Chart and table, Table, Chart, or KPI cards (which adds it to the Dashboard; KPI cards may hold up to twelve measures).
Under Only these security groups can see it, pick the groups, or none for everyone with ERP Reporting.
Tick Always run it for the company it was built for to pin the company; otherwise it runs for whichever company is chosen.
If you can read two or more companies, tick Consolidated to total every company the person running it can read (see ERP Analytics on consolidation).
Click Save report.

Saving needs ERP: Finance, or ERP: Administration together with ERP: Reporting. A report aggregates, so it can show in summary what someone cannot see in detail: set the security groups when you save it.

Running saved reports

On Saved reports, use Find a report to search by name, figure or grouping. Each row shows its period, layout, any schedules and who can see it, with Run, Open in Explore (to change it), Schedule and, for your own, Delete.

Scheduling a report

Click Schedule on a report, or Schedule a report on the Schedules tab.
Choose the Report and Company (The report's own company, a company, or for a consolidated report the whole group).
Choose How often: Every day, Every week (Mondays) or Every month (on the 1st), and the time At.
Choose the File: CSV or Excel (.xlsx), and Send from, the e-mail account it is sent from.
Enter Send to: one address per line (commas and semicolons work too).
Leave Turned on ticked and click Save schedule.

A schedule runs as its owner, the person who saved it, with their current groups, roles and company access, so it only ever contains what they can see. If the owner is deactivated, the schedule stops. Editing and saving a schedule makes you its owner.

Sending and failures

The Schedules tab shows each schedule's company, timing, recipients, sending account, Status (Not run yet, Sent, Failed, Off), last result and Next run, with Send now, Edit, Turn off and Delete. Send now records its result without moving the regular slot. A failure is retried 15 minutes later, the wait doubling each time; five failures in a row switch the schedule off with the reason shown.

Schedules are sent by the scheduled task Scheduled Report Distribution, which runs hourly and ships disabled. Until the hosting administrator or an architect switches it on in the platform's scheduled tasks (see Scheduling), schedules look configured and send nothing.

What goes wrong

"No e-mail account is set up to send from.": an architect adds one first.
Company shows "None chosen" in red: the report has no company and is not consolidated, so it cannot run. Edit the schedule and pick a company.
Status Failed ×5 and Off: read the last result, fix the cause, and turn it back on.
"Runs as nobody - re-save it": the schedule has no owner; edit and save it.

Worked example

A finance controller explores Revenue and Gross margin % by Month for the financial year, saves it as "Monthly margin" shown as Chart and table, visible only to the Finance group. They schedule it every month on the 1st at 07:00 as Excel to four named people, sent from accounts@. On 1 November the task sends it; one address bounces, the schedule shows Failed and retries, and they correct the address with Edit.

Recommendations

Set security groups when you save, not when somebody notices.
Send weekly unless someone acts daily.
Send to named people and review recipients when roles change.
Switch on the distribution task or nothing is sent.

The ERP Analytics Console

ERP Analytics gives each company its key figures: a KPI dashboard, an Explore tab for any measure broken down by period, account, customer, supplier, cost centre, project or item, the rows behind any number, and saved and scheduled reports. Every figure is for one company you can read; a total across companies is only ever a saved report marked Consolidated.

Where to find it

Architect Panel → ERP - Trading & Analytics:

Analytics — the console: Dashboard, Explore, Saved reports and Schedules
Measures — the figures
Dimensions — the groupings
Reports — saved reports
Report Schedules — scheduled e-mails

Admin Panel → Reporting:

ERP Analytics — the same console for finance and reporting staff who are not architects

Who can use it

ERP: Reporting: read-only. Run, drill and export.
ERP: Finance: also save reports and manage schedules. Someone holding ERP: Reporting and ERP: Administration can save and schedule too; ERP: Administration alone does not open the screen.

Which companies anyone sees is their company access; a role never implies a company. Holding ERP: Reporting or ERP: Finance is enough to read the datastores the starter pack's measures use, so no separate datastore grant is needed. Grant the roles and company access on ERP Setup (see Getting Started with ERP).

Choosing a company and period

At the top, choose the Company (the top of the group is listed first and elimination companies last; the screen remembers your last choice) and the Period: All dates, This month to date, Last month, This quarter to date, Last quarter, This calendar year to date, Last calendar year, This financial year to date (the default), Last financial year, Last 30 days, Last 90 days, Last 12 months, or Between dates… with From and To. Financial-year periods use the company's own year end.

The Dashboard

The Dashboard tab shows every saved report whose layout is KPI cards, such as the starter pack's Finance KPIs and Operations KPIs. Each card gives the figure for the period, the change against the comparable earlier period (green or red depending on whether higher is better), and a twelve-month trend. A figure that is a state today, such as inventory value from the cost layers, says Today's figure and shows no comparison or trend.

Exploring a question

Go to Explore.
Tick up to six Measures; use Find a measure… to search.
Choose Group by and, optionally, Then by. Only groupings that fit every chosen measure are listed.
Choose the Chart: Automatic, Bar, Stacked bar, Horizontal bar, Line, Area, Pie, Doughnut or Table only.
Click Add a filter to narrow the rows, then Run.
Select any figure in the table to see the rows behind it; Close hides them.
Click Export CSV to download, or Save as report… to keep the question.

The headline total is worked out again without the grouping, so an average or a balance is the true overall figure. A result that hits the row limit says so. If a figure or grouping is missing and you may add to those datastores, New measure and New grouping appear under Missing a figure or a grouping?

ERP figures on BI dashboards

Holders of ERP: Reporting or ERP: Finance can also put ERP figures on a platform BI dashboard (Admin Panel → Reporting → Dashboards). Open a dashboard and click Add an ERP figure, then give it a Title, choose the Company (or the group, all companies in the group currency), up to six Figures, Group by and Then by, the Period and Show as, and click Add to dashboard. Everyone who opens the dashboard sees the figures for the companies they may read. See BI Dashboards.

What goes wrong

"No enabled measures are defined": load the starter pack or define measures.
Every KPI reads 0.00: you are looking at an elimination company; pick a trading company.
A grouping is missing from the list: it does not fit one of the chosen measures; remove that measure.
"Give both a From and a To date for a custom period."
"ERP figures need the ERP: Reporting or ERP: Finance role." on a BI dashboard.

Worked example

An operations manager with ERP: Reporting opens ERP Analytics for the UK company, financial year to date. The Operations KPIs show inventory value today, billable utilisation, unbilled time, purchase spend and hours logged. Utilisation is down, so they click through to Explore, tick Hours logged, Billable hours and Billable utilisation %, group by Month then Resource, and run it. One consultant's billable hours dropped in August; they select the figure to see the time entries behind it, and export the table for their team meeting.

Recommendations

Start from the Dashboard and drill into Explore only for the question in front of you.
Keep to two groupings; save several focused reports rather than one large one.
Check the company and period before reading any figure.
Use the rows behind a figure to settle any disagreement about a number.

Consolidated Figures Across Companies

ERP Analytics reports one company at a time unless you ask for a group total. A group total is a saved report marked Consolidated, a BI dashboard figure set to the group, or a role centre figure for all your companies. Each adds together only the companies the person looking can read, translated into one group currency, and says plainly what happened to trading between those companies.

Where to find it

Architect Panel → ERP - Trading & Analytics:

Analytics — the console: the Consolidated tick when saving a report, and the Schedules tab

Architect Panel → ERP - Finance:

Exchange Rates — the rates group figures are translated at

Admin Panel → Reporting:

ERP Analytics — the same console
Dashboards — BI dashboards, where an ERP figure can be set to the group

Who can consolidate

Saving a consolidated report needs ERP: Finance (or ERP: Administration together with ERP: Reporting) and read access to at least two companies. Running one needs read access to two or more companies at the time. Someone who can read fewer companies sees fewer: a group total never includes a company the viewer cannot read.

Which currency

If every company keeps its books in the same currency, that is the group currency. Otherwise the companies' common reporting currency is used, set on each company in ERP Setup → Companies. If they name different reporting currencies, the report is refused with the reason. Then:

Flows (revenue, costs, cash in and out) are translated month by month at that month's average rate, falling back to the latest spot or closing rate on or before the month end.
Balances are translated at the closing rate on the last day of the period.
Only money is translated. Counts, hours and quantities are simply added. An average or minimum of amounts in two currencies is refused.
A missing rate refuses the figure, naming the currency and month; it is never treated as 1.

Money columns in exports are headed with their currency, for example "Revenue (GBP)".

Saving a consolidated report

Build the question on Explore and click Save as report….
Tick Consolidated - total every company the person running it can read. The hint under it names the group currency, or says why consolidation is not possible.
Leave Always run it for the company it was built for unticked, and click Save report.
To schedule it, choose The whole group (consolidated) as the company. The Schedules tab shows it as Whole group (consolidated), every company its owner can read, in the group currency.

Intercompany trading

Every group figure carries a sentence about intercompany trading, read from the ledger for that period:

With no elimination company in the group: Intercompany trading is not eliminated, naming the companies that traded with each other.
With an elimination company but nothing posted in it: a warning, No intercompany eliminations have been posted for this period - trading between the companies is included.
With eliminations posted: which company and how many journals, and, where members still booked sales and purchases with each other, that those are still in income and expenses.

Posting the eliminations themselves is a ledger task (see Consolidation and Intercompany Eliminations).

What goes wrong

"These companies keep their books in … and do not name ONE group reporting currency": set the same reporting currency on each company, or report on each separately.
"'…' cannot be translated into …: there is no exchange rate from …": load the average, spot or closing rate, then run again.
The Consolidated tick is disabled: you cannot read two companies, or the companies have no common currency; the hint says which.
A group total looks high: read the intercompany sentence; trading between members may not be eliminated.

Worked example

A group has a UK parent (GBP), a US subsidiary (USD) and an elimination company, all with reporting currency GBP. The CFO saves "Group P&L by month" as Consolidated. September's US revenue is translated at September's average USD rate, while the balance sheet report uses the closing rate on 30 September. The note under the heading warns that no eliminations have been posted for September, so the controller posts them, and the October run says how many elimination journals are included.

Recommendations

Set a reporting currency on every company before building group reports.
Load average and closing rates each month.
Post eliminations before reading group profit.
Schedule group reports from someone who can read every company, since a schedule runs with its owner's access.