Reporting Automation

Client: International Salon Supplies

Role: Developer

Period: Aug 2023 – Apr 2026

Status: Production (tools evolved over time)

Tech stack: PostgreSQL, SQL, Excel VBA, WordPress, PHP, WP-Cron, PHPMailer, Next.js, SheetJS, Power BI

A set of reporting tools that took ISS from manual exports to automated outputs: a JSONB-pivoting SQL price list feeding an Excel macro that builds branded order forms with in-cell product images, a shelf-ticket printer, a WordPress plugin that emails scheduled SQL reports, and ultimately Power BI and in-app reporting.

The problem

In 2023, most reports at ISS started with someone exporting data and formatting it by hand: brand price lists for sales reps, promo shelf tickets for the store, weekly numbers for management. The same work was repeated every week.

This post covers the small tools that removed that work, and how they fed into the reporting built into the ISSWA CMS.

1. Brand Price List & Order Form generator

Pipeline: PostgreSQL → Excel VBA → print-ready PDF per brand.

• A SQL query over the product mirror expands the JSONB attributes array and pivots Size and Colour into columns. It takes the category from the last segment of the category path, cleans string 'null' values into real nulls, and truncates prices to two decimals.
• A VBA macro (~690 lines) groups rows by brand → category using nested Scripting.Dictionary objects.
• A small decision tree chooses the right image URL (variation image vs header image, keyed by product ID or a normalised manufacturer SKU) and inserts an =IMAGE() formula so the picture renders inside the cell.
• Each brand gets a branded "Price List & Order Form": logo, contact line, category bands, product code, barcode kept as text, currency formatting, an empty QTY column, and a "prices as of" note.

It was maintained through versions 1.2 → 1.5 over about 13 months, which is a good sign it earned its place.

2. Promo & clearance shelf-ticket printer

• A file picker imports the ERP export. Columns are located by header name, so changes to the export layout don't break the tool.
• Rows are split into Clearance and Promotion. A "No. of prints" column repeats a ticket that many times, laid out four across per page.
• A formatted template cell is copied, and placeholders ({product_title}, {pos_price}, {discount_price}, {percent_value}, {plu}, {promo_end_date} …) are replaced through Characters().Text, which keeps the rich formatting intact.
• A shared optimisation routine switches off recalculation, screen updates and events while it runs, so large batches of tickets generate quickly.

3. WordPress SQL Reporter plugin

A small plugin that lets administrators save a SQL query, a subject and recipients, and receive the result as an emailed CSV, either once or on a schedule.

• Admin-only (manage_options) with WordPress nonces.
• The CSV is built in memory with a 5 MB buffer (php://temp/maxmemory) and sent through SMTP.
• One-off reports treat the entered time as Perth time and convert it to UTC for wp_schedule_single_event.
• Recurring reports each register their own custom WP-Cron interval through the cron_schedules filter, rebuilt from saved settings.
• A "Test Email" button runs a report immediately.

Lesson learned: a tool that runs admin-entered SQL should connect with a read-only database user. That became a design principle in everything I built afterwards.

4. Data-quality queries

Not every report is for people. One query checks the ERP → website sync by finding products the ERP marks as on promotion that have no sale price on the website, catching dropped data before customers do.

5. Where it ended up

• A small Next.js prototype generated Excel files on the server with SheetJS, which was the first step towards reports inside a web app.
• A Power BI model on Retail Express data now covers management dashboards.
• Scheduled, templated reports (Word, Excel, PDF) and an AI reporting assistant now live inside the CMS, with a proper scheduler.

Takeaway

Small, well-aimed automations compound. Each of these tools took days to build and replaced work that was being repeated by hand every week. Together they built the case for investing in a proper reporting platform.