The bug that reports a smaller number than the truth

A case study — SharePoint KPI Dashboards

Summary

| | |
|---|---|
| **Problem** | A Power BI-shaped reporting need in a tenant where Power BI licensing, data gateways or governance made it the wrong answer. |
| **Constraint** | The data lives in SharePoint lists, which return at most 5,000 rows per response and refuse to filter unindexed columns past a threshold — and neither failure announces itself. |
| **Solution** | Config-driven dashboards where every KPI, threshold, theme and layout is a list row, with paging, caching and design-time health warnings built to make the platform's silent failures loud. |
| **Outcome** | Six web parts, 44,000 lines, 877 tests, a formal adversarial pre-deployment audit — and nothing yet run against a live tenant. |
| **Role** | Architecture, all web parts, calculation engine, data providers, the audit, documentation. |
| **Stack** | SPFx 1.21.1 · React 17 · TypeScript 5.3 · Fluent UI v8 · PnPjs v3 · Node 22 · pptxgenjs / html2canvas / jsPDF |

1. The problem

Every organisation with data in SharePoint eventually wants dashboards over it. The default answer is Power BI, and it is often right. It is wrong when the licensing does not extend to everyone who needs to *look*, when the data never leaves SharePoint and a gateway is disproportionate, or when governance would rather the reporting layer stay inside the same tenant boundary as the data.

The brief was a KPI and dashboard layer that runs entirely inside SharePoint: no external service, no gateway, no per-viewer licence beyond what they already have.

2. The constraint

SharePoint lists are a hostile reporting source, in a specific way: the failures are silent and they change the answer.

The clearest statement of it is in the paging module's own header:

.top(5000) does not mean "give me up to 5000 rows" — SharePoint caps a single response at 5000 regardless… That is a data-accuracy bug wearing a performance disguise: nothing errors, nothing warns, the KPI simply reports a smaller number than the truth.

That sentence is the entire design brief. A dashboard that is occasionally slow is an annoyance. A dashboard that is confidently, quietly wrong is worse than no dashboard, because decisions get made on it.

3. Architecture

A KPI is a row in a list. Its definition — source, calculation type, thresholds, date window, formatting — is data, not code. A calculation service switches over thirteen calculation types; twenty-six visuals render the results; a data-provider abstraction lets a KPI read from a SharePoint list on this site, a list on another site, or Dataverse.

Two caches sit in front of the data layer: a fetch cache so that many KPIs reading the same list, window and columns issue one request rather than N, and an aggregation cache above it.

Everything else — themes, layouts, thresholds — is likewise a list row. The person adding a KPI is an author, not a developer.

4. The interesting engineering

4.1 Making the platform's silence loud

Three mechanisms, all aimed at the same class of failure.

Paging that does not lie. All reads page properly rather than taking the first 5,000 rows. A read-all cap exists but is documented as *"a runaway guard, not a limit anyone should hit"* — and crossing it marks the figure partial rather than returning it as if it were complete. A number that might be wrong is labelled as such.

Design-time health warnings. A metadata service warns the *author*, while they are defining a KPI, that a column they are filtering on is unindexed on a large list. The reason is the important half:

The threshold refusal otherwise surfaces at render time, in front of a viewer, as a failure nobody present can fix.

Moving the warning from render time to design time moves it from the person who cannot act on it to the person who can.

Partial results are labelled, not hidden. The consistent principle across all three: the system's job is not to always be right, it is to never be confidently wrong.

4.2 An audit designed to disprove itself

The pre-deployment audit is the strongest process artefact across the whole portfolio. Six auditors covered disjoint dimensions, and every finding was then handed to an adversarial verifier instructed to refute it by default.

Fifty-nine findings raised. Fifteen refuted. Forty-four kept.

A quarter of the findings did not survive scrutiny — which is precisely why the exercise is credible. An audit that confirms everything it finds is measuring its own enthusiasm.

Two of the fourteen must-fixes are the best bugs in the set:

A cache key that omitted the date field. Two KPIs over the same list, differing *only* in which date column they used, collided on a byte-identical cache key — and the second was served the first one's rows. Both numbers rendered fine. One was answering the other's question.

Locale number parsing. A German user typing 1.234 for one thousand two hundred and thirty-four stored 1.234 — three orders of magnitude out, silently, in the system of record. Not a display bug; a data-corruption bug wearing a formatting disguise.

Two more worth carrying, both time-related and both regional:

• A calendar heatmap keyed by UTC dropped the last day of the month east of UTC — the whole Gulf, always. A bug that is invisible in the developer's timezone and permanent in the customer's.
• Weekly stepping implemented as +7 × DAY lost the 167-hour spring week when daylight saving shifted.

4.3 AI that sends metadata and not data

There is an AI feature here — drafting a KPI definition from a sentence — and its data boundary is drawn explicitly:

Sent: list titles, column names, coarse column types, and choice values. Not sent: list items, KPI values, history, user names.

The model sees the *shape* of the data and never the data. That distinction is what makes the feature deployable in a tenant with a governance position on external AI services, and stating it in the code rather than in a policy document is what makes it checkable.

4.4 Four features built, shipped and switched off

A dedicated document names four capabilities that are built, tested, in the bundle, and disabled: page-hit logging, alerts, AI, and Arabic/RTL.

The reasoning on page-hit logging is a good example of a decision that should be made more often:

A row per page view on every page in the site, growing forever with no retention policy, for two KPIs.

The feature works. The cost — an unbounded list with no retention story — is not worth two KPIs. Shipping it disabled, with the reason recorded, is better than deleting it and better than enabling it.

5. Outcome

Six web parts and an extension, ~44,000 lines of TypeScript, 877 tests across 55 files, thirteen calculation types, twenty-six visuals, eight provisioned lists.

And, stated in its own documentation after the audit fixes: *"Nothing here was run against a live tenant."*

That is the honest summary. The correctness work is real and specific; the audit was rigorous; the evidence that it works end-to-end in a real environment does not exist yet.

Also open: the natural-language "ask your KPIs" feature and a Dataverse sample-data target are both deferred, and the App Catalog description was found by the audit to describe a different product — a web part that does not exist, a script under the wrong filename, and eight calculation types against thirteen. It is recorded as fixed in a later version; that fix has not been independently re-verified.

6. What I'd take from this

The worst failures are the ones that return a number. An error gets investigated. A dashboard reporting 4,800 of 12,000 rows gets *believed*, and decisions get made on it. Most of the engineering here is about converting silent wrongness into visible partialness.

Move warnings to whoever can act on them. A threshold error at render time reaches a viewer who cannot fix it. The same information at design time reaches the author who can. Same fact, different audience, entirely different value.

Build refutation into review. Fifteen of fifty-nine findings did not survive an adversarial pass. Without that pass, all fifty-nine would have been "fixed" — and a quarter of that work would have been changing correct code.

Shipping a feature switched off is a legitimate outcome. Four here, each with a written reason. That is a better record than a deleted branch and a more honest one than a feature quietly left enabled that nobody costed.