Building an Automated Executive Report for an AmLaw 150 Firm

SQL Server 2016 · SQL Server Reporting Services (SSRS) · SQL Server Analysis Services (SSAS) · MDX

The Problem

A large international AmLaw 150 firm with more than 1,000 employees needed its partners and C-suite to actually see the firm's financial performance each month. Getting busy senior leaders to open multiple reports is difficult, so the finance team wanted one automated report, delivered monthly, covering both firmwide and department-level results.

The scope was large: more than 80 measures, each shown across several time periods (month-to-date, year-to-date, rolling 12 months, and others), all pulled from SSAS cubes. The firm had started building the report themselves, but with only about a quarter of it complete, it was already timing out and slowing down the server whenever it ran.

What I Found

The existing attempt had design problems that would only get worse as the report grew:

• Data pulls were not filtered efficiently, so far more data came back from the cubes than the report needed.
• The entire report ran from a single large dataset, so one oversized query had to finish before anything could render.
• Calculations were written in ways that were expensive to compute over large datasets.
• Heavy use of conditional formatting rules added rendering overhead on every cell.

What I Changed

I rebuilt the report from scratch. Before any building started, I spent the first month working with the firm on requirements: which measures to include, how each should be calculated, and how the report should be laid out for its audience.

• Rewrote the calculations, in both the cube and the report.
• Moved and rewrote calculations as MDX in the SSAS cube, so they are computed where the data lives.
• Simplified the remaining report-level expressions to reduce work at render time.
• Split the single dataset into smaller, targeted datasets.
• Each section of the report runs its own smaller, filtered query.
• Queries return only the data that section needs, cutting load on both servers.
• Redesigned the layout to use fewer formatting rules.
• Kept the same finished look the firm had signed off on.
• Reduced rendering time and made the report easier to maintain.
• Automated monthly delivery to partners and the C-suite.

The Result

The full report, with all 80+ measures and every time period, runs in about 10 minutes without timeouts or slowing the server. It is delivered automatically each month, replaced several separate reports, and gets more senior leaders looking at the numbers that matter.

More than six years later it is still in use with minimal changes, and it carried over cleanly in the firm's recent upgrade from SQL Server 2016 to SQL Server 2019.

Details such as the firm's name, schema, and code are withheld for client confidentiality. The techniques and results described are accurate.