Advanced Spreadsheets and Reporting Documentation
Index

Inter-Company Overview

Advanced Spreadsheets and Reporting enables you to create financial reports that pull data from multiple companies in your Business Central environment. This guide explains the multi-company capabilities.

Understanding Multi-Company Reporting

The Challenge

In standard Business Central:

  • Financial reports operate within a single company
  • Comparing or consolidating data requires manual effort
  • Currency differences complicate comparisons

The Solution

Advanced Spreadsheets and Reporting allows you to:

  • Specify companies on individual report lines
  • Specify companies on column definitions
  • Automatically convert standard G/L-based amounts to the reporting company's currency
  • Create consolidated views

How It Works

Company Specification

You can specify a company in two places:

  1. Row Definition Line: The Company field on each line (shown after Show)
  2. Column Definition: The Company field on each column

In both places the company is picked from a lookup of the companies in the environment.

Priority Rules

When both are specified:

Column Company > Row Company > Current Company
  • If column has a company: Use that company
  • Else if row has a company: Use that company
  • Else: Use the current company

Setting Up Multi-Company Reports

Method 1: Company on Rows

Use this when you want each row from a different company:

Row 10: US Revenue       Company: CRONUS USA
Row 20: EU Revenue       Company: CRONUS EU
Row 30: UK Revenue       Company: CRONUS UK
Row 40: Total Revenue    Formula: 10+20+30

Result: Each row shows revenue from its specified company.

Method 2: Company on Columns

Use this when you want to compare the same data across companies:

Column A: US Data        Company: CRONUS USA
Column B: EU Data        Company: CRONUS EU
Column C: UK Data        Company: CRONUS UK
Column D: Total          Formula: A+B+C

Result: Each column shows the row's data from that company.

Method 3: Combined

Use both for complex matrix reports:

Rows with specific accounts (no company)
Columns with company specifications

Result: See each account from each company

Currency Conversion

Automatic Conversion

When a standard cell (Posting Accounts, Total Accounts, Cost Type, Cash Flow, Statistical Account) is read from a company with a different Local Currency (LCY):

  1. The other company's LCY code is compared with the reporting company's LCY code
  2. The exchange rate for that LCY code on the work date is read from the reporting company's Currency Exchange Rate table
  3. The cell value is converted to the reporting company's LCY

Conversion applies only to these standard cells. Indicator rows are never converted, and formula rows (Formula, Set Base For Percent) are not converted again because the cells they sum are already converted.

Requirements

For currency conversion to work:

  • Both companies must have LCY codes set in General Ledger Setup
  • The reporting company must have a Currency record for the other company's LCY code
  • That Currency must have an exchange rate valid on the work date in the reporting company's Currency Exchange Rate table

Conversion Logic

Standard (non-indicator, non-formula) cell?
     ↓ Yes
Source Company LCY ≠ Reporting Company LCY?
     ↓ Yes
Get Work-Date Exchange Rate for Source LCY (reporting company)
     ↓
Convert: Amount × Exchange Rate
     ↓
Display in Reporting Company LCY

Working with Indicators

Indicator Company Resolution

Indicators also respect company settings:

  1. Check column for company
  2. If blank, check row for company
  3. If blank, use current company
  4. Read indicator data from that company

Indicator values are returned as read; they are never currency-converted, even when the other company has a different LCY.

Example: Cross-Company Indicator

Row: Open Orders (Indicator)
     Table: Sales Line
     Field: Amount
     Company: CRONUS USA

Result: Shows open orders from CRONUS USA

Multi-Company Indicator Rows

Row 10: US Orders    Indicator, Company: CRONUS USA
Row 20: EU Orders    Indicator, Company: CRONUS EU
Row 30: Total        Formula: 10+20

If the indicators return amounts, the total mixes the two companies' currencies; use counts and quantities, or fields that already hold a common currency.

Use Cases

Consolidated Income Statement

Revenue
  US Sales (Company: USA)
  EU Sales (Company: EU)
  Total Sales (Formula)
  
Expenses
  US Expenses (Company: USA)
  EU Expenses (Company: EU)
  Total Expenses (Formula)
  
Profit (Formula: Sales - Expenses)

Company Comparison

           USA     EU      UK      Total
Revenue    1000    800     600     2400
Costs      700     500     400     1600
Profit     300     300     200     800

Regional Dashboard

Combine G/L data with indicators from multiple companies:

  • G/L balances from each region
  • Operational KPIs from each region
  • Consolidated totals

Best Practices

Setup

  1. Plan Your Structure: Decide whether to use row or column companies
  2. Pick Companies from the Lookup: The Company field offers the companies in the environment
  3. Test Currency Conversion: Verify the reporting company has rates for the other companies' LCY codes

Performance

  1. Limit Companies: Each company adds processing time
  2. Use Formulas for Totals: Let BC calculate totals
  3. Consider Caching: Run complex reports during off-peak hours

Maintenance

  1. Document Company Usage: Note which reports use which companies
  2. Review Exchange Rates: Keep rates current
  3. Test After Changes: Verify after company structure changes

Permissions

Users need appropriate permissions:

  • Read access in all specified companies
  • G/L account and ledger access in all companies
  • Table access for indicators in all companies

Setting Up Cross-Company Access

Users in Business Central are tenant-wide, so the user itself exists in every company. What matters is the permission-set assignment: each permission set is assigned either for all companies or for a specific company, and the assignments must cover every company named in the report.

Limitations

Current Limitations

  • Company redirection covers G/L Entry, G/L Budget Entry, Cost Budget Entry and Statistical Ledger Entry / Analysis View Entry reads; Cost Type actual entries, Cash Flow rows and row definitions based on an Analysis View are not redirected
  • Indicator rows are not currency-converted
  • Currency conversion uses the work-date rate only
  • Some complex scenarios may need formula rows

Workarounds

  • For historical rates: Create custom indicators
  • For complex consolidation: Use multiple reports
  • For elimination entries: Add manual rows

Troubleshooting

Zero Values from Other Companies

Causes:

  • Company name spelled incorrectly
  • No permission in that company
  • G/L accounts don't exist there

Solutions:

  • Re-select the company from the lookup
  • Check permission-set assignments for that company
  • Verify account structure

Currency Conversion Not Working

Causes:

  • No Currency record or exchange rate for the other company's LCY code in the reporting company
  • LCY codes not set
  • No rate valid on the work date
  • The row is an indicator (never converted)

Solutions:

  • Create the Currency and exchange rates in the reporting company
  • Verify G/L Setup in both companies
  • Check rate effective dates against the work date

Slow Performance

Causes:

  • Many companies
  • Large datasets
  • Complex calculations

Solutions:

  • Reduce companies in single report
  • Add filters to reduce data
  • Split into multiple reports