Advanced Spreadsheets and Reporting Documentation
Index

Column Definitions

Column definitions (column layouts) define how data is presented across columns in your financial reports. Advanced Spreadsheets and Reporting extends column definitions with multi-company capabilities.

Understanding Column Definitions

Standard Column Definition Fields

Field Purpose
Column No. Unique identifier for the column
Column Header Text shown at the top of the column
Column Type How dates affect the calculation
Ledger Entry Type Source of the entries (G/L, Budget, etc.)
Amount Type Net, Debit, or Credit amounts
Comparison Period Formula Date formula for period comparisons
Comparison Date Formula Specific date adjustments
Show Opposite Sign Reverse the sign of values
Show Always, Never, or When Positive/Negative

Column Types

Understanding column types is essential for proper date filtering:

Column Type Description
Net Change Shows changes within the specified period
Balance at Date Shows cumulative balance up to end of period
Beginning Balance Shows balance at start of period
Year to Date Shows cumulative for the fiscal year
Rest of Fiscal Year Remaining period in the fiscal year
Entire Fiscal Year Full fiscal year data

Company Field

Advanced Spreadsheets and Reporting adds a Company field to column definitions.

Purpose

The Company field allows you to:

  • Display data from multiple companies side by side
  • Create comparative analysis across entities
  • Build consolidated views

How to Use

  1. Search for Column Definitions in Business Central
  2. Select or create a column definition
  3. Add columns as needed
  4. In the Company field, select a company from the lookup

Example: Multi-Company Comparison

Column  Header          Type          Company
1       US Actual       Net Change    CRONUS USA
2       EU Actual       Net Change    CRONUS EU
3       UK Actual       Net Change    CRONUS UK
4       Total           Formula       (blank)

Company Field Behavior

With Standard G/L Rows

When you specify a company on a column:

  • G/L entries are read from that company
  • G/L accounts must exist in that company
  • Amounts are converted to the reporting company's currency when the LCY codes differ (see Currency Handling)

With Indicator Rows

When indicators encounter a column with a company:

  • The indicator reads data from that company
  • All indicator filters apply in that company's context
  • Indicator values are never currency-converted

Currency Handling

Standard (non-indicator, non-formula) cells are converted automatically:

  1. The other company's LCY code (General Ledger Setup) is compared with the reporting company's LCY code
  2. If they differ, 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 currency

Indicator rows are never converted, and formula rows are not converted again (they sum cells that are already converted).

Requirements:

  • The reporting company must have a Currency record for the other company's LCY code, with exchange rates covering the work date
  • LCY codes must be set in General Ledger Setup in both companies

Configuring Column Definitions

Creating a New Column Definition

  1. Search for Column Definitions
  2. Click New or + New
  3. Enter a Name (code)
  4. Add columns with their settings

Adding the Company Field

If the Company field isn't visible:

  1. Open the Column Definition
  2. Personalize the page (gear icon → Personalize)
  3. Add the Company field to your view

Single Company with Periods

Col  Header          Type               Formula
1    This Month      Net Change         (blank)
2    Last Month      Net Change         <-1M>
3    YTD             Year to Date       (blank)
4    Same Month LY   Net Change         <-1Y>

Multi-Company Current Period

Col  Header          Type         Company
1    Main Company    Net Change   (blank)
2    US Division     Net Change   CRONUS USA
3    EU Division     Net Change   CRONUS EU
4    Consolidated    Formula      (blank)

Budget Comparison

Col  Header          Type          Entry Type
1    Actual          Net Change    G/L Entries
2    Budget          Net Change    G/L Budget
3    Variance        Formula       (1-2)
4    Variance %      Formula       (3/2)*100

Interaction with Indicators

How Indicators Use Column Settings

When an indicator calculates:

  1. Company Priority
    • Column company takes precedence
    • If column is blank, row company is used
    • If both blank, current company is used
  2. Date Filtering (Column Period)
    • Indicator filters marked as "Column Period" use the column's date calculation
    • Respects all data column types (Net Change, Balance at Date, Beginning Balance, Year to Date, Rest of Fiscal Year, Entire Fiscal Year)
    • Formula columns apply no period filter to the indicator
    • Date formulas on the filter shift the column's start and end dates further
  3. Result Formatting
    • Values follow the column's Show settings
    • Opposite sign is applied if configured

Example: Indicator with Column Period Filter

If your indicator has a filter:

  • Field: Posting Date
  • Filter Type: Column Period

And your column is:

  • Type: Year to Date
  • Comparison Period Formula: <-1Y>

The indicator will calculate for last year's YTD period.

Best Practices

Design Principles

  1. Consistent Naming: Use clear, consistent column headers
  2. Logical Grouping: Group related columns together
  3. Formula Columns Last: Place calculated columns after data columns

Multi-Company Layouts

  1. Verify Companies: Ensure company names are correct
  2. Test Thoroughly: Check that data appears as expected
  3. Consider Currency: Document whether values are converted

Performance

  1. Limit Columns: More columns means more calculations
  2. Simplify Formulas: Complex nested formulas slow reports
  3. Use Appropriate Types: Choose the right column type for your needs

Troubleshooting

Column Shows Zero

  • Verify the company name is correct
  • Check that G/L accounts exist in that company
  • Ensure you have permission to read from that company

Currency Values Seem Wrong

  • Check that the reporting company has a Currency record and exchange rates for the other company's LCY code
  • Verify LCY codes are set in both companies
  • The rate used is the one valid on the work date

Formula Column Not Calculating

  • Ensure the formula syntax is correct
  • Check that referenced columns exist
  • Verify the order of columns (formulas can only reference earlier columns)