Advanced Spreadsheets and Reporting Documentation
Index

Cross-Company Data Access

This guide provides detailed instructions for accessing data across multiple companies in your financial reports using Advanced Spreadsheets and Reporting.

Understanding Cross-Company Access

How Business Central Handles Companies

In Business Central:

  • Each company is a separate data silo
  • Users typically work in one company at a time
  • Cross-company queries require explicit setup

How Cross-Company Access Works

The app uses Business Central's ChangeCompany functionality:

  • Temporarily switches data context
  • Reads from specified company
  • Returns to current company context

Configuration Methods

Row-Level Company Specification

Add the Company field to your row definition lines:

  1. Open your Row Definition
  2. Personalize to show the Company column if it is hidden
  3. Select a company from the lookup on specific rows

Example:

Row Description Totaling Company
10 Local Revenue 40000..49999 (blank)
20 US Revenue 40000..49999 CRONUS USA
30 Total 10+20 (blank)

Column-Level Company Specification

Add the Company field to your column definitions:

  1. Open the Column Definition
  2. Add/show the Company column
  3. Select companies from the lookup on columns

Example:

Col Header Type Company
A Local Net Change (blank)
B USA Net Change CRONUS USA
C Europe Net Change CRONUS EU

Data Flow

Standard G/L Data

When company is specified:

Row/Column has Company?
     ↓ Yes
ChangeCompany to specified company
     ↓
Read G/L Entries / G/L Budget Entries / Cost Budget Entries /
Statistical Ledger or Analysis View Entries
     ↓
Read G/L Accounts / Cost Types
     ↓
Calculate totals
     ↓
Return to current company

Cost Type actual entries, Cash Flow rows and row definitions based on an Analysis View are not redirected; they always read the reporting company.

Indicator Data

Indicators also respect company settings:

Determine company (column > row > current)
     ↓
Open RecordRef in specified company
     ↓
Apply filters
     ↓
Execute calculation
     ↓
Return result (not currency-converted)

Currency Handling

Automatic Detection

For standard (non-indicator, non-formula) cells the app automatically detects currency differences:

IF Remote_GL_Setup."LCY Code" <> GL_Setup."LCY Code" THEN
    Convert Amount

Conversion Process

  1. Get LCY Code from remote company's G/L Setup
  2. Get LCY Code from current company's G/L Setup
  3. If different, get exchange rate
  4. Convert amount: Foreign × Rate = Local

Indicator rows are never converted; formula rows are not converted again (the cells they sum already are).

Exchange Rate Source

Exchange rates come from:

  • The reporting company's Currency Exchange Rate table
  • Rate for the WorkDate
  • Rate for the remote company's LCY code

The reporting company therefore needs a Currency record for the remote company's LCY code, with exchange rates covering the work date.

Practical Examples

Example 1: Multi-Company Revenue Comparison

Goal: Compare revenue across three subsidiaries.

Column Definition:

Column A: Main Company (blank company)
Column B: US Subsidiary (CRONUS USA)
Column C: EU Subsidiary (CRONUS EU)
Column D: Total (Formula: A+B+C)

Row Definition:

Row 10: Total Revenue (G/L 40000..49999)
Row 20: Cost of Sales (G/L 50000..59999)
Row 30: Gross Profit (Formula: 10-20)

Result: See each company's figures side by side with totals.

Example 2: Consolidated Balance Sheet

Goal: Show consolidated assets from all companies.

Row Definition:

Row 10: Current Assets - Main    G/L Accounts, Company: (blank)
Row 20: Current Assets - US      G/L Accounts, Company: CRONUS USA
Row 30: Current Assets - EU      G/L Accounts, Company: CRONUS EU
Row 40: Total Current Assets     Formula: 10+20+30

Column Definition: Standard column definition showing Balance at Date.

Example 3: KPIs Across Companies

Goal: Show operational KPIs from each company.

Row Definition:

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

The indicator configuration points to Sales Header table, uses Count method. Indicator values are not currency-converted, so counts and quantities are the safest cross-company indicators.

Permissions Required

User Setup

Users are tenant-wide in Business Central, so no separate user record is needed per company. For cross-company access, users need:

  1. Permission sets whose assignment covers each company named in the report (assigned for all companies, or once per company)
  2. Read access to the required tables in those companies

Minimum Permissions

Object Type Objects Permission
Table G/L Entry Read
Table G/L Account Read
Table G/L Budget Entry Read (budget columns)
Table Cost Budget Entry, Cost Type Read (cost budget columns)
Table Statistical Ledger Entry, Analysis View Entry Read (statistical account rows)
Table General Ledger Setup Read
Table Currency Exchange Rate Read (reporting company)
Table (Indicator tables) Read

Super User Alternative

Users with SUPER permission set have access to all companies by default.

Best Practices

Design

  1. Document Company Usage: Maintain a list of which reports use which companies
  2. Consistent Naming: Use exact company names consistently
  3. Clear Headers: Label columns/rows with company names

Performance

  1. Minimize Cross-Company Calls: Each call has overhead
  2. Use Formulas for Totals: Calculate totals in BC, not database
  3. Filter Data: Reduce dataset size with filters

Security

  1. Principle of Least Privilege: Grant only needed permissions
  2. Audit Access: Track who accesses which companies
  3. Review Periodically: Ensure permissions are still appropriate

Troubleshooting

Company Not Found

Error: Data returns as zero or error.

Causes:

  • Company was renamed or deleted after it was selected

Solution:

  • Re-select the company from the lookup on the row or column

Permission Denied

Error: Error when accessing company data.

Causes:

  • Permission sets are assigned only for the reporting company
  • Missing table permissions

Solution:

  • Assign the permission sets for all companies, or add an assignment for the target company
  • Grant read permission on the required tables

Currency Not Converting

Issue: Values seem wrong (not converted).

Causes:

  • Same LCY in both companies (no conversion needed)
  • The row is an indicator (never converted)
  • No Currency record or exchange rate for the remote LCY code in the reporting company
  • LCY not set in G/L Setup

Solution:

  • Verify LCY codes are different
  • Create the Currency and exchange rates in the reporting company
  • Check G/L Setup configuration

Slow Performance

Issue: Reports take long to generate.

Causes:

  • Many cross-company queries
  • Large datasets
  • Network latency (for remote databases)

Solution:

  • Reduce number of companies
  • Add filters to reduce data
  • Consider scheduled generation

Advanced Scenarios

Different Chart of Accounts

If companies have different G/L account structures:

  • Create mapping rows
  • Use formulas to align data
  • Or standardize chart of accounts

Different Fiscal Years

If companies have different fiscal calendars:

  • Be careful with YTD calculations
  • Consider using specific date ranges
  • Document fiscal year differences

Elimination Entries

For true consolidation:

  • Add rows for elimination adjustments
  • Manual entry or formula-based
  • Document elimination logic

Company on Spreadsheet Regions

Spreadsheet regions have the same idea as a single setting: every region that reads the database — Table, Pivot, FlowField, Image, SQL, Budget Matrix and Financial Report — carries a Company field. A Financial Report region with a company reads the entries from that company exactly as a company on a row or column does (the row and column definitions and the chart of accounts stay the current company's, and a company on an individual line or column keeps precedence). The setting accepts [Address], so a ?FIELD(Company, Name) slicer switches a whole dashboard between companies. See Reading Another Company.