SSRS & SQL Reporting

The instinct when reporting is painful is to commission new reports. That almost always makes it worse, because it adds to an estate nobody understands. The first thing we do instead is look at the execution log: which reports actually ran in the last twelve months, how often, how long they took, and who ran them.

The result is consistently the same shape. A small number of reports carry nearly all the usage, a large number have not been opened in over a year, and a handful of slow ones are responsible for most of the database load. Once that is on paper the decisions become easy — and the work required is usually a fraction of what was originally scoped.

The most damaging reporting problem is not a slow report, it is three reports that disagree. When each report embeds its own filter logic, small differences in how a date range, a cancellation or a returned quantity is treated produce different numbers for what everyone believes is the same metric. Management stops trusting reporting altogether, and starts keeping a private spreadsheet — which is where the real cost begins.

So business definitions go into shared stored procedures or views that every report consumes, rather than being written inline per report. When the definition of dispatched quantity changes, it changes in one place and every report follows. This is the same single-source-of-truth discipline described on our database pages, applied to the reporting layer.

A report that scans large tables while operations are writing to them produces lock contention, and the symptom appears somewhere else entirely — a supervisor seeing a timeout on a save, blamed on the application. Because the report itself eventually finishes, nobody connects the two.

The fix depends on one question we always ask first: how fresh does this genuinely need to be? Reports that people assume must be real-time are usually perfectly fine at hourly, and that answer changes the solution by an order of magnitude — a scheduled materialised extract instead of a live query against production. Where it truly must be current, a read replica keeps the load off the primary.

There is a strong assumption that every SSRS report should become an interactive dashboard. That is right for some and wrong for others. A trend that someone explores by filtering and drilling is genuinely better as a dashboard. A twelve-page aged stock statement that gets printed, signed and filed is better as a paginated report — dashboards are poor at pixel-exact multi-page print output, and always have been.

So we split the estate deliberately. Exploratory and monitoring reports move to interactive dashboards in Angular or React, computed server-side so every user sees the same authoritative figure. Print-critical and archival reports stay paginated. Both read from the same shared definitions, which is what keeps the dashboard and the printout agreeing with each other.