Formula Reference

Report Designer Formula Guide

Welcome to the Report Designer formula guide! This document explains how to write custom calculated columns and dynamic labels for your reports using two primary formula helpers: API (Context & Environment) and Fn (Functions & Utilities).

How Formulas Work

When building dynamic columns or calculated fields, your formulas combine three key building blocks:

Row Data (row['Column_Name']): Pulls the value from a specific field in the current data row.
Context API (API): Fetches report-level information, such as run dates or global report formatting rules.
Helper Functions (Fn): Converts values, applies logical rules, or looks up external parameters.

You can combine these components using standard operations like `+` to join text or standard math operators ( +, -, *, /) for numbers.

Context API Reference (API)

The API object provides report-level context, environment properties, execution dates, and lookup utilities.

API.look(path, data, default=None, astype=None)

Searches structured JSON/dict data using a path expression (JMESPath) and optionally coerces the output type.

Parameters:
path (str): Search expression.
data (Any): Data structure to query.
default (Any, optional): Fallback if value is missing.
astype (str, optional): Convert output to 'str', 'int', or 'float'.

API.dt(datelike, tz=None, format=None, default=None)

Converts inputs into a normalized report date object. Returns current execution time if no input is provided.

Parameters:
datelike: Input date (string, timestamp, or date object).
tz (str, optional): Timezone override.
format (str, optional): Explicit parsing format string.
default: Fallback date if main input fails to convert.

API.isoweek(shift=0, unit='days', now=None, base=None)

Calculates the ISO calendar week identifier formatted as YYYY-00-WW.

Parameters:
shift (int, optional): Time adjustment offset.
unit (str, optional): Time unit for shift ('days', 'hours').
now / base (str, optional): Starting base timestamp.

Fn Reference 1 — Type Conversion & Formatting

The Fn object provides helper functions for formula calculations.

Fn.str(a)

Converts any input into a text string.

column = Fn.str(row['account_code'])

Fn.int(a)

Converts numeric values or numeric text strings into whole integers (treating missing/blank values as 0).

column = Fn.int(row['quantity']) + 1

Fn.float(a)

Converts numeric values or strings into decimal values (treating missing/blank values as 0.0).

column = Fn.float(row['tax_rate']) * 100

Fn Reference 2 — Math & Comparisons

Fn.plus(a, b)

Adds two numbers or joins two text strings. Missing inputs default to 0.

Fn.minus(a, b)

Subtracts b from a. Missing inputs default to 0.

Fn.mult(a, b)

Multiplies two numbers or repeats a text string b times. Missing inputs default to 0.

Fn.div(a, b)

Divides a by b. Safely returns 0 if dividing by zero.

Fn.abs(a)

Returns the positive absolute magnitude of a number.

Fn.ceil(a, base=0)

Rounds a decimal up to the nearest whole number, or to the nearest multiple of base.

Fn.floor(a, base=0)

Rounds a decimal down to the nearest whole number, or to the nearest multiple of base.

Fn.round(a, b=None)

Rounds a value a to b decimal places.

Fn.min(a, b) / Fn.max(a, b)

Returns the smaller or larger value between two numbers.

Logical Comparisons

Evaluate conditions and return True or False:

Fn.eq(a, b): Equal to (a == b).
Fn.ne(a, b): Not equal to (a != b).
Fn.gt(a, b): Greater than (a > b).
Fn.lt(a, b): Less than (a < b).
Fn.ge(a, b): Greater than or equal to (a >= b).
Fn.le(a, b): Less than or equal to (a <= b).

Fn Reference 3 — Aggregations & Grouping

Key Concept: Column aggregations evaluate and summarize data across report dataset rows. Most aggregation functions accept an optional groupby parameter (e.g., groupby=['region', 'category']), which calculates totals or statistics within specific groupings or sub-categories rather than over the entire dataset.

Fn.Sum(a, groupby=None)

Calculates the total sum of all values in a column.

# Calculate total sales grouped by store region
column = Fn.Sum(row['sales_amount'], groupby=['region'])

Fn.Avg(a, groupby=None)

Calculates the arithmetic mean (average) of numerical values in a column.

# Calculate average customer rating per product category
column = Fn.Avg(row['rating'], groupby=['category'])

Fn.Min(a, groupby=None) & Fn.Max(a, groupby=None)

Finds the lowest (Min) or highest (Max) value within a column.

# Find the lowest and highest unit prices per department
min_price = Fn.Min(row['unit_price'], groupby=['department'])
max_price = Fn.Max(row['unit_price'], groupby=['department'])

