Advanced Spreadsheets and Reporting Documentation
Index

Formula Behavior

Advanced Spreadsheets and Reporting introduces a Formula Behavior setting that controls how formula rows interact with formula columns. This guide explains when and how to use this feature.

Understanding the Challenge

In standard financial reports, when both a row and a column are formulas, Business Central calculates both:

  1. Each cell in the formula row is calculated
  2. Then the formula column calculates across rows

This can lead to unexpected or meaningless results in certain scenarios.

The Formula × Formula Problem

Example Scenario

Consider this financial report:

Row Definition:

Row 10: Revenue (G/L Accounts)
Row 20: Cost (G/L Accounts)
Row 30: Profit (Formula: 10-20)

Column Definition:

Col A: Actual (Net Change)
Col B: Budget (Budget Entries)
Col C: Variance (Formula: A-B)

Standard Result:

Actual (A) Budget (B) Variance (C)
Revenue 1,000 1,200 -200
Cost 600 700 -100
Profit 400 500 -100

The intersection of Row 30 (Profit formula) and Column C (Variance formula) calculates as:

  • Actual Profit (400) - Budget Profit (500) = -100

When This Is a Problem

This result might be:

  • Correct and desired: You want to see profit variance
  • Misleading: The "double formula" intersection doesn't represent meaningful data
  • Confusing: Users may misinterpret what the number means

Formula Behavior Setting

Location

The Formula Behavior field is on the Row Definition line:

  1. Open your Row Definition
  2. Find the Formula Behavior column (add it via personalization if needed)
  3. Set the value on formula rows

Options

Setting Behavior
(blank) Normal calculation - formulas calculate as usual
Row Formula x Column Formula = Zero A Formula row under a Formula column returns zero

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

When to Use Each Setting

Keep Default (Blank)

Use the default when:

  • You want formula intersections to calculate
  • The result is meaningful (e.g., profit variance)
  • Users understand what the intersection represents

Use "Row Formula x Column Formula = Zero"

Use this setting when:

  • The intersection would be meaningless
  • You want to prevent misleading numbers
  • The intersection could be misinterpreted

Common Use Cases for Zero Setting

  1. Percentage Rows with Variance Columns
    • A percentage row (Row A / Row B × 100)
    • Combined with a variance column
    • Results in a non-intuitive number
  2. Ratio Calculations
    • Rows calculating ratios (A/B)
    • With columns comparing periods
    • The ratio of variances often isn't meaningful
  3. Complex Formulas
    • Multi-step formula rows
    • With formula columns that aggregate
    • Results can be confusing

Implementation Examples

Example 1: Prevent Percentage Variance

Setup:

Row 10: Revenue
Row 20: Gross Profit
Row 30: GP Margin (Formula: 20/10*100, Formula Behavior: Row×Col=Zero)

Column Definition:

Col A: Current Period
Col B: Prior Period
Col C: Change (Formula: A-B)

Result:

Current Prior Change
Revenue 1,000 900 100
GP 400 350 50
GP Margin 40% 38.9% 0

The GP Margin × Change cell shows 0 instead of a potentially confusing calculation.

Example 2: Standard Behavior (No Change)

Setup:

Row 10: Revenue
Row 20: Cost
Row 30: Profit (Formula: 10-20, Formula Behavior: blank)

Result: Profit variance calculates normally, showing meaningful profit variance data.

Setting Up Formula Behavior

Step-by-Step

  1. Open Financial Reports
  2. Select your report and Edit Row Definition
  3. Locate your formula rows
  4. In the Formula Behavior column:
    • Leave blank for normal calculation
    • Select "Row Formula x Column Formula = Zero" to return zero

Applying to Multiple Rows

You can set Formula Behavior on multiple rows:

  1. Each row is evaluated independently
  2. Only affects rows where it's set
  3. Non-formula rows are unaffected regardless of setting

Interaction with Indicators

Formula Behavior also works with indicator rows that are used in formulas:

Example:

Row 10: G/L Revenue (Posting Accounts)
Row 20: Open Orders (Indicator)
Row 30: Pipeline (Formula: 10+20, Formula Behavior: Row×Col=Zero)

If Column C is a formula, the Pipeline × Column C cell returns zero.

Technical Details

How It Works

When Business Central calculates a cell:

  1. The standard engine calculates the cell
  2. The app checks whether the row totaling type is Formula and the column type is Formula
  3. If both AND Formula Behavior is set (and the app is licensed), the result is replaced with 0
  4. Otherwise, the calculated value is kept

Performance Impact

The check is negligible; the setting has no measurable effect on report performance.

Best Practices

Documentation

  1. Comment Your Choices: Note why certain rows have this setting
  2. Be Consistent: Apply the same logic across similar reports
  3. Review Periodically: Ensure the setting still makes sense

User Communication

  1. Explain Zeros: Users may wonder why cells show zero
  2. Add Notes: Consider adding report notes explaining the behavior
  3. Train Users: Ensure report readers understand the design

Design Guidelines

  1. Analyze Each Formula Row: Decide if intersection is meaningful
  2. Consider All Columns: Check all formula columns in the layout
  3. Test Thoroughly: Verify results match expectations

Troubleshooting

Unexpected Zeros

If you see zeros where you expected values:

  • Check if Formula Behavior is set
  • Verify the row and column are both formulas
  • Clear the setting if calculation is desired

Setting Not Applied

If formula × formula still calculates:

  • Verify the setting is saved
  • Confirm the row is a Formula row AND the column is a Formula column
  • Check that the app is licensed
  • Check that you're looking at the correct row

Mixed Results

If some formula intersections calculate and others don't:

  • This is expected if only some rows have the setting
  • Review each row's Formula Behavior configuration