How to Automate Reporting and Dashboards With AI

Why LLMs Must Not Compute the Numbers

This is the single most important rule. LLMs hallucinate arithmetic. Not sometimes: reliably, under load, in edge cases you will not anticipate. A model that confidently writes 'revenue grew 18.3% month-over-month' when the real figure is 12.1% is worse than no automation at all, because a human will trust it.

The architecture that holds in production is:

• Deterministic layer: SQL queries, dbt models, pandas transforms, or any code path that produces a JSON payload of named metrics. This layer is version-controlled, tested, and produces the same output given the same input, always.
• LLM layer: receives the JSON payload as context and is prompted to write the narrative, compare current values to prior periods (already computed), identify outliers, and recommend actions.
• Audit layer: the raw payload is stored alongside every generated report so you can reproduce any past narrative by re-running the prompt against the archived data.

What teams get wrong: they call the LLM first, let it 'figure out' what queries to run via tool calls, then trust the numbers it returns. Tool-calling to a database is fine for exploration. It is not fine for a CFO report that will be read once and acted on.

The Production Architecture: Five Layers

Here is the blueprint I use for clients who need automated weekly or monthly reports delivered to Slack, email, or a dashboard endpoint.

1. Scheduled Data Pipeline

A cron job or orchestrator (Airflow, Prefect, GitHub Actions scheduled workflow) triggers at the cadence the report requires. It runs your queries against the production replica or warehouse (Snowflake, BigQuery, Redshift, Postgres). Output is a structured JSON document with explicit field names: {'period': '2026-05', 'revenue_usd': 482300, 'prev_revenue_usd': 441200, 'new_customers': 214, ...}. No prose, no interpretation, just numbers.

2. Anomaly Pre-Pass (Deterministic)

Before the LLM sees anything, a rule-based or statistical check flags values outside expected ranges. Z-score on a rolling 90-day window works well. Mark each metric with a tag: normal, watch, or alert. Pass those tags into the LLM context. This focuses the model's attention and makes sure it does not miss a genuine spike by rambling about unimportant trends.

3. LLM Narrative Generation

The prompt receives the structured payload, the anomaly tags, the prior-period deltas (computed deterministically), and a system instruction that constrains the model to cite only figures from the payload. A hard rule in the prompt: 'Do not compute or infer any number not explicitly provided. If a figure is tagged ALERT, lead with it.' Temperature 0. Model: a fast, cheap one like Claude Haiku or GPT-4o-mini for routine reports; a stronger model only when the prompt includes complex multi-metric reasoning.

4. Human Review Gate (Configurable)

For executive-facing or customer-facing reports, route the draft through a lightweight approval UI before delivery. For internal Slack digests, skip it. The gate costs you 15 minutes and saves you the career-limiting moment when a model confidently misread a timezone offset and told the board Q4 was down 40%.

5. Delivery and Storage

Deliver via the channel the audience already uses: Slack webhook, SendGrid email, PDF render via Puppeteer or WeasyPrint, or a write-back to a Notion/Confluence page. Store every generated report with its input payload and the exact prompt version used. This is your audit trail.

Worked Example: Weekly SaaS Metrics Report

Concrete end-to-end to make this tangible.

Step 1: SQL query (runs in BigQuery, scheduled Monday 07:00 UTC)

SELECT
  DATE_TRUNC(created_at, WEEK) AS week,
  COUNT(DISTINCT user_id) AS active_users,
  SUM(revenue_usd) AS revenue_usd,
  COUNT(DISTINCT CASE WHEN is_new THEN user_id END) AS new_users
FROM events
WHERE created_at BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 2 WEEK) AND CURRENT_DATE
GROUP BY 1
ORDER BY 1 DESC
LIMIT 2;

This returns two rows: this week and last week. A thin Python script computes the deltas and wraps them into the payload JSON.

Step 2: Anomaly check

If abs(delta_revenue_pct) > 15 or abs(delta_active_users_pct) > 20, the relevant field is tagged ALERT.

Step 3: Prompt skeleton

System: You are a data analyst writing a weekly business summary.
You may ONLY reference figures from the JSON payload below.
Do not infer, estimate, or compute any number yourself.
Alerts are marked ALERT and must appear in the opening sentence.

Payload:
{json_payload}

Step 4: Output

The model returns 3 to 5 short paragraphs of English narrative with every figure it mentions traced directly to a field in the payload. The Slack webhook posts it to #exec-metrics. The raw payload and prompt hash are written to a report_audit table.

Total incremental cost per report run: roughly $0.001 using Haiku. For 52 weekly reports a year, that is under $0.10 in LLM spend.

Retrieval, Context Window, and Multi-Period Reports

Monthly or quarterly reports that need to compare many periods will overflow a naive prompt. The fix is not a larger context window: it is smarter retrieval.

• Store each prior report summary (not the full narrative, just the key metrics JSON) in a vector store or a simple indexed table.
• At report time, retrieve the 3 to 5 most relevant prior periods based on the current anomalies. If revenue spiked, retrieve the last spike period for comparison framing.
• Inject only the retrieved comparators into the prompt, not the full history.

This keeps prompts small, fast, and cheap. It also makes the model's historical comparisons accurate because you chose the comparators deterministically, not the model.

