One Dataset Per Report: Why the Builder Will Not Join Sales to Stock
Which customers bought the items we are now out of stock on? One question, two modules, and the report builder will not answer it. Why a report is built on exactly one dataset, what that protects you from, and the thirty-four prebuilt reports that exist because of it.
The first genuinely useful question anybody asks a reporting tool usually spans two modules. Which customers bought the items we have now run out of. Which suppliers we paid late and what that did to our stock position. Which projects went over budget and which staff were on them. Every one of those is an obvious business question, and every one of them requires two different kinds of record to be looked at together — which is precisely the thing a self-service report builder is worst at, here and nearly everywhere else.
The design decision is that a report is built on exactly one dataset. That is a real constraint and it is worth explaining rather than presenting as a feature, because the reason behind it is the more interesting half, and because the workaround is better than the constraint suggests.
What a dataset actually is
There are thirty of them, grouped into eleven categories — inventory, assets, procurement, sales, POS, accounting, workflows, audit, HR, projects and helpdesk. A dataset is not simply a table with the fields exposed. Each one carries a defined column list with labels, a set of measures — the columns it makes sense to sum or average — a set of dimensions to group by, a default date field so a period filter knows which date it means, its own permission, its own list of supported export formats, and its own list of chart types that suit its shape.
That curation is why the builder is usable by somebody who is not a database person. It is also why datasets are versioned, and why a saved report can drift when one changes — a subject in its own right under certification.
One thing that does reach across from elsewhere: your own custom fields. Custom field values are joined in per report, so a field you added to employees, tickets, projects, tasks or expenses is selectable and filterable alongside the built-in columns. Extending a record does not push it outside the reach of reporting, which is the failure people reasonably expect.
Why one dataset, and what the alternative costs
A builder that lets anyone join anything
- Answers cross-module questions directly, which is genuinely what people want
- Requires the person building the report to understand relationship cardinality — the concept, not the word
- A one-to-many join silently duplicates rows on the "one" side
- Which means every total on that side is multiplied, by a factor that varies per row
- The report does not error. It returns a number that is too big and looks plausible
- Two people build the "same" report with different join paths and get different answers
- The person who has to work out why is not the person who built it
A builder that gives you one curated dataset
- Cannot answer a cross-module question in a single report — the real cost, stated plainly
- Every measure it offers is a measure that is safe to sum on that grain
- Row counts mean what they appear to mean
- Two people building the same report get the same total
- Curated labels, so a column means something without a data dictionary
- Per-dataset permissions, so access is grantable by subject area
- Cross-module questions are pushed to prebuilt reports where the joins were written once, carefully
The middle row of the left column is the whole argument. A duplicated-row total is the worst class of reporting bug because it is invisible: the report runs, the chart draws, the figure is roughly the right order of magnitude, and it is wrong by a multiple. We have seen this cost a real decision more than once, and a self-service tool that makes it easy to produce is not doing anybody a favour. Restricting a report to one grain removes an entire category of quiet error at the price of an obvious limitation, and an obvious limitation is much cheaper than a silent one.
So where do cross-module questions go?
To reports where somebody wrote the joins deliberately and tested the arithmetic. There are thirty-four of them, grouped by module, and they are a separate surface from the builder rather than a starting point inside it.
The two reporting surfaces, and what must cross between them
The builder — your questions
One dataset, your columns, your filters, saved and shared by you.
- Thirty datasets across eleven categories
- Your own custom fields, joined in automatically
- Filters, groupings, measures and charts you choose
- Saved, shared, scheduled, certified, exported
- Single grain — one kind of record per report
The prebuilt catalog — the hard questions
Thirty-four reports where the joins and the arithmetic were written once.
- Inventory valuation and stock-as-of any past date
- Stock movement with opening and closing balances
- Dead stock and ageing, turnover and days on hand, ABC banding
- Batch expiry with value at risk
- Transfers with in-transit stock and shrinkage
- Cross-module finance, sales, projects, HR and helpdesk summaries
What you have to carry across the boundary yourself
- The knowledge that the prebuilt report exists at all — this is the real gap, because somebody hunting for stock valuation in the builder will conclude it is not possible.
- The join, when neither surface has your specific question: export two single-dataset reports and match them on a shared key in a spreadsheet. Unglamorous, correct, and about ten minutes.
Check the prebuilt catalog before building anything. A surprising share of the questions people bring to a report builder are already answered there, with more care than a self-service report would apply — the inventory group alone runs to ten reports, including stock as of a past date, which is genuinely difficult to get right and not something you would want to assemble yourself.
Doing the join yourself, properly
When your question fits neither surface, the honest answer is two exports and a lookup. It is worth doing deliberately rather than sheepishly, because there is a right way.
-
Decide which side is the "one" before you export anything
Customers, or invoices? Items, or movements? The side you are counting is the side that must not be duplicated, and naming it first is what stops the total going wrong. This is the same judgement a join builder would have demanded of you, made once and explicitly.
-
Export the "many" side aggregated, not raw
Group and sum inside the report so one row comes out per key. Bringing out raw rows and aggregating in the spreadsheet is where the duplication you were avoiding reappears.
-
Check both row counts against the report screen
The export cap is 5,000 rows and truncation is silent — see exports, limits and formats. A join between two files where one was quietly cut short produces a confidently incomplete answer.
-
Match on a real key, never on a name
Codes, numbers, identifiers. Customer and supplier names differ by a trailing "Ltd", a space or a spelling in about one row in twenty, and every one of those becomes a silent non-match rather than an error.
-
Count the unmatched rows and look at them
The single most useful step, and the one everybody skips. Unmatched rows are either a key problem you have just found or a genuine finding — a customer with no invoices, an item with no movement. Either way you want to know, and a lookup that returns no misses at all usually means you matched on the wrong thing.
-
Save the two report definitions with matching names
Something like "Debtors join — customer side" and "Debtors join — invoice side". Next quarter it is two exports and a paste rather than an hour of reconstruction, and somebody else can do it.
What AWRA OpsHub does today
- Thirty curated datasets across eleven categories, each with labelled columns, defined measures and dimensions, a default date field and its own permission.
- Custom field values joined into reports automatically, so fields you added are selectable and filterable.
- Thirty-four prebuilt module reports covering the cross-module and computationally awkward questions, filtered to what your deployment actually has.
- Per-dataset permissions, so reporting access can be granted by subject area rather than all or nothing.
- Versioned datasets and definitions, so drift between a saved report and its data is detectable.
- Chart types offered per dataset according to the shape of its dimensions and measures.
What it does not do
- No cross-dataset joins in the builder. One report is one dataset, and there is no relationship picker or join step.
- No sub-queries, no report-on-a-report, and no way to use one report's output as another's input.
- No union of two datasets, so "all transactions" across sales and POS is two reports rather than one list.
- No cross-dataset calculated field — a calculation can only reference columns within its own dataset.
- No signposting from the builder to the prebuilt report that already answers your question, which is the gap most likely to waste your time.
- No warning when a question you are attempting genuinely needs two datasets; you simply will not find the columns.
The missing signpost is the one we would fix first, because its cost is invisible. Somebody looks for stock valuation among the inventory dataset columns, does not find it, and concludes the system cannot do it — while a tested Inventory Valuation report with as-of-date support sits one screen away. If you take one habit from this article, make it checking the prebuilt catalog first.
Use the builder
One kind of record, your own filters, possibly your own custom fields
Employees by department and contract type. Tickets by category and age. Invoices over a threshold in a period. Expenses by project with a custom field you added. This is most day-to-day reporting and the builder is the right tool with no caveats.
Use a prebuilt report
Anything involving valuation, ageing, balances over time, or two modules
Stock as of a past date, opening and closing balances, dead stock ageing, turnover and days on hand, ABC banding, batch expiry value at risk, transfers with shrinkage. These involve arithmetic that is easy to get subtly wrong, and they have already been written and tested. Look here first.
Export and join
A genuine cross-module question neither surface covers
Two single-dataset reports, aggregated on the "many" side, matched on a real key in a spreadsheet, with the unmatched rows examined. Save both definitions with paired names so it is repeatable. Ten minutes, and it is correct.
Ask for it
A cross-module question you need every month
A recurring manual join is a report that has not been built yet. Once you are doing the same two exports monthly, the honest answer is a prebuilt report rather than a permanent habit — and the fact that you can describe the join precisely, having done it by hand, is exactly what makes it specifiable.
The limitation you can see is the cheap one
A report builder that refuses your question is annoying, and you find out in thirty seconds. A report builder that accepts your question and answers it wrong is expensive, and you may find out in a board meeting, or not at all. Given that the mechanism for the second — a one-to-many join duplicating rows and multiplying every total on the other side — produces figures that look entirely reasonable, we would rather be annoying. The trade is one obvious constraint against one invisible class of error, and it is not a close call.
Our take
Learn the boundary once and you stop fighting it. One report is one dataset, out of thirty curated ones, with your custom fields joined in — and that covers most day-to-day reporting properly, with totals that mean what they say. Before you conclude something is impossible, check the thirty-four prebuilt module reports, because valuation, stock as of a past date, ageing, turnover and the other computationally awkward questions live there and are easy to miss entirely from inside the builder. When you genuinely need a cross-module answer, export two reports, aggregate the "many" side before you export it, match on a real key rather than a name, and look at the unmatched rows. And when you find yourself doing the same manual join every month, treat that as a specification rather than a routine.
Reports whose totals mean what they say
Thirty curated datasets with defined measures, dimensions and per-dataset permissions, your own custom fields joined in automatically, and thirty-four prebuilt module reports for the valuation, ageing and cross-module questions.
See plans & pricingFrequently asked questions
Can I build a report that combines two modules?
Not in the builder — a report is built on exactly one of the thirty datasets, and there is no join or relationship step. For cross-module questions, check the thirty-four prebuilt module reports first, since many are already covered there. If yours is not, export two single-dataset reports and match them on a shared key in a spreadsheet.
Why not just allow joins?
Because the most common outcome is a silently wrong total. A one-to-many join duplicates rows on the "one" side, so every sum on that side is multiplied by a factor that varies row to row. The report does not error, the chart draws, and the number looks plausible — which makes it the most expensive class of reporting bug there is. One curated grain per report removes that whole category at the cost of a limitation you notice in thirty seconds.
What is actually in a dataset?
More than a table with its fields exposed. Each of the thirty carries a labelled column list, defined measures — the columns it is safe to sum or average — dimensions to group by, a default date field so period filters know which date they mean, its own permission, its own supported export formats and chart types suited to its shape. That curation is what makes the builder usable without database knowledge.
Are my custom fields available in reports?
Yes. Custom field values are joined into reports automatically, so a field you added to employees, tickets, projects, tasks or expenses is selectable and filterable next to the built-in columns. Extending a record does not put it beyond the reach of reporting.
How do I do a two-module join correctly by hand?
Decide which side you are counting before exporting, and aggregate the other side inside the report so one row comes out per key — bringing out raw rows and summing in the spreadsheet reintroduces exactly the duplication you are avoiding. Check both row counts against the screen, because the export cap is 5,000 rows and truncation is silent. Match on a code rather than a name, and then look at the unmatched rows: they are either a key problem or a real finding, and a lookup with no misses at all usually means you matched on the wrong column.
How do I know whether a prebuilt report already covers my question?
Look at the catalog before building anything — this is the habit worth forming, because nothing in the builder points you there. The inventory group alone has ten reports including stock as of a past date, valuation, dead stock ageing, turnover and days on hand, batch expiry with value at risk, and transfers with shrinkage. People regularly conclude those are impossible after failing to find the columns in the builder.