Fn.Median(a, groupby=None)

Calculates the statistical middle value in a dataset distribution, reducing the impact of outliers.

# Determine median deal size by sales territory
column = Fn.Median(row['deal_size'], groupby=['territory'])

Fn.Mode(a, groupby=None)

Returns the value that appears most frequently in a column.

# Identify the most common order size per customer group
column = Fn.Mode(row['order_quantity'], groupby=['customer_group'])

Fn.Count(a, groupby=None)

Counts the total number of non-empty (non-null) rows in a column.

# Count total processed orders per branch
column = Fn.Count(row['order_id'], groupby=['branch_id'])

Fn.CountDistinct(a, groupby=None)

Counts the number of unique, non-repeating values in a column.

# Count unique active customers per country
column = Fn.CountDistinct(row['customer_id'], groupby=['country'])

Fn.StdDev(a, groupby=None)

Calculates the standard deviation to measure the amount of variation or dispersion in a set of values.

# Measure monthly revenue variance per store location
column = Fn.StdDev(row['monthly_revenue'], groupby=['store_id'])

Fn.StringConcat(a, groupby=None)

Combines multiple text string values across rows into a single list.

# Collect all order notes into a list per account
column = Fn.StringConcat(row['note'], groupby=['account_id'])

Fn.UniqueList(a, groupby=None)

Extracts only unique values across rows into a deduplicated list.

# List unique promo codes used per customer
column = Fn.UniqueList(row['promo_code'], groupby=['customer_id'])

Fn.TakeFirst(a, groupby=None)

Retrieves the first value encountered in a record set or group.

# Get the first order timestamp recorded for each user
column = Fn.TakeFirst(row['order_timestamp'], groupby=['user_id'])

Fn.CumSum(a, groupby=None)

Calculates a running cumulative sum across rows sequentially.

# Compute year-to-date running sales total per region
column = Fn.CumSum(row['daily_sales'], groupby=['region'])

Fn.Quantile(a, q)

Returns the value marking a specific percentile cutoff q (ranging from 0.0 to 1.0).

# Determine the 90th percentile threshold (0.9) for response times
column = Fn.Quantile(row['response_time_ms'], 0.9)

Fn.aggregateif(to_sum, criterion, value, aggregator='Sum')

Conditionally aggregates values in to_sum only for rows where the criterion matches the target value. Defaults to summing, but supports other aggregation types.

# Sum total revenue where the order status is explicitly set to "Completed"
column = Fn.aggregateif(
to_sum=row['revenue'],
criterion=row['order_status'],
value='Completed',
aggregator='Sum'
)

Fn Reference 4 — Special & Domain Helpers

Key Concept: Special and domain helper functions provide advanced conditional logic, data transformation pipelines, structural dataset manipulation, and complex supply chain algorithms (such as forecasting, inventory transfers, and batch aging).

Fn.use(a)

Returns the input value a completely unchanged. This acts as an explicit passthrough, commonly used when injecting dynamic context variables or standard corporate rule macros into report logic.

# Pass through a dynamic global target variable without modification
column = Fn.use(row['global_tax_rate'])

Fn.when(test, true_value, false_value)

Evaluates a logical test condition. Returns true_value if the condition is True, and false_value if it evaluates to False.

# Classify customer tier based on sales volume
column = Fn.when(row['sales'] > 10000, "Enterprise", "Standard")

Fn.rename(a, mapper, default=None)

Maps an input value a to a new label using a translation dictionary (mapper). If a is not found in the dictionary, it falls back to the default value.

Parameters:
a: Input key to look up.
mapper (dict): Key-value lookup map.
default (Any, optional): Fallback if key is missing.

# Map raw warehouse codes to human-readable names
warehouse_map = {'WH_01': 'East Coast Hub', 'WH_02': 'West Coast Hub'}
column = Fn.rename(row['wh_code'], warehouse_map, default='Unknown Facility')

Fn.look(expression, data, default=None)

Queries complex, nested dictionary or JSON data structures using a JMESPath path expression.

# Extract the primary email address from a nested metadata payload
column = Fn.look('contact.emails[0]', row['customer_payload'], default='N/A')

Fn.timediff(a, b)

Calculates the duration or time difference between two date/timestamp inputs a and b.

# Calculate processing time between order placement and fulfillment
column = Fn.timediff(row['fulfilled_at'], row['ordered_at'])

Fn.zip(a, b, mode='dict')