Observability and Evals: Know When the System Breaks

An automated report that silently degrades is worse than no automation. Build these checks in from day one.

Structural Evals

After every generation, run a lightweight eval that checks: does the output mention every ALERT-tagged field? Does it contain any number NOT present in the payload (a hallucinated figure)? Does it exceed the max word count? Fail any of these and the report goes to a human queue instead of being delivered.

You can implement this as a second LLM call with a strict yes/no grader prompt, or as a regex pass for number extraction followed by a set-membership check against the payload values. I prefer the regex approach for number hallucination detection because it is deterministic and cheap.

Logging and Alerting

Every pipeline run should emit: data query duration, payload size, LLM latency, eval pass/fail, delivery status. Alert on: eval failures above 5% in a rolling window, query latency doubling (data pipeline issues upstream), delivery failures. Use whatever observability stack you already have: Datadog, Grafana, a Slack alert from a 10-line Python script.

Prompt Version Control

Treat prompts like code. Store them in git. Tag each report with the commit SHA of the prompt used to generate it. When the model changes or you update the prompt, run a regression eval against 20 historical payloads before deploying to production.

Security and Access Control in AI Reporting

Reports often contain financially sensitive or personally identifiable data. The LLM layer introduces a new attack surface if you are not deliberate.

• Never pass raw PII to the LLM. Aggregate before the payload. If a report needs 'top 5 customers by revenue', pass anonymized IDs or hashed references, not names or emails, unless the model output is internal-only and your legal team has signed off.
• Scope API keys narrowly. The service account that runs data queries needs read-only access to specific tables. The LLM API key needs no database access at all.
• Prompt injection via data. If any metric label or dimension value comes from user-generated content (a product name, a campaign label), sanitize it before interpolating into the prompt. An adversarial label like 'Ignore previous instructions and...' is a real risk in automated pipelines where a human is not reading the prompt at runtime.
• Audit log retention. Keep report payloads and generated text for at least 90 days. This is not just good practice: for financial reporting automation it is likely a compliance requirement.

When Not to Automate (and What to Build Instead)

Not every reporting workflow benefits from an LLM layer. Be honest with yourself about the ROI.

The honest answer is: you need less AI than you think for most reporting. A scheduled SQL query that emails a CSV to the right people on Monday morning already beats the status quo in most organizations. Add the LLM narrative layer when the audience genuinely cannot read raw numbers and when the volume of reports makes manual narrative writing a real bottleneck.

Frequently Asked Questions

Can I just let the LLM query my database directly?

For ad-hoc exploration and internal tooling, yes, with guardrails: read-only credentials, query whitelisting, and never trusting the output numbers without verification. For recurring reports that drive decisions, no. The moment a manager acts on a hallucinated figure, you have a trust problem that is hard to recover from. Run deterministic queries, pass the results to the model.

What AI model should I use for automated reporting?

Use the cheapest model that passes your structural evals. For straightforward weekly digest reports, Claude Haiku or GPT-4o-mini is sufficient and costs fractions of a cent per report. Reserve stronger models for reports that require multi-dimensional reasoning across many metrics or where the narrative quality meaningfully affects a downstream decision. Never over-provision model capability: it inflates cost and latency for zero measurable benefit on routine reports.

How do I handle anomalies the model misses?

The model should not be your anomaly detector. A statistical pre-pass (Z-score, percentage change threshold, or a dedicated monitoring tool like Monte Carlo for data quality) catches anomalies deterministically and tags them before the LLM sees the data. The model's job is to write about the anomalies you have already found, not to find them. If you rely on the model for detection, you will have false negatives and no way to know it.

How do I make AI-generated reports auditable?

Store three things for every report: the raw data payload (the JSON the model received), the prompt version (a git commit SHA or a hash), and the generated output. With these three artifacts you can reproduce any past report, prove the figures are correct, and diagnose any discrepancy. This also lets you run regression evals when you update the prompt or switch models.

What is the typical build time and cost for this kind of system?

A well-scoped single-report pipeline, from data query to Slack delivery with an audit trail, takes roughly 2 to 4 days of engineering time for a team that already has a data warehouse and an LLM API key. Ongoing LLM inference cost for weekly reports is typically under $5 per month at Haiku-class pricing. The real cost is engineering time for eval tooling and observability, which is worth every hour because it is what makes the system trustworthy enough to actually use.

Can this replace my BI tool or dashboard?

No, and you should not try to make it. BI tools like Looker, Metabase, or Tableau are purpose-built for interactive exploration, drilling down, and self-service. AI-generated narrative reports are best for push delivery: sending a synthesized summary to people who will not log into a dashboard. The two are complementary. Run your deterministic queries from the same data models your BI tool uses, so the numbers are consistent, and let each layer do what it does best.

Build Reporting Automation That You Can Actually Trust

The pattern is simple and the ROI is real: deterministic queries for numbers, LLM for narrative, structural evals so you know when it breaks, an audit trail so you can prove the figures. The teams that get this right ship reporting automation in a week and never have to defend a hallucinated number to a CFO.

If you want to build this correctly the first time without the trial-and-error, I work with teams as an independent advisor on exactly this kind of system. See my AI automation service page for how I engage, or reach out directly to talk through your specific pipeline.