← All use cases
A PDF statement for every member
A fund sends 400 members their monthly statement: a branded PDF each, emailed to each member with only their own statement attached. Nothing may go out before someone has checked it, and the mail server shouldn’t get 400 emails in one second.
What it produces
↓ Download one member’s statement (PDF)
How it’s built
The report’s SQL and YAML, as they sit in the project, and the commands that run it.
queries: [ms_members, ms_balances, ms_contributions]
output:
- name: statement
format: pdf
template: {file: statement.html}
author: Mirage Super
for_each: {query: ms_members, key: member_id, concurrency: 4}
destination: {profile: local, path: "out/mirage_super/{{ run.date.format('%Y%m') }}/{{ each.member_id }}.pdf"}
- name: email
format: message
for_each: {query: ms_members, key: member_id}
title: "Your Mirage Super statement for {{ period('last_month').start.format('%B %Y') }}"
text: "Hello {{ each.first_name }}, your statement for {{ period('last_month').start.format('%B %Y') }} is attached."
destination:
- {profile: mail_local, to: "{{ each.email }}", attach: statement, hold: true, rate: 50/second, burst: 10}-- One row per member: the population, and each member's own values (each.*).
select customer_id as member_id,
full_name,
split_part(full_name, ' ', 1) as first_name,
email,
case customer_id % 3 when 0 then 'Growth' when 1 then 'Balanced' else 'Conservative' end as investment_option
from customers
order by customer_id<h1>{{ each.full_name }}</h1>
<p class="sub">Member {{ each.member_id }} · {{ each.investment_option }} option</p>
...
<table>
<tbody>{% for r in results.ms_balances.rows %}
<tr><td>{{ r.asset_class }}</td><td class="num">{{ (r.balance / total.v) | percent(0) if total.v else '' }}</td><td class="num">{{ r.balance | currency('AUD', 2) }}</td></tr>{% endfor %}
</tbody>
</table>dre run mirage_super # 400 PDFs, 400 emails held
dre run mirage_super --preview # or first: three members, nothing sent
dre release mirage_super --run <run id> --by checker