Magrid by LetzMath Sarl
What was actually going on
The reporting layer was built on SQL views, and the views had IDs written directly into them. Not looked up from the tables. Typed in.
So the logic deciding what belonged in a report was not reading the data. It was reading a list somebody had maintained by hand, in some cases years earlier.
These were not internal dashboards. They went to customers.
Every time something new was added to the system, someone had to open the views and add its ID by hand. Not once. Eight to twelve times inside each report, because the same list had been copied into filters, joins and case statements as the reporting grew. Four main reports, another five to seven behind them. Somewhere between seventy and a hundred and thirty individual places where a list had to be kept in sync manually, and no way to tell whether it had been.
A couple of hours of careful work every time something new was added. Careful, and still not reliable, because being careful is not a control. Miss one and nothing breaks. The new item just does not appear in that report. Or it appears in one report and not another, which is worse, because now two reports disagree and neither is obviously the broken one.
This had been going on for more than two years. Six to eight columns across those reports were carrying wrong values by the time anyone looked. Nobody had done anything wrong. The system was built to require a manual step that a person will eventually forget, and over two years people forgot.
Magrid by LetzMath Sarl
What I did
I went through every view in the reporting layer, pulled out the hardcoded IDs, and rewrote the queries to read from the tables the way they should have been doing from the start. Where the logic genuinely needed a grouping or a rule, that moved into the data rather than staying buried in SQL.
Then I corrected the historical figures, so their reporting was not showing one set of numbers before the fix and a different set after it.
Magrid by LetzMath Sarl
What changed
Adding something new is now just adding it. It appears in the reports because the reports look at the data. There is no list to maintain, no second place to remember, no way to half-do it, and no couple of hours of manual editing attached to every addition.
The six to eight columns that had been wrong are right, and they stay right without anybody maintaining them.
We are now working on the larger piece, which is rebuilding the report centre properly. Getting rid of the view layer and the duplication that grew inside it, and giving them something where the data comes out cleanly instead of being reassembled every time. That is in progress and I will write it up when it is done.
Magrid by LetzMath Sarl
Why this happens
Hardcoded IDs in a view are almost never a bad decision. They are usually a reasonable shortcut taken early, when there were four of something and a list in a query was quicker than building the lookup properly. The problem is that the shortcut has no expiry date. The system grows, new views get written by copying the ones that already exist, and the list spreads into places nobody remembers. Nothing tells you it has gone wrong, because a query filtering on a stale list of IDs is a perfectly valid query. It returns a correct answer to the wrong question. Monitoring will not catch it. Tests will not catch it, unless somebody wrote a test that knew what the right answer was supposed to be.
If your reporting is built on views and you have never gone looking, it is worth an hour of somebody's time to search your view definitions for literal IDs. If you find them, the question is not whether the list is right today. It is what happens the next time somebody adds something and does not know a view was supposed to be updated too.