Advanced Spreadsheets and Reporting Documentation
Index

Building KPI-style Financial Reports

This guide explains how to build a financial report that reads like a dashboard, by combining standard rows, formula rows and indicator rows in one row definition.

For interactive dashboards use the spreadsheet side of Advanced Spreadsheets and Reporting instead: spreadsheets with KPI Card and Chart regions, the slicer dashboards generated by Open as Spreadsheet on a financial report or G/L budget, and the Spreadsheet Role Center. See the spreadsheet documentation for those.

What is a KPI-style Report?

A KPI-style financial report presents:

  • Key financial metrics at a glance
  • Multiple indicators in one report
  • Visual hierarchy of important data
  • Quick access to critical information

Designing the Report

Design Principles

  1. Key Metrics First: Most important data at the top
  2. Visual Hierarchy: Use formatting to guide attention
  3. Consistent Layout: Predictable structure
  4. Appropriate Detail: Summary with drill-down capability

Dashboard Structure Example

┌─────────────────────────────────────┐
│        EXECUTIVE DASHBOARD          │
├─────────────────────────────────────┤
│ KEY FINANCIAL METRICS               │
│   Revenue          ███████ 1.5M     │
│   Profit           ██████  900K     │
│   Cash             █████   750K     │
├─────────────────────────────────────┤
│ OPERATIONAL KPIs                    │
│   Open Orders      125              │
│   Backlog Value    350K             │
│   DSO              42 days          │
├─────────────────────────────────────┤
│ PERFORMANCE vs TARGET               │
│   Revenue %        105%  ▲          │
│   Cost Control     98%   ▼          │
│   Collection       95%   ▲          │
└─────────────────────────────────────┘

Creating a KPI-style Report

Step 1: Design the Row Definition

Create rows for each metric:

Row  Description              Type           Totaling/Config
────────────────────────────────────────────────────────────
     KEY FINANCIAL METRICS    (Header)
10   Revenue YTD              Posting Accts  4xxxx
20   Gross Profit             Formula        10-COGS
30   Net Profit               Formula        20-OPEX
40   Cash Position            Indicator      Bank Account Ledger Entry
────────────────────────────────────────────────────────────
     OPERATIONAL KPIS         (Header)
100  Open Sales Orders        Indicator      Count
110  Order Backlog Value      Indicator      Sum
120  Days Sales Outstanding   Indicator      Custom (requires developer code)
────────────────────────────────────────────────────────────
     PERFORMANCE              (Header)
200  Revenue vs Budget        Formula        %
210  Cost vs Budget           Formula        %

Step 2: Configure Indicators

For each indicator row, set up:

Open Sales Orders (Row 100):

  • Table: Sales Header
  • Method: Count
- Filter: Document Type = Order, Status = Open Released

Order Backlog Value (Row 110):

  • Table: Sales Line
  • Method: Sum
  • Field: Line Amount
  • Filter: Document Type = Order, Outstanding Qty > 0

Cash Position (Row 40):

  • Table: Bank Account Ledger Entry
  • Method: Sum
  • Field: Amount (LCY)
  • Filter: Posting Date, Filter Type: Column Period (use a Balance at Date column to get the balance)

Days Sales Outstanding (Row 120):

  • Method: Custom
  • Requires an AL event subscriber to OnCustomCalculate written by a developer; without it the row returns 0

Step 3: Design the Column Definition

Simple KPI column definition:

Col  Header    Type           Purpose
─────────────────────────────────────
A    Current   Net Change     Current period
B    Prior     Net Change     Comparison period
C    Var       Formula        Variance (A-B)
D    Var%      Formula        Variance % ((A-B)/B)

Step 4: Apply Formatting

Use formatting fields:

  • Bold: Section headers, totals
  • Underline: Subtotals
  • Indentation: Detail under headers
  • Show: Hide intermediate calculations

Dashboard KPI Examples

Financial KPIs

KPI Source Method
Revenue G/L Entries Standard
Gross Margin Formula Standard
Working Capital G/L Entries Standard
Current Ratio Formula row (assets row / liabilities row) Formula

Operational KPIs

KPI Source Method
Orders in Pipeline Sales Header Count
Average Order Value Sales Header Average
Inventory Value Item Ledger Entry Sum
Days Inventory Indicator Custom (requires developer code)

Customer KPIs

KPI Source Method
Customer Count Customer Count
Total Receivables Cust. Ledger Entry Sum
Average Balance Customer Average
Past Due Amount Cust. Ledger Entry Sum

Best Practices

Layout

  1. Group Related Metrics: Financial together, operational together
  2. Logical Flow: From summary to detail
  3. Consistent Spacing: Use blank rows for visual separation
  4. Clear Labels: Descriptive row descriptions

Data Quality

  1. Verify Calculations: Test all indicators
  2. Cross-Check Totals: Ensure formulas are correct
  3. Document Sources: Note where each metric comes from

Maintenance

  1. Regular Review: Update metrics as business changes
  2. Performance Testing: Monitor calculation time
  3. User Feedback: Adjust based on user needs

Interactive Dashboards in Business Central

The financial report itself is static. For an interactive dashboard inside Business Central:

  1. Open the financial report and click Open as Spreadsheet; a spreadsheet with a title and wired date-range and dimension slicers is generated
  2. Add KPI Card regions for headline numbers and Chart regions for trends in the Spreadsheet editor
  3. Save the spreadsheet and pick it as the spreadsheet shown on the Spreadsheet Role Center (Select Spreadsheet on the role center part)

Power BI Dashboard Integration

Using the API for Power BI Dashboards

  1. Connect Power BI to the financial report API
  2. Create visuals from API data
  3. Add formatting and interactivity
  4. Share dashboard in Power BI Service

Advantages

  • Rich visualization options
  • Interactive filtering
  • Automated refresh
  • Mobile access

Exporting the Data

To Excel

  1. Create a print job for the report
  2. Set format to XLSX
  3. Run or schedule
  4. Use Excel for further analysis

To PDF

  1. Create print job
  2. Set format to PDF
  3. Distribute to stakeholders
  4. Consistent formatting

Troubleshooting

Slow Performance

  • Reduce number of indicators
  • Add more specific filters
  • Use formulas instead of redundant indicators

Inconsistent Results

  • Verify date filters
  • Check dimension filters
  • Review indicator configurations

Missing Data

  • Confirm data exists for period
  • Check permissions
  • Verify indicator table/field selections