Automated Client Reporting
From Inbox to Inbox – Pipeline & Admin Web ApplicationAn end-to-end reporting pipeline that replaced a manual, multi-hour monthly process with a fully automated workflow – built lean, without enterprise licensing costs. Extended with a bespoke admin web application for client management, data export, and report control.
The Challenge
My Client provides logistics services to a broad client base across Sameday, UK Overnight, and International freight. Each month, the business needed to deliver individual branded performance reports to every client – covering booking volumes, spend breakdowns, sustainability metrics, and year-on-year comparisons.
The existing process was entirely manual. Someone had to pull booking data, validate it, identify anomalies, format figures, and build out a PowerPoint presentation for each client. It worked, but it was time-consuming and impossible to scale as the client base grew.
“The goal was simple: remove the human from the routine and leave them only the things that require human judgement.”
An additional constraint shaped the entire design – the solution had to be built without significant licensing overhead. The most obvious tooling for this kind of output sits behind premium capacity licence tiers that weren’t justifiable. That meant finding a smarter path.
The Solution
A five-stage automated pipeline was designed and built across the Microsoft Azure ecosystem – connecting a shared inbox all the way through to a finished, branded report sent to an inbox of choice. A bespoke user-friendly admin web application was then built on top of the pipeline to give the operations team full control without touching code or Azure directly.
Stage 1 – Data Ingestion
A Power Automate flow monitors a shared inbox and automatically picks up booking data file attachments as they arrive each month. Files are validated and saved to SharePoint, with the activity logged throughout.
Stage 2 – Validation & Anomaly Detection
An Azure Data Factory pipeline processes the data through a set of business and data quality rules – flagging missing information, data inconsistencies, and pricing issues. Anything unusual is routed to a review queue rather than silently passing through.
Stage 3 – Operator Review & Approval
Flagged items surface in a SharePoint-based exception queue where an operator can review and make a decision. Once all anomalies are resolved, clean data is automatically promoted to the production database.
Stage 4 – Report Generation & Distribution
A final flow loops through the client list, calls an Azure Function to generate a fully branded multi-slide PowerPoint report for each client, saves it to SharePoint, and emails it directly to the configured recipients – all without human involvement.
Stage 5 – Admin Web Application
A Python Flask web application hosted on Azure App Service gives the operations team a clean browser-based interface to manage every aspect of the pipeline without touching code, Azure, or SQL directly. Four functional areas: triggering reports on demand, registering new clients, managing multi-site client groupings, and exporting raw booking data as CSV.
Key Decisions & Problem Solving
Several deliberate decisions shaped how this was built – particularly around cost, flexibility, and maintainability.
Bypassing Licensing Barriers
Power BI paginated reports require a Premium Capacity licence. A custom Python solution was built instead, producing identical output at zero additional cost. The same principle was applied to the admin interface – a lightweight Flask web app replaced a Power Apps canvas app that was abandoned mid-build due to persistent SQL connector reliability issues.
Data Quality at the Gate
Every file passes through automated validation rules before it can reach a report. The admin app reinforces this with a pre-flight data check – confirming bookings exist in the production database for the selected client and period before generation is allowed to start.
Multi-Client Architecture
A client grouping model in Azure SQL allows multiple site codes to be combined into a single report with no changes in the automation layer. The admin app exposes this through a dedicated management screen – no SQL knowledge required.
Built for Handover
The pipeline is designed to migrate cleanly into an Azure tenant – connection strings and environment variables are the only changes needed.
Self-Service Data Export
The admin app provides a configurable CSV export directly from the browser – filterable by client, date range and service category. Files stream directly from SQL with no intermediate storage required.
Client Branding at Scale
Each report is generated using the client’s own logo, retrieved from Azure Blob Storage at run time. Logos can be uploaded directly through the admin app and are picked up automatically by the next report generation cycle.
Distribution Management
Account Management and Client email addresses are managed through the admin app alongside all other client configuration. Multiple addresses per client are supported.
Skills & Technologies
The Admin Web Application
The Flask admin app sits on top of the pipeline as a thin but fully functional control layer. It was built after a Power Apps canvas app was abandoned mid-build due to persistent SQL connector reliability issues – a decision that ultimately produced a better result. The web app is faster, fully version-controlled in GitHub, deployable via CI/CD, and has no licensing dependencies.
▶ Run Report
Operators select a client and reporting period, run a pre-flight data check to confirm bookings exist in production, then trigger generation with a single click. The finished PowerPoint is streamed directly to the browser defualt downloads folder – no SharePoint navigation required.
＋ Add Client
New clients are registered through a structured form covering client code, display name, reporting frequency, quarter start month, distribution email addresses, and an optional logo upload. The client code is validated against the database and logos are written to Blob Storage automatically.
⚙ Manage Groups
Clients with multiple booking codes – different sites or departments – are managed through a dedicated grouping screen. Operators can see all linked codes, add new members, and remove secondary codes. The primary group code is protected from deletion.
⬇ Export Data
A configurable data export lets operators download raw booking data for any client and date range as CSV. Filters for service category and credit note inclusion match the report logic exactly – ensuring export figures and report figures always agree.
The Outcome
A monthly process that previously demanded hours of manual effort now runs entirely on its own. Reports are consistent, accurate, and delivered on time – every month – without anyone building a spreadsheet or assembling a slide deck by hand.
The operations team can manage every aspect of the pipeline – clients, groups, logos, email addresses, and on-demand reports – through a clean web interface without touching Azure, SQL, or code. The pipeline is documented, modular, and ready to scale – and it was built without adding high cost premium Microsoft licences to the bill.
