Reporting & Analytics
Legal Reporting & Analytics on Elite 3E
SQL Server and SSRS reporting for three law firms against Thomson Reuters Elite 3E data: financial, matter, timekeeper, WIP, accounts-receivable and billing reporting, built from scratch at two firms and rewritten from the ground up at a third during its move to Elite 3E.
Law firms run on time, billing and collections data held in an ERP. Partners, practice groups and accounting staff need accurate, repeatable answers from it: what is unbilled, what is outstanding, how each timekeeper and practice group is performing. Two of these firms had no reporting when I started. The third had a large catalog built on the old Elite Enterprise 3.7 database that could not simply be carried over when the firm moved to Elite 3E.
- 339
- SSRS reports across three law firms
- ~58,000
- lines of SQL behind them
- ~776
- people who use the reports
- 200+
- reports rewritten for the Elite 3E migration
What it solves
- No repeatable reporting for AR, WIP, billing, timekeeper statistics and firm performance, so each answer meant a fresh manual export.
- A reporting catalog tied to the Enterprise 3.7 database, which could not be pointed at 3E, whose schema has more than 10,000 tables compared with a few hundred.
- Ad hoc requests that each needed custom work, with no shared catalog to build on.
- Manual, error-prone monthly invoice uploads to a firm’s largest client after the client changed its required format.
Technology stack
My role
I wrote and maintain the SSRS and SQL reporting at all three firms. At Harper Grey I worked full-time for four years and have continued as a consultant since. RBS and Farris came through Harper Grey referrals, and I built their reporting from scratch. The Enterprise-to-3E application migration at RBS and Farris was done by the software vendor’s professional services; my work there was reporting.
Highlights
At Harper Grey I rewrote more than 200 SSRS reports for the move from Elite Enterprise 3.7 to Elite 3E. The database, schema and application all changed, so the rewrite was complete rather than a conversion. The reports were written during user acceptance testing, over roughly 18 to 24 months.
RBS and Farris had no reporting before. Each catalog took about twelve months and covers AR, WIP, billing, timekeeper statistics, contractor and client billings, client and timekeeper rankings, performance, practice groups and firm KPIs.
Eight Excel dashboards linked to SSRS and more than fifty SQL extract files cover requests that do not justify a full report.
A custom exporter prepares the monthly invoice upload to Harper Grey’s largest client. I rewrote it when the client changed its required upload format, replacing a third-party vendor script.
Key decisions
With a new database, schema and application, I wrote Harper Grey’s reports against the Elite 3E schema rather than translating the old SQL line by line.
Writing reports during user acceptance testing meant the people who relied on them were exercising the numbers before go-live rather than after.
Scale
Across the three firms the catalog is about 339 SSRS reports and roughly 58,000 lines of SQL, serving about 776 people. Harper Grey is the largest: 260 reports and just under 40,000 lines of SQL for about 280 users, with around 60 reports built since go-live from ad hoc requests. RBS has 52 reports (about 12,000 lines, about 220 users) and Farris 27 reports (about 6,400 lines, about 276 users). Together with SQL analyses and Excel workbooks, this is the more than 400 reporting assets I have worked with. These are my own counts and are approximate.
Managing the catalog
A catalog this size needs administration as well as development. I built SSRS Vantage to handle report moves, definition backups, exports and schedule review.
What comes next
A Power BI project and a cloud data warehouse are being planned for one of the firms. Neither has started, and the platform has not been chosen.
Outcomes
- Three firms that either had no reporting or had a catalog broken by a system migration now have maintained, repeatable reporting.
- The rewritten Harper Grey catalog went live with the Elite 3E migration and has grown by about 60 reports since.
- Monthly billing uploads to a major client are produced by a purpose-built exporter instead of a vendor script.