Monthly finance pack

Finance wants the same workbook every month: one tab per cut of the data, money formatted properly, and it has to land in their inbox without anyone remembering to send it. You write the SQL once and describe the workbook in YAML.

Recorded from a real run: the command, the workbook in Excel and the email it sent.

The By region sheet of the finance pack in Excel, with three tabs — The workbook DRE wrote, opened in Excel (real screenshot, light mode). Column widths were auto-fitted in Excel: DRE doesn’t set widths yet.

The finance pack email in the Mailpit test inbox, with the workbook attached — The email in Mailpit, the demo’s test inbox. The addresses are fictional.

↓ Download the workbook (finance_pack.xlsx)

reports/finance_pack/finance_pack.yml

queries:
  - {query: finance_regions, tab_name: By region}
  - {query: finance_channels, tab_name: By channel}
  - {query: finance_products, tab_name: Top products}
output:
  format: xlsx
  columns:
    net: {format: "#,##0.00"}
  destination:
    - {profile: local, path: "out/finance_pack.xlsx"}
    - profile: mail_local
      to: finance@dre-demo.test
      cc: cfo@dre-demo.test
      subject: "Sales {{ period(var('period')).start.date }} to {{ period(var('period')).end.date }}"
      attachment_name: "Sales pack {{ run.date.yyyymmdd }}.xlsx"

reports/finance_pack/finance_regions.sql

{% set p = period(var('period')) %}
select
  region_name,
  count(distinct order_id) as orders,
  sum(qty) as units,
  sum(net_amount) as net
from {{ ref('paid_order_facts') }}
where order_date between '{{ p.start.date }}' and '{{ p.end.date }}'
group by 1 order by 1

terminal

dre run finance_pack