Combines two equal-length lists a and b together. Generates a dictionary when mode='dict' or a list of paired tuples when mode='tuple'.

# Pair product keys with their corresponding stock quantities into a dictionary
column = Fn.zip(row['sku_list'], row['qty_list'], mode='dict')

Fn.listreplace(items, mapper)

Iterates through a list of items and replaces any element matching a key in the mapper dictionary.

# Standardize status tags in a list
status_map = {'PEND': 'Pending', 'APPR': 'Approved'}
column = Fn.listreplace(row['status_tags'], status_map)

Fn.pairstomap(pairs, key=0, value=1)

Converts a collection of paired tuples or nested lists into a standard dictionary structure, specifying which positions represent the dictionary key and value.

# Convert a list of (attribute_name, value) pairs into a dictionary
column = Fn.pairstomap(row['attribute_pairs'], key=0, value=1)

Fn.find_by_ref(key, key_list, value_list, default=0)

Locates the index position of key inside key_list, then extracts and returns the corresponding value from value_list at that same position.

# Look up tier discount percentage matching the account tier
column = Fn.find_by_ref(row['tier'], ['Bronze', 'Silver', 'Gold'], [0.05, 0.10, 0.20], default=0)

Fn.sort_by_ref(ref_list, key_list, values, default_value=None)

Reorders elements in values based on a reference list (ref_list) alignment order.

# Sort current store values according to custom region priority order
column = Fn.sort_by_ref(['North', 'South', 'West'], row['region_keys'], row['region_values'])

Fn.find_dict(data_list, key, default=None)

Searches through a list of dictionaries (data_list) and returns the first dictionary that contains the specified key.

# Find the config block containing tax rules from a list of configuration objects
column = Fn.find_dict(row['config_blocks'], key='tax_rates', default={})

Fn.forward_need(cover_weeks, forecasts)

Calculates total required inventory purchases by evaluating future demand across array elements in forecasts to ensure enough stock to cover cover_weeks of forward sales.

# Calculate unit purchasing requirement to cover 6 weeks of demand
column = Fn.forward_need(6.0, row['monthly_forecasts'])

Fn.transfers(...)

Generates inventory replenishment and rebalancing transfer recommendations between distribution centers (DCs) and receiving store locations. Balances stock based on current inventory, minimum safety stock thresholds, and demand demand signals.

Parameters:
col_product: Product or SKU identifier column.
col_location: Destination location identifier column.
col_locations: Available source warehouse/DC identifier column.
col_levels: Source stock availability levels.
col_need: Target destination shortfall/need unit quantity.

column = Fn.transfers(
col_product='sku_id',
col_location='store_id',
col_locations='source_dcs',
col_levels='dc_stock_levels',
col_need='shortfall_units'
)

Fn.forecast(...)

Generates predictive time-series estimations using historic value trends aligned over calendar dates or ISO weeks.

Parameters:
values: Historical performance values list.
weeks: Corresponding historical week identifiers list.
length: Number of future periods to predict.
single_value (bool): When True, returns single aggregated projection instead of array.

# Predict next 4 weeks of unit sales based on historical weekly sales data
column = Fn.forecast(
values=row['history_sales'],
weeks=row['history_weeks'],
length=4,
single_value=True
)

Fn.batch_aging(...) & Fn.batch_aging_loc(...)

Computes stock aging distributions, lot depletion tracking (FIFO/LIFO), and batch movement between stock locations.

# Track inventory age distribution across active storage lots
column = Fn.batch_aging(
batches=row['lot_list'],
quantities=row['lot_qty'],
dates=row['received_dates']
)

Structural & Dataset Mutators Reference

Key Concept: Structural and dataset mutators modify the shape, order, layout, and filtering of report datasets. Unlike scalar functions that operate cell-by-cell, these functions transform entire dataset tables—enabling operations such as filtering, sorting, subtotal injection, row expansion, and rolling window calculations.

Fn.Sort(key, asc=True)

Re-orders the entire dataset rows based on a specified column or key.

Parameters:
key (str): The column name or field to sort by.
asc (bool, optional): Set to True for ascending order (A–Z, 0–9), or False for descending order. Default is True.

# Sort the report dataset by transaction date in descending order (newest first)
dataset = Fn.Sort('transaction_date', asc=False)

Fn.Reduce(by, reducer='Count', reducers=None)

Groups the dataset by specific dimensions and reduces the rows into aggregated summary metrics.

Parameters:
by (str | list): Column name(s) to group by.
reducer (str, optional): Default aggregation method (e.g., 'Sum', 'Count', 'Avg') applied across columns. Default is 'Count'.
reducers (dict, optional): Map specifying different aggregation operations per column (e.g., {'sales': 'Sum', 'qty': 'Avg'}).

# Group dataset by region and calculate total sales and average margin
dataset = Fn.Reduce(
by=['region'],
reducers={'sales': 'Sum', 'margin_pct': 'Avg'}
)

Fn.Where(query, default=None)

Filters dataset rows, returning only the records that meet the specified conditional logical query expression.

Parameters:
query (str): Conditional query template expression.
default (Any, optional): Fallback value to return if no records match the criteria.

# Filter the report to only include active orders above $500
dataset = Fn.Where("status == 'Active' and total_amount > 500")

Fn.AggregateWhere(a, query, aggregator='Sum')

Filters the dataset using a target expression, then performs an aggregation over the matching subset of rows.

Parameters:
a (str): Column name to aggregate.
query (str): Conditional filtering expression.
aggregator (str, optional): Aggregation function to run ('Sum', 'Avg', 'Count', etc.). Default is 'Sum'.

# Calculate total sales specifically for closed deals in the EU region
closed_sales = Fn.AggregateWhere(
a='sales_amount',
query="status == 'Closed' and region == 'EU'",
aggregator='Sum'
)

Fn.Subtotal(on, aggregators, label='Subtotal')

Injects summary subtotal calculation rows into the dataset whenever the values in the grouping column(s) change.

Parameters:
on (str | list): Dimension column name(s) to monitor for subtotal grouping boundaries.
aggregators (dict): Dictionary mapping column names to their intended summary aggregation methods.
label (str, optional): Label prefix inserted into the grouping column on summary rows. Default is 'Subtotal'.

# Insert a subtotal row at each region change, summing sales and quantities
dataset = Fn.Subtotal(
on='region',
aggregators={'sales': 'Sum', 'quantity': 'Sum'},
label='Region Total'
)

Fn.Unpack(column, sep=None, missing=None)

Expands (unrolls) delimited strings or list elements in a specified column into individual, standalone report rows.

Parameters:
column (str): Name of the column containing lists or delimited strings.
sep (str, optional): Delimiter character (e.g., ',', ';') if the target column is a single string.
missing (Any, optional): Value to assign if the column is empty or missing.

# Expand comma-separated tag strings into individual dataset rows
dataset = Fn.Unpack('product_tags', sep=',')

Fn.Take(take, skip=0)

Extracts a specific window or range of rows from the report dataset, ideal for pagination or subset sampling.

Parameters:
take (int): Maximum number of rows to return.
skip (int, optional): Number of leading rows to skip. Default is 0.

# Get the first page of results (rows 1 to 50)
dataset = Fn.Take(take=50, skip=0)

Fn.AddRows(rows, fields=None)

Appends new blank or pre-filled rows to the existing dataset.

Parameters:
rows (int | list): Number of empty rows to append, or a list of row dictionaries to add.
fields (dict, optional): Default column values to populate in newly generated rows.

# Pad the dataset with 3 extra empty placeholder rows
dataset = Fn.AddRows(3, fields={'status': 'Pending Verification'})

Fn.RollingSum(column, window, groupby=None)

Computes a moving cumulative sum over a defined window of contiguous rows.

Parameters:
column (str): Column name containing numeric values to accumulate.
window (int): Number of preceding rows to include in the rolling calculation.
groupby (str | list, optional): Column(s) to partition the rolling window calculations across.

# Calculate a 7-row rolling total of daily revenue grouped by store
column = Fn.RollingSum('daily_revenue', window=7, groupby=['store_id'])

Fn.enrich_rows(rows, values)

Applies structural metadata or supplemental key-value pairs across a collection of row objects.

Parameters:
rows (list): Target dataset row objects to enrich.
values (dict): Dictionary of key-value attributes to append to each row.

# Enrich output rows with source metadata attributes
enriched_data = Fn.enrich_rows(dataset_rows, {'data_source': 'ERP_DB', 'batch_id': 1042})

Fn.rows_subtable(rows, names, mode='records')

Converts nested arrays, raw row collections, or records into formatted, structured sub-tables suitable for reporting layout rendering.

Parameters:
rows (list): Collection of input records.
names (list): Column header titles/field keys to extract into the sub-table.
mode (str, optional): Output structure format ('records', 'tuples'). Default is 'records'.

# Convert line item records into a structured sub-table
subtable = Fn.rows_subtable(row['line_items'], names=['item_id', 'qty', 'price'])