One-Sentence Summary

Built a batch reporting factory that generated filtered Excel workbooks for 50 management teams by combining a reusable SQL reporting API with template-driven workbook automation.

The Context

The year was 2005, I was supporting a 500-user workflow system that my team and built the prior year. This system replace a LotusNotes solution with a client-server solution with a SQL Server backend.

The system allowed user to extract data, and they did. But what we did not expect is that our 50 managers would each develop their own manual reporting solution that required someone to:

Extract data from the system
Import it into Excel
Clean the data (surrogate keys and the like)
Create pivot tables

It did not take long to figure out that:

They prefer Excel (and PivotTables) to the then state of the art SQL Server Reporting Services solution.
We had 50 teams spending several hours a week pulling data for their internal reporting needs.
Our SQL Server Reporting Services solution could not meet the entire need.
The computers our users had could not handle large datasets.
50 teams extracting their data during the work day puts a stain on the system.
Our users, while they loved Excel were not yet very proficient in filtering and slicing.

The Challenge

Automate the production of Excel workbooks for managers and senior leadership.

Why It Was Hard

The challenge was not just technical. Users strongly preferred Excel to the reporting platform available at the time, their machines could not handle large datasets well, and dozens of teams were repeatedly extracting overlapping data during business hours. The solution had to respect both system limits and human workflow preferences.

The Constraints

We do not overload the system during the day. We need a handsfree solution for nights and weekends.
Due to computer and cognitive constraints, each team needs their own filtered Excel file.

The Approach

This solution split cleanly into a back-end reporting API and a front-end workbook generation engine. The design goal was simple: define each reporting entity once, define the filtering rules once, and then let Excel templates turn the returned data into manager-ready workbooks.

flowchart LR\n subgraph SQL_Server_Backend[SQL Server Backend]\n EV[Consistent Entity Views\n5 initially, later 20+]\n SPA[Stored Procedure API\nEntity + Parameters -> Filtered Query]\n EV --> SPA\n end\n\n subgraph Excel_Automation[Excel Automation Layer]\n MW[Master Workbook\nMetadata + VBA Controller]\n MD[Metadata Rows\nWho / What / Which Template / Parameters]\n TPL[Entity Workbook Templates\nOne per reporting entity]\n GEN[Generated Manager Workbook]\n PC[Pivot Cache Sheet\nRaw returned dataset]\n PT[Pivot Table Sheet\nShared cache]\n UV[Prebuilt Use-Case Sheets\nViews for common reporting needs]\n\n MW --> MD\n MW --> TPL\n TPL --> GEN\n GEN --> PC\n PC --> PT\n PT --> UV\n end\n\n MD -->|Entity + filter parameters| SPA\n SPA -->|Filtered result set| GEN

Components

1. Consistent Entity Views

We created a standard set of SQL views for the entities we wanted to report on. At first there were only a handful, but the pattern scaled well and eventually supported more than 20 reporting entities. The point was not just reuse. The point was that every downstream reporting process could rely on a predictable shape and naming style.

2. Stored Procedure API

Instead of building one query per report, we built a single stored procedure interface that accepted an entity plus filter parameters. Because the entity views were consistent, the procedure could translate the parameters into the needed filter logic and execute the correct query. This gave us one reporting API instead of a growing pile of bespoke extracts.

3. Entity Workbook Templates

Each reporting entity had its own Excel template workbook. The template included a hidden or working sheet for the pivot cache, a pivot table sheet built on that cache, and additional sheets that presented prebuilt views for common management use cases. When a new use case appeared, we usually did not need new code. We updated the template.

4. Master Workbook and Metadata

The master workbook acted as the control center. It stored metadata describing which template to use, what parameters to pass, and which output workbook to generate. The VBA walked row by row through that metadata, duplicated the appropriate template, opened the new workbook, executed the stored procedure, loaded the returned data, and refreshed the pivot structures.

5. Generated Workbooks for Managers

The output was a separate Excel workbook for each team or leader, already filtered and already shaped for the intended audience. This respected both technical and human limits. Users got smaller, relevant workbooks that their machines could handle and that their brains could navigate.

Operational Flow

Weekend batch process starts from the master workbook.
VBA reads one metadata row.
VBA selects the correct entity template and creates a copy.
VBA calls the stored procedure API with the row's parameters.
SQL executes against the selected entity view and returns the filtered dataset.
Excel loads the data into the workbook's pivot cache.
Pivot tables refresh.
Prebuilt reporting sheets are ready for the manager with no manual Excel cleanup required.

This was a pragmatic 2005 solution: push the heavy lifting into a controlled batch window, centralize the query logic, and meet users where they already lived—inside Excel.

My Role

My intern and I split this solution into two pieces. He owned the Excel side while I owned the back end.

Key Decisions

Standardize reporting entities behind consistent SQL views so downstream reporting logic could reuse predictable shapes instead of bespoke extracts.
Build a stored procedure reporting API so filters could be defined once and reused across many workbook outputs.
Use metadata-driven Excel templates so new management views could be added by changing templates instead of writing new code for every report variation.

The Outcome

The result was a scalable reporting factory for its time. Instead of 50 teams repeatedly extracting and reshaping overlapping data during business hours, we generated tailored workbooks in bulk during off-hours. That reduced daytime load on the system, removed hours of repetitive manual work, and gave managers polished Excel files built around the tools they were already comfortable using.

Evidence / Signals of Success

The solution addressed manual reporting needs across roughly 50 teams and managers.
The reporting model scaled from 5 initial entities to more than 20 reporting entities.
Workbooks were generated during off-hours, reducing daytime system load and removing hours of repetitive manual Excel work.

Resume Bullet Seeds

Architected a metadata-driven reporting factory that generated manager-ready Excel workbooks in bulk, replacing manual extract-and-pivot workflows used across roughly 50 teams.
Built a reusable SQL reporting API and template-based Excel automation model that scaled from a handful of entities to 20-plus while reducing daytime load on a 500-user workflow system.

Interview Story Angles

Meeting users where they actually worked instead of forcing a preferred tool.
Separating reusable backend reporting logic from presentation-specific workbook generation.
Designing for system limits, desktop limits, and human cognitive limits at the same time.
Splitting ownership across an intern and backend lead in a way that accelerated delivery.

Lessons Learned

Ad Hoc Requests Are Rarely Isolated
Temporary Solutions Are Not Always Temporary