Advanced Spreadsheets and Reporting Documentation
Index

Account Schedule Extensions

Advanced Spreadsheets and Reporting extends the standard row definition (account schedule) functionality with additional fields and capabilities. This guide covers all the enhancements available.

Extended Fields on Row Definition Lines

Company Field

The Company field allows you to specify which company's data should be used for this line.

Purpose:

  • Pull G/L data from a different company
  • Create consolidated reports
  • Compare data across companies

How to Use:

  1. Open the Row Definition
  2. Find the Company column (add it if not visible)
  3. Select a company from the lookup

Behavior:

  • When blank: Uses the current company
  • When specified: Pulls data from that company
  • Applies to G/L Entry, G/L Budget Entry, Cost Budget Entry and Statistical Ledger/Analysis View Entry calculations, and to indicators
  • Currency conversion is applied only to these standard (non-indicator, non-formula) cells; indicator rows are never converted, and formula rows sum cells that are already converted

Indicator Defined Field

The Indicator Defined field is a flow field that shows whether an indicator is configured for this line.

Purpose:

  • Quickly identify which lines use indicators
  • Visual confirmation of indicator setup

Values:

  • Yes: An indicator is configured
  • No/Blank: No indicator defined

Note: This field is read-only and automatically calculated.

Enhanced Totaling Type

Indicator Totaling Type

A new option in the Totaling Type dropdown: Indicator

When to Use:

  • When you need data from tables other than G/L entries
  • When standard G/L totaling doesn't provide the needed calculation
  • For KPIs based on operational data

Behavior:

  • Sets the Totaling field to "-" automatically
  • Use the lookup on Totaling, or the assist-edit (…) on Indicator Defined, to open the Define Indicator page (clicking the Totaling cell itself does nothing; the page does not open when the app is unlicensed)
  • The line uses the indicator calculation engine instead of standard logic

Formula Behavior Field

The Formula Behavior field controls how formula rows interact with formula columns.

Options:

Value Description
(blank) Standard behavior - formulas calculate normally
Row Formula x Column Formula = Zero A Formula row under a Formula column returns zero instead of the calculated value

The setting requires a registered license, like all indicator features.

Use Case: This is useful when you have:

  • A row formula like "Revenue - Cost"
  • A column formula like "Budget - Actual"
  • You don't want the intersection to calculate (it would be meaningless)

Example:

Without this setting:

              Actual    Budget    Variance (Formula)
Revenue        1000      1200         -200
Cost            600       700         -100
Profit (F)      400       500         -100  ← May be misleading

With "Row Formula x Column Formula = Zero":

              Actual    Budget    Variance (Formula)
Revenue        1000      1200         -200
Cost            600       700         -100
Profit (F)      400       500            0  ← Zero, indicating no calc

Additional System Fields

These fields are used internally by the app but may be visible in advanced scenarios:

Current Report (For API filtering)

Stores the report name when accessed via API.

Start CalcDate Formula / End CalcDate Formula

Used by the CalcDate API to pass date formulas for calculations.

Dimension Filters

Store dimension filter values passed from API or report parameters:

  • Dim1Filter
  • Dim2Filter
  • Dim3Filter
  • Dim4Filter

Working with Extensions

Adding the Company Field to Your View

If the Company field isn't visible:

  1. Open the Row Definition page
  2. Click Settings (gear icon) → Personalize
  3. Click + Field on the desired column position
  4. Select Company from the available fields
  5. Click Done

Setting Up Multi-Company Lines

Example of a multi-company profit comparison:

Row  Description           Type            Company
10   Local Revenue         Posting Accts   (blank)
20   US Revenue           Posting Accts   CRONUS USA
30   EU Revenue           Posting Accts   CRONUS EU
40   Total Revenue        Formula         (blank)
     Totaling: 10+20+30

Mixing Indicator and Standard Lines

Row  Description           Type            Company
10   G/L Revenue          Posting Accts   (blank)
20   Open Order Value     Indicator       (blank)
30   Total Pipeline       Formula         (blank)
     Totaling: 10+20

Data Flow with Extensions

When a financial report calculates with these extensions:

  1. Company Check
    • If Company specified, G/L Entry, G/L Budget Entry, Cost Budget Entry and Statistical Ledger/Analysis View Entry reads are redirected to that company
    • When the other company's LCY code differs, the standard cell value is converted using the work-date exchange rate for that LCY code in the reporting company's Currency Exchange Rate table
    • Indicator rows and formula rows are not converted
  2. Totaling Type Check
    • Standard types → Normal calculation
    • Indicator → Indicator calculation engine
  3. Formula Behavior Check
    • Applies when the row is a Formula row and the column is a Formula column
    • Returns zero when "Row Formula x Column Formula = Zero" is set
  4. Result Integration
    • Values appear in the report
    • Can be used in subsequent formula calculations

Best Practices

Company Field Usage

  1. Currency Handling: When specifying different companies, consider currency differences
  2. Performance: Cross-company queries can be slower
  3. Permissions: Users need permission in all specified companies

Indicator vs. Standard

  1. Use Standard When Possible: G/L calculations are optimized
  2. Use Indicators For: Non-G/L data, complex aggregations, external data
  3. Document Choices: Note why indicator was chosen over standard

Formula Behavior

  1. Enable Selectively: Only on formula rows where formula x formula is meaningless
  2. Document Usage: Note when and why this setting is used
  3. Review Reports: Ensure zeros are expected and clear to readers

Troubleshooting

Company Field Not Working

  • Verify the company name is spelled correctly
  • Check that you have permissions in that company
  • Ensure the G/L accounts exist in that company

Indicator Not Calculating

  • Verify the indicator is properly configured
  • Check license registration
  • Review indicator filters

Formula Behavior Not Applied

  • Confirm the field is set on the correct rows
  • The row must be a Formula row AND the column a Formula column for it to apply
  • Check that the app is licensed (the setting is ignored when unregistered)