SQL Reporting Dashboard

Automated weekly and monthly management reports from a PostgreSQL database — what used to take 2-3 hours of manual querying and Excel formatting now runs on demand in seconds, from one command.

A small operations team produces weekly and monthly reports for management from a relational database: joining several tables, applying business-specific aggregation logic, and formatting the output into a specific Excel layout. Done by hand, each report takes a couple of hours and depends on one person who understands the query logic — reports slip, and formatting drifts between runs.

This project is a fictionalized, from-scratch rebuild of that pattern for a "retail operations" scenario: no real company's data, schema, or branding is used anywhere here — see Data below.

flowchart LR
    A[("PostgreSQL 16\n(docker-compose)")] -->|"parameterized\nSQL functions"| B["reporting_dashboard.generator"]
    B --> C["reporting_dashboard.excel_writer\n+ .xlsx template"]
    B --> D["reporting_dashboard.pdf_writer\n(KPI + chart summary)"]
    B --> E["reporting_dashboard.run_log\n(run_log.csv)"]
    F["reporting_dashboard.cli"] --> B

• Database layer: a base view (vw_order_details) joins seven tables; parameterized SQL functions (fn_weekly_sales_report, fn_monthly_department_report) sit on top of it and accept date ranges and optional filters. See docs/erd.md for the schema.
• Python layer: reporting_dashboard/generator.py calls the right function with the requested parameters, reads the result into a pandas DataFrame, times the query, and hands it to excel_writer.py (and optionally pdf_writer.py). excel_writer.py only ever writes values into a pre-built .xlsx template — header styling, column widths, and conditional formatting live in the template file, not in code.
• Run log: every run appends its timing and row count to logs/run_log.csv and prints a summary — an easy way to notice if a report looks different from last time before it goes out.

git clone https://github.com/wshopcode/sql-reporting-dashboard.git
cd sql-reporting-dashboard
docker-compose up -d          # Postgres 16, auto-loads schema + seed data + views + functions
pip install -r requirements.txt
cp .env.example .env           # defaults already point at the docker-compose db

python -m reporting_dashboard.cli generate weekly-sales --start 2025-01-01 --end 2025-01-07
python -m reporting_dashboard.cli generate monthly-department --month 2025-01

Open the generated file in output/. That's the whole loop — no manual setup beyond docker-compose up -d.

# Weekly sales by store/region, optionally filtered to one region
python -m reporting_dashboard.cli generate weekly-sales --start 2025-01-01 --end 2025-01-07 --region West

# Monthly performance by department, with an Excel + PDF summary
python -m reporting_dashboard.cli generate monthly-department --month 2025-01 --formats xlsx,pdf

# See recent runs — timing and row counts, so a surprising change is visible immediately
python -m reporting_dashboard.cli list-runs

• Parameterized SQL functions for date ranges and optional region/department filters — the aggregation logic lives in Postgres, is versioned with the repo, and can be tested and changed independently of the Python code.
• Template-driven Excel output — layout, branding, and conditional formatting are controlled entirely by the .xlsx files in templates/, not generated from code.
• Two output formats per report — a detailed Excel file and a one-page PDF KPI/chart summary, generated from the same query result.
• Run log (logs/run_log.csv + list-runs) — query time and row count for every run, so an unexpectedly different report is visible before it goes to anyone.
• "Data as of" footer on every report, sourced from fn_data_freshness(), so the reader always knows exactly how current the numbers are.
• Configurable output directory and filenames, for scripted/archiving use.

Reports are meant to run unattended. The simplest setup is an OS-level scheduler calling the CLI directly:

# cron, every Monday at 6am
0 6 * * 1 cd /path/to/project && python -m reporting_dashboard.cli generate weekly-sales --start "$(date -d 'last monday -7 days' +%F)" --end "$(date -d 'last sunday' +%F)"

(Windows Task Scheduler works the same way — point it at the same command.) scripts/run_scheduled.py is an alternative if you'd rather manage scheduling from inside a long-lived Python process (uses APScheduler).

docker-compose up -d
pip install -r requirements.txt
pytest -v

tests/test_functions.py asserts exact row counts and aggregates from the functions against the fixed-seed dataset (deterministic, not a smoke test). tests/test_excel_writer.py and tests/test_run_log.py are pure unit tests. GitHub Actions (.github/workflows/ci.yml) runs the same suite against a postgres:16 service container on every push.

Weekly Sales Performance Report — Excel output (conditional formatting on Margin %) and the one-page PDF summary:

Monthly Department Performance Report — same pattern, different template:

Everything in db/init/ is synthetic: a fixed-seed (Faker, seed 42), deterministic dataset generated by scripts/generate_seed_data.py — 8 departments, 48 employees, 14 stores, 500 customers, 180 products, and ~18 months of orders (2024-01 through 2025-06). No real company, employee, or customer data appears anywhere in this repository.

db/init/                  schema, seed data, views, functions (loaded by docker-compose)
reporting_dashboard/      Python package: cli, generator, db, excel_writer, pdf_writer, run_log
templates/                the two .xlsx templates the Python code writes into
scripts/                  seed data generator, template builder, optional scheduler example
tests/                    pytest suite (SQL function tests + unit tests)
docs/                     ERD and screenshots

Complex aggregation logic. Some reports needed multi-level aggregation that was awkward as a single query. Splitting the work into a base view (vw_order_details, the joins) and functions built on top of it (the aggregation) kept each layer readable and independently testable — the functions in 04_functions.sql never repeat a join.

Template-driven Excel generation. Keeping layout in .xlsx template files rather than generating it from openpyxl code makes the report look adjustable without touching Python — header text, column order, and conditional formatting rules all live in the template. The trade-off: a template's column order and the DataFrame's column order have to stay in sync, since excel_writer.py writes positionally. In practice, template changes are rare enough that this hasn't been a real cost.

Data freshness. A report is only useful if the reader knows how current it is. fn_data_freshness() returns the MAX(updated_at) across every source table; the generator surfaces the most recent of those as a "Data as of" footer on every output file, so staleness is explicit rather than assumed.

MIT — see LICENSE.