Advanced Spreadsheets and Reporting Documentation
Index

Indicator Methods

The calculation method determines how an indicator processes records from the source table. This guide explains each method in detail.

Available Methods

Method Description Best For
Lookup Returns single value Specific record values
Sum Totals all values Aggregate amounts
Count Counts records Quantity metrics
Min Finds minimum Lowest value analysis
Max Finds maximum Highest value analysis
Average Calculates mean Distribution analysis
Custom Developer-defined Special calculations

Lookup Method

How It Works

Lookup finds the first matching record and returns the field value.

Filter → Find First Record → Return Field Value

Characteristics

  • Returns value from one record only, as a Decimal
  • Record order depends on table's primary key
  • If no records match, returns 0
  • FlowFields are calculated before returning

When to Use

  • Getting a specific value (e.g., exchange rate)
  • The filters uniquely identify one record
  • You want the "first" value by default sort

Example: Customer Credit Limit

Table: Customer
Field: Credit Limit (LCY)
Filters: No. = 'CUST001'
Method: Lookup
Result: Credit limit for customer CUST001

Example: Customer Balance

Table: Customer
Field: Balance (LCY)
Filters: No. = 'CUST001'
Method: Lookup
Result: Balance for customer CUST001

Sum Method

How It Works

Sum adds together the field values of all matching records.

Filter → CalcSum on the field → Return Total
(FlowFields: Loop Through Records → CalcField → Add Values → Return Total)

Characteristics

  • Processes all matching records
  • A database-side CalcSum is attempted first on any numeric field
  • Only FlowFields fall back to a per-record loop, calculating the FlowField for each record
  • Returns 0 if no records match

Performance Optimization

For normal (non-FlowField) numeric fields:

  • BC's built-in CalcSum is attempted first
  • Much faster than looping through records
  • Fields in a SumIndexField key are fastest
  • Only if CalcSum fails (FlowFields) does the per-record loop run

When to Use

  • Totaling amounts across many records
  • Financial aggregations
  • Any "total of" requirement

Example: Open Sales Order Value

Table: Sales Line
Field: Line Amount
Filters: Document Type = Order, Outstanding Quantity > 0
Method: Sum
Result: Total value of all open order lines

Example: Inventory Value

Table: Item Ledger Entry
Field: Cost Amount (Actual)
Filters: Entry Type = Purchase
Method: Sum
Result: Total purchase value in inventory

Count Method

How It Works

Count returns the number of records matching the filters.

Filter → Count Records → Return Number

Characteristics

  • Uses BC's built-in COUNT function
  • Very efficient
  • Field selection doesn't affect result
  • Returns 0 if no records match

When to Use

  • Number of customers, orders, etc.
  • Any "how many" question
  • KPIs based on quantities

Example: Number of Open Orders

Table: Sales Header
Field: (any field)
Filters: Document Type = Order, Status = Open
Method: Count
Result: Number of open sales orders

Example: Active Customers

Table: Customer
Field: (any field)
Filters: Blocked = ' ' (blank)
Method: Count
Result: Number of non-blocked customers

Min Method

How It Works

Min loops through records and finds the smallest value.

Filter → Loop Through Records → Track Minimum → Return Smallest

Characteristics

  • Requires a field selection
  • FlowFields are calculated for each record
  • Returns 0 if no records match
  • Only Decimal, Integer and BigInteger fields can be selected; date fields cannot be used

When to Use

  • Finding the lowest value in a set
  • Minimum prices, quantities

Example: Minimum Order Amount

Table: Sales Header
Field: Amount
Filters: Document Type = Order
Method: Min
Result: Smallest order value

Example: Lowest Stock Level

Table: Item
Field: Inventory
Filters: Inventory > 0
Method: Min
Result: Item with lowest (positive) inventory

Max Method

How It Works

Max loops through records and finds the largest value.

Filter → Loop Through Records → Track Maximum → Return Largest

Characteristics

  • Requires a field selection
  • FlowFields are calculated for each record
  • Returns 0 if no records match
  • Useful for finding peaks and maximums

When to Use

  • Finding the highest value in a set
  • Maximum orders, revenues

Example: Largest Customer Balance

Table: Customer
Field: Balance (LCY)
Filters: Blocked = ' '
Method: Max
Result: Highest customer balance

Example: Maximum Line Amount

Table: Sales Line
Field: Line Amount
Filters: Document Type = Order
Method: Max
Result: Largest single order line value

Average Method

How It Works

Average calculates the sum of values divided by the count.

Filter → Loop Through Records → Sum Values & Count → Return Sum/Count

Characteristics

  • Combines Sum and Count logic
  • FlowFields are calculated for each record
  • Returns 0 if no records match
  • Division by zero is prevented (no records = 0)

When to Use

  • Mean value calculations
  • Average order sizes
  • Performance per unit metrics

Example: Average Order Value

Table: Sales Header
Field: Amount
Filters: Document Type = Order, Status = Released
Method: Average
Result: Average value per order

Example: Average Item Cost

Table: Item
Field: Unit Cost
Filters: Type = Inventory
Method: Average
Result: Average cost per item

Custom Method

How It Works

Custom methods use developer-provided logic via an integration event.

OnCustomCalculate Event → Developer Code → Return Result

Characteristics

  • Requires AL development
  • Requires a selected table (an indicator without a table returns 0)
  • Maximum flexibility
  • Used for complex calculations
  • The indicator record has a Custom Instructions (CustomCode) field, but it is hidden on the card; use the schedule name and line number of the passed indicator record to identify which calculation to run

When to Use

  • Standard methods don't fit
  • Complex business rules
  • Multi-step calculations
  • Integration with other systems

Developer Implementation

Developers subscribe to the OnCustomCalculate event:

[EventSubscriber(ObjectType::Codeunit, Codeunit::"AFR Management Hgd", 'OnCustomCalculate', '', true, true)]
local procedure HandleCustomCalculate(
    var AccMgt: Codeunit AccSchedManagement;
    var AccLine: Record "Acc. Schedule Line";
    var ColumnLayout: Record "Column Layout";
    var SpecialCalculation: Record "Indicator Detail Hgd";
    var Result: Decimal)
begin
    // Custom calculation logic here
    // Use SpecialCalculation."Schedule Name" / "Line No." (or the row's
    // Description) to determine which calculation to run
    Result := YourCalculation();
end;

Method Comparison

Method Speed Memory Use Case
Lookup Fast Low Single values
Sum Medium* Low Totals
Count Fast Low Quantities
Min Slow Low Minimums
Max Slow Low Maximums
Average Slow Low Means
Custom Varies Varies Complex logic

*Sum is fast for normal numeric fields (database CalcSum); FlowFields are summed record by record

Performance Considerations

Fast Methods

  • Count: Uses native BC function
  • Lookup: Single record read
  • Sum on normal fields: Database-side CalcSum

Slower Methods

  • Sum on FlowFields: Loops through all records, calculating the FlowField each time
  • Min/Max/Average: Must examine every record

Optimization Tips

  1. Add Filters: Reduce the record set
  2. Prefer Normal Fields: Sum normal numeric fields rather than FlowFields where possible
  3. Limit Records: Use specific filters
  4. Test Performance: Use the Test feature