Advanced Spreadsheets and Reporting Documentation
Index

Financial Report Overview

This guide explains how Financial Reports work in Business Central and how Advanced Spreadsheets and Reporting enhances them.

Understanding Financial Reports

Financial Reports in Business Central are powerful tools for creating financial statements. They consist of two main components:

  1. Row Definition: Defines the rows of the report - what data to show
  2. Column Definition: Defines the columns - how to present the data

Standard Financial Reports Structure

Row Definition Components

Each row in a financial report can be configured with:

Field Purpose
Row No. Unique identifier for the row
Description Text displayed in the report
Totaling Type How the row calculates its value
Totaling G/L accounts, formulas, or other rows to total
Row Type Net Change, Balance at Date, or Beginning Balance
Amount Type Net Amount, Debit Amount, or Credit Amount

Standard Totaling Types

Business Central provides these standard totaling types:

  • Posting Accounts: Total from specific G/L accounts
  • Total Accounts: Use the G/L account's totaling
  • Formula: Calculate using other row values
  • Set Base For Percent: Base for percentage calculations
  • Cost Type: Link to cost accounting
  • Cash Flow Entry Accounts: Cash flow data
  • Cash Flow Total Accounts: Cash flow totals

Indicator Totaling Type

Advanced Spreadsheets and Reporting adds a new totaling type: Indicator

When you select Indicator as the totaling type:

  • The row no longer calculates from G/L accounts
  • Instead, it uses your configured indicator definition
  • Data can come from any table in Business Central

How Indicators Integrate

When Business Central calculates a financial report:

  1. For each row, it checks the totaling type
  2. Standard types use G/L entry calculations
  3. Indicator types call the Advanced Spreadsheets and Reporting calculation engine
  4. The engine executes the indicator logic and returns the value
  5. The value appears in the report like any other row

Calculation Flow

Financial Report Request
        ↓
Row Type Check
        ↓
┌───────────────────────┐     ┌───────────────────────┐
│ Standard Totaling     │     │ Indicator Totaling    │
│ (G/L Accounts, etc.)  │     │ (Indicator engine)    │
└───────────────────────┘     └───────────────────────┘
        ↓                              ↓
  G/L Entry Calc                Indicator Calculate
        ↓                              ↓
        └──────────┬───────────────────┘
                   ↓
            Report Results

Creating a Financial Report with Indicators

Step 1: Create or Edit a Row Definition

  1. Search for Financial Reports
  2. Select a report and click Edit Row Definition
  3. Or create a new row definition

Step 2: Add Indicator Rows

  1. Add a new line
  2. Set Totaling Type to Indicator
  3. Open the Define Indicator page: use the lookup on Totaling, or the assist-edit (…) button on the Indicator Defined column (shown after Totaling Type). Simply clicking the Totaling cell does nothing, and the page does not open when the app is not licensed.
  4. Configure the indicator (see Indicator Card)

Step 3: Mix with Standard Rows

You can mix indicator rows with standard rows:

Row 10: Revenue (Posting Accounts - G/L)
Row 20: COGS (Posting Accounts - G/L)
Row 30: Gross Profit (Formula: 10-20)
Row 40: Open Orders (Indicator - Sales Line table)
Row 50: Customer Count (Indicator - Customer table)

Step 4: Use in Formulas

Indicator rows can be used in formula rows:

Row 60: Revenue per Customer (Formula: 10/50)

Column Definition Integration

Indicators respect the column definition settings:

Date Filtering

When using Column Period filter type on an indicator:

  • The column's date calculation is applied
  • Supports all data column types: Net Change, Balance at Date, Beginning Balance, Year to Date, Rest of Fiscal Year and Entire Fiscal Year
  • Formula columns apply no period filter

Multi-Company

When a column or row specifies a company:

  • Indicators read from that company's data
  • Indicator values are not currency-converted; only standard G/L-based cells are converted

Best Practices

Report Design

  1. Group Related Data: Keep indicator rows near related G/L rows
  2. Use Clear Descriptions: Indicators can pull from anywhere, so be specific
  3. Document Your Indicators: Note what each indicator calculates
  4. Test Thoroughly: Verify indicator results against source data

Performance

  1. Limit Indicator Rows: Each indicator adds processing time
  2. Use Specific Filters: Reduce the data each indicator processes
  3. Consider Caching: For complex reports, run during off-peak hours

Maintenance

  1. Review Periodically: Ensure indicators still return expected values
  2. Update Filters: Adjust for new dimension values or business changes
  3. Version Control: Track changes to indicator definitions

Example: Enhanced Income Statement

Here's an example mixing standard and indicator rows:

INCOME STATEMENT
================
10  Revenue                         G/L Accounts
20  Cost of Goods Sold              G/L Accounts
    ────────────────
30  GROSS PROFIT                    Formula: 10-20
40  Operating Expenses              G/L Accounts
    ────────────────
50  OPERATING INCOME                Formula: 30-40

KEY PERFORMANCE INDICATORS
==========================
100 Open Sales Orders               Indicator: Sales Header (Count)
110 Open Sales Order Value          Indicator: Sales Line (Sum Amount)
120 Average Order Value             Formula: 110/100
130 Pending Invoices                Indicator: Cust. Ledger Entry (Count)
140 Days Sales Outstanding          Indicator: Custom (requires developer code)

Next Steps