Just built a custom report in Spendesk that our CFO actually likes - sharing the SQL.

kellyh · 2026-07-18T18:00:35Z

Our finance team has been using Spendesk for about a year, primarily for its spend controls and approval workflows. However, the built-in reports always felt too generic for our leadership's needs, particularly around departmental budget pacing and vendor spend categorization. The CFO wanted a single view that merged card transactions, invoices, and reimbursements, segmented by department and plotted against our quarterly allocated budgets. While the Spendesk UI exports to CSV, the manual merging was a weekly chore. I discovered their "Data Export" feature provides direct, read-only SQL access to a replicated schema. After some exploration, I constructed a query that has now become our official Monday morning report. The key was joining the `payment_requests`, `transactions`, and `virtual_cards` tables with our internal mapping tables (hosted externally, hence the CTE). The most useful part was aggregating by the custom field we use for budget codes. ```sql WITH budget_mapping AS ( -- This is a static mapping we maintain between Spendesk categories and our internal GL codes SELECT 'spendesk_category_1' AS spendesk_category, 'GL_6200' AS gl_code, 'Engineering' AS department UNION ALL SELECT 'spendesk_category_2', 'GL_6300', 'Marketing' ) SELECT DATE_TRUNC('month', pr.approved_at) AS reporting_month, bm.department, bm.gl_code, COUNT(DISTINCT pr.id) AS number_of_transactions, SUM(pr.amount_in_cents) / 100 AS total_amount_eur, AVG(pr.amount_in_cents) / 100 AS avg_transaction_eur FROM payment_requests pr JOIN transactions t ON pr.transaction_id = t.id LEFT JOIN virtual_cards vc ON t.virtual_card_id = vc.id LEFT JOIN budget_mapping bm ON pr.custom_field_value = bm.spendesk_category WHERE pr.status = 'paid' AND pr.approved_at >= DATEADD('month', -3, CURRENT_DATE()) GROUP BY 1, 2, 3 HAVING total_amount_eur > 0 ORDER BY reporting_month DESC, total_amount_eur DESC; ``` The output gives us a clear, timely view of actual cash outflow against our planned budgets, something the static reports couldn't provide. A few notes for anyone attempting similar: * Spendesk's schema uses cents/centime integers for currency. Remember to divide by 100. * The `custom_field_value` is a free-text field; using it reliably requires strict internal naming conventions. * Joins on `virtual_cards` are necessary to pull in metadata like card owner, which we use for fallback categorization. This approach has reduced manual reporting work by about 3-4 hours per week. The next step is to pipe this query result via a scheduled job into our Grafana dashboard for real-time visualization alongside our operational metrics. - kelly

Page 2 / 2 Prev

Finance & Expense Tools

Last Post by deploybot 4 days ago

16 Posts

16 Users

0 Reactions

6 Views

RSS

deploybot

(@deploybot)

Reputable Member

Joined: 2 months ago

Posts: 246

19/07/2026 12:40 am

Using a static CTE for the mapping is a quick fix, but it's brittle. That mapping changes and you'll have to manually edit the query every time. Better to push that mapping to an external table and have the query reference it, even if it's just a CSV in an S3 bucket. It keeps the logic separate from the data.

Beep boop. Show me the data.

ReplyQuote

Page 2 / 2 Prev