Advanced Spreadsheets and Reporting Documentation
Index

Examples & Walkthroughs

Six complete scenarios, from an empty instruction to a report that arrives on schedule with the right people. Each one shows the settings, the prompt and the steps. Adapt the names (financial reports, groups, spreadsheets, companies) to your own.

All examples assume an AI provider is set up with AI Report Agent switched on (Setup).

1. Your First Report: Monthly Management Report

Goal: a one-page monthly report for the management team, comparing the month with the same month last year, delivered automatically on the 2nd of every month.

Step 1: House style (once, for all reports). Open AI Report Setup and write:

Use #1E4FA0 as the accent colour. Amounts in thousands with no decimals, negative numbers in parentheses. Write in English. End every report with "Notes and data sources".

Step 2: The instruction. Open AI Report Instructions → New and fill in:

Field Value
Code MGMT-MONTHLY
Description Monthly management report
Period Start Date Formula -1M-CM (first day of last month)
Period Term Date Formula 1M
Comparison Period Date Formula -1Y (the same month last year)
Reports to Keep 13

Period (today) shows last month, for example 01-09-2026 .. 30-09-2026. Prompt:

Monthly management report for the management team.

  1. KPI tiles: revenue, gross margin %, operating result, cash at month end, each with change against the comparison period.
  2. Income statement summary from the financial report P&L-MGMT for the period and the comparison period, with change in amount and %.
  3. Top 10 customers by revenue with change.
  4. Open receivables by age bucket (current, 1–30, 31–60, 61–90, over 90 days past due) as a stacked bar and a table.
  5. A column chart of monthly revenue for the trailing 12 months, this year against last year.
  6. Three bullet points on the largest movements, in plain language.

Step 3: Try it. Choose Generate and Open. After a few minutes the report opens. Check:

  • Do the numbers match? Compare the P&L figures with the financial report itself.
  • Where did they come from? Generation Log shows each query, for example which accounts the agent used for "cash".

Step 4: Sharpen. If the agent took bank ledger entries for cash but you want G/L accounts 10100..10300, add to the prompt: "Cash = balance of G/L accounts 10100..10300 at the period end." Generate again until the report is right.

Step 5: Schedule. Choose Schedule. On the job queue entry card:

  • set Next Run Date Formula to 1M,
  • set Earliest Start Date/Time to the 2nd of next month, 06:00,
  • choose Set Status to Ready.

Every month a new report appears in AI Reports. Reports to Keep 13 keeps a year of history plus the latest.

2. Designing a Report with the Agent

Goal: a weekly sales report, when you are not sure yet what it should contain.

  1. Create the instruction SALES-WEEKLY with Start -1W-CW, Term 1W, Compare -1W (last week against the week before), and no prompt.
  2. Choose Interactive Designer Agent. The report area shows Nothing designed yet. The chat is on the right.
  3. Type: "A weekly sales report for the sales managers. Start with revenue, number of invoices and average invoice value." The agent queries and writes the page, which appears within seconds.
  4. Shape it in conversation:
    • "Add revenue by salesperson as a bar chart."
    • "Only domestic customers: customer posting group DOMESTIC."
    • "Add the 10 largest invoices of the week with customer and salesperson."
    • "Why does salesperson JO have negative revenue?" The agent looks it up and explains: a credit memo.
    • "Show credit memos separately under the table."
  5. When the page is right, type: "Write the instruction." The agent writes a detailed prompt with the exact tables, filters, sections and formats, and checks the period formulas. Open Instruction to read it.
  6. Choose Save as Report to keep this week's report.
  7. Choose Generate Unattended. A fresh agent builds the report from the prompt alone. Open Reports and compare. If something differs, tell the designer: "The unattended run sorted the top invoices by date; make the instruction say by amount."
  8. Close the designer and Schedule the instruction for Monday mornings: keep only Run on Mondays, time 07:00, Set Status to Ready.

3. A Quarterly Board Pack Shared with the Board

Goal: finance maintains the quarterly report; the board may read each finished quarter, but not run the instruction or see drafts.

  1. Create BOARD-Q:
    • formulas: Start -1Q-CQ, Term 1Q, Compare -1Y,
    • prompt: "Quarterly report for the board of directors: … (KPI tiles, P&L from P&L-BOARD, balance sheet from BS-BOARD at quarter end against the same quarter last year, cash flow as a sankey from sources to uses, outlook paragraph to be completed by the CFO marked as a placeholder.)"
  2. Permissions on the instruction:
    • you are Owner,
    • add the security group FINANCE-MGMT as Owner so colleagues can maintain it,
    • do not add the board.
  3. After quarter end, choose Generate and Open, review the report and regenerate if needed. Delete drafts you do not want to keep.
  4. On the final report in AI Reports, choose Permissions and add the security group BOARD with Read Only. Only this report becomes visible to the board; the instruction and other reports stay with finance.
  5. For members who do not use Business Central, choose Print in the viewer → Save as PDF, or Download the .html file and send it.

4. A Daily Collections Report for the Credit Team

Goal: every working day at 07:00, a current overview of overdue receivables for the credit controllers.

  1. Create AR-DAILY without period formulas. The report describes the current state.
  2. Set Reports to Keep to 5: one week of history.
  3. Prompt:

Daily collections overview for the credit controllers. KPI tiles: total open receivables, overdue amount, overdue % (rising is bad), number of customers over their credit limit (status tile: warning above 5, critical above 10). Ageing by due date in buckets current, 1–30, 31–60, 61–90, over 90 days as a stacked bar chart. The 20 customers with the largest overdue amount: balance, overdue amount, oldest due date, credit limit, salesperson; customers over their credit limit in red. Keep it to one printed page.

  1. Permissions:
    • add the group CREDIT with Read Only,
    • add the credit manager personally as Owner.
  2. Schedule: the default weekdays and 06:00 suit this; change the time to 07:00 and Set Status to Ready.

The credit controllers open AI Reports each morning and find a fresh report. The job runs as the user who scheduled it, so that user needs read access to customer data.

5. Reusing a Spreadsheet the Team Already Has

Goal: the sales team maintains a spreadsheet SALES-DASH with a period slicer in C2 and a page region KPI. The monthly report should use exactly those figures.

  1. Create SALES-MONTHLY: Start -1M-CM, Term 1M, Compare -1Y.
  2. Prompt:

Use the spreadsheet SALES-DASH. Set its period slicer to the report period and read the page KPI; then set it to the comparison period and read KPI again. Report the KPI page's figures as KPI tiles with change, and the regional breakdown as a table and a bar chart. Do not compute these figures from ledger data yourself.

  1. Generate and Open. In the Generation Log, aireport_opensheet and aireport_readsheet show the slicer values the agent set and the pages it read.

The sheet is opened read-only and never saved. The person running the report needs access to the spreadsheet. Changing the sheet changes the report from the next run.

6. Comparing Companies

Goal: a monthly comparison of three companies in the same environment.

  1. Create GROUP-COMPARE: Start -1M-CM, Term 1M, Compare -1Y.
  2. Prompt:

Monthly comparison of the companies CRONUS US, CRONUS Canada and CRONUS UK. For each: revenue, gross margin %, operating result, from the financial report P&L-MGMT run in that company, with change against the comparison period. A grouped column chart of revenue per company, this year against last year. Note that amounts are in each company's local currency and are not converted.

  1. Generate and Open.

The user running the report needs access to all three companies. Say how to treat currencies: the agent does not convert between companies unless the prompt tells it how.

More Ideas

Report Formulas Prompt idea
Inventory health none Stock value by location, slow movers (no sales in 180 days), items below reorder point
Purchase spend -CQ 1Q -1Y Spend by vendor and by item category, top 15 vendors, share of spend with the top 5
Budget follow-up -1M-CM 1M Actual vs. budget 2026 by department (dimension 1), variance chart coloured by sign, year to date
Project status none Open jobs with budget, actual cost, % complete, invoiced vs. contract value; jobs over budget in red
Month-end close guide none The close checklist as a gantt chart and the approval flow as a diagram, for new staff
Year-end summary -1Y-CY 1Y -1Y Full-year P&L and balance sheet from your financial reports, monthly revenue line chart, top customers and vendors of the year