Workday’s Aggregate Related Instances (ARI) function does not add numbers, count records, or compute averages: it gathers related object instances across a relationship, strips out duplicates, and returns an alphabetical multi-instance object list. To calculate numeric totals, counts, distinct counts, or averages over multi-instance rows, use Sum Related Instances and Count Related Instances directly for one-hop relationships, or place Aggregate Related Instances underneath them when bridging a two-hop object path.
ARI vs. Sum Related Instances vs. Count Related Instances
Report writers familiar with SQL SUM(), COUNT(), and AVG() routinely open Create Calculated Field, search for “Aggregate,” and pick Aggregate Related Instances expecting an aggregation dropdown. Instead, Workday prompts for Source Field, an optional Condition, and Fields to Aggregate (which accepts only single-instance or multi-instance object fields, never numbers or currencies).
Each multi-instance rollup requirement maps to a distinct function in Workday (all secured under the Custom Field Management domain in the System functional area):
| Calculated Field Function | Return Field Type | Deduplicates Automatically? | What It Actually Does |
|---|---|---|---|
| Calculated Field FunctionAggregate Related Instances (ARI) | Return Field TypeMulti-Instance (object references) |
Deduplicates Automatically?Yes, excludes duplicate instances and sorts alphabetically | What It Actually DoesCollects all related object instances from Fields to Aggregate across every item in Source Field (for example, all unique Job Profile instances across all Positions in an Organization) |
| Calculated Field FunctionSum Related Instances (SRI) | Return Field TypeNumeric or Currency |
Deduplicates Automatically?Sums every matching instance | What It Actually DoesTotals a numeric or currency Field to Sum across All instances or Instances where condition is true on a related business object |
| Calculated Field FunctionCount Related Instances (CRI) | Return Field TypeNumeric (integer) |
Deduplicates Automatically?Counts every instance in the target field (deduplicated only if fed by ARI) | What It Actually DoesCounts how many instances exist in Related Field across All instances or Instances where condition is true |
| Calculated Field FunctionExtract Multi-Instance (EMI) | Return Field TypeMulti-Instance (object references) |
Deduplicates Automatically?Depends on Subset, Intersection, Except, or Union operation | What It Actually DoesFilters a multi-instance field on a single hop by a Boolean condition or compares two multi-instance fields on the same business object |
If your report can use a Matrix layout instead of row-level calculated fields on an Advanced report, compare this approach with native Workday Matrix report aggregations and drilldowns, which summarize indexed data sources faster at scale.
How to Sum or Count One-Hop Multi-Instance Rows Correctly
When the rows you want to total or count sit directly on a related business object of your Primary Business Object (PBO), such as Expense Report Lines on Expense Report, Dependents on Worker, or Supplier Invoice Lines on Supplier Invoice, you do not need Aggregate Related Instances at all.
First, inspect the relationship on the Business Object Details report to confirm the exact multi-instance field linking your PBO to the Related Business Object (RBO) and note the RBO’s name. If you only want to sum or count a subset of rows (for example, taxable invoice lines, meal expense items, or involuntary terminations), create the True/False Condition calculated field on the Related Business Object, not on the PBO:
# Step 1: Filter condition on the Related Business Object
Task: Create Calculated Field
Field Name: CF_TF_Is_Meal_Expense_Line
Business Object: Expense Report Line # Must be the RBO, NOT Expense Report
Function: True/False Condition
Condition: Expense Item in the selection list (Meals, Business Meals)
# Step 2: Conditional sum on the Primary Business Object
Field Name: CF_SRI_Total_Meal_Lines_Amount
Business Object: Expense Report
Function: Sum Related Instances
Related Field: Expense Report Lines
Sum: Instances where condition is true
Condition: CF_TF_Is_Meal_Expense_Line
Field to Sum: Converted Amount (or Amount in Corporate Currency)Mixed currencies break Sum Related Instances
When Field to Sum is a Currency field, every summed instance must use the same currency code or Workday displays an error. On multi-currency objects like global expense lines or worker benefit costs, first build a Convert Currency calculated field on the RBO (backed by rates in Maintain Currency Conversion Rates), then select that converted field as your Field to Sum.
How to Compute an Average Over Multi-Instance Rows
Workday does not ship an Average Related Instances calculated field function. Whenever you need an instance-level average on a PBO (such as average performance review score per organization, or average meal line amount per expense report for a business process approval rule), combine three fields:
- Numerator (
Sum Related Instances): Sum the target numeric or currency field across the RBO. If rows with a blank or zero score should be excluded from the average, select Instances where condition is true and apply a True/False Condition on the RBO that excludes unrated or ineligible rows. - Denominator (
Count Related Instances): Count instances on the exact same Related Field using the exact same Boolean condition. UsingAll instanceson the count while filtering the sum skews the average downward whenever excluded rows exist. - Quotient (
Arithmetic Calculation): Divide the Sum Related Instances field by the Count Related Instances field, and check Return Zero on Error on the Calculation tab. Without Return Zero on Error, any worker or organization with zero matching child instances triggers a divide-by-zero runtime error instead of returning0.
Keep in mind that numeric calculated fields display up to 6 decimal places in Workday, while currency calculated fields follow the ISO decimal precision of the currency code (such as 2 decimal places for USD).
When You Actually Need Aggregate Related Instances: Two-Hop Rollups and Distinct Counts
Aggregate Related Instances becomes essential in two scenarios where Sum Related Instances and Count Related Instances cannot reach the target data on their own:
1. Bridging a Two-Hop (1:M to 1:M) Relationship
Suppose your report PBO is Organization (or Supervisory Organization), which has a multi-instance Positions field, and each Position has a Job Profile (or multi-instance Workers who each have multi-instance Dependents or Time Off Entries). Because Count Related Instances and Sum Related Instances only traverse a single Related Field hop from the PBO, they cannot jump directly from Organization to Job Profile or grandchild lines.
Build an Aggregate Related Instances field on the PBO first to collect the grandchild instances into a single multi-instance field on the PBO, then point Count Related Instances or Sum Related Instances at that ARI field:
# Hop 1 + 2 flattened onto Organization via Aggregate Related Instances
Field Name: CF_ARI_Org_Job_Profiles
Business Object: Organization
Function: Aggregate Related Instances
Source Field: Positions (or Active Filled Positions)
Condition: (Optional Boolean on Position, e.g., Is Open Position = False)
Fields to Aggregate: Job Profile
# Distinct count of Job Profiles in that Organization
Field Name: CF_CRI_Distinct_Job_Profiles_In_Org
Business Object: Organization
Function: Count Related Instances
Related Field: CF_ARI_Org_Job_Profiles
Count: All instances2. Counting Distinct Child Values Versus Total Child Rows
Because Aggregate Related Instances automatically deduplicates repeated object instances, wrapping Count Related Instances around an ARI field gives you an exact COUNT(DISTINCT) on an Advanced report. In the configuration above, if an organization has 40 filled positions sharing 6 job profiles, counting Positions directly returns 40, whereas counting CF_ARI_Org_Job_Profiles returns 6.
By the same rule, be careful when feeding an ARI field into Sum Related Instances. If two child records reference the exact same shared object instance, ARI collapses that duplicate instance to one entry before Sum Related Instances adds it up. Only feed ARI into Sum Related Instances when each grandchild transaction is a distinct business object instance (such as unique Expense Report or Payroll Result events across workers), never when multiple rows point to a shared lookup record.
Verifying Security and Row Totals Before Deploying
Both Sum Related Instances and Count Related Instances enforce Workday’s instance-level domain security silently: Workday sums or counts only the related instances the running user has permission to view. If an HR Partner or Integration System User lacks domain access to compensation, absence, or certain organizations, the field does not error out; it simply returns a smaller total or 0 (see why calculated fields return blank or unexpected values).
To verify your rollup end to end:
- Run View Security for Securable Item on the underlying Workday-delivered fields to confirm every target persona or Integration System User has access to the domains that secure them.
- Build a quick audit Advanced report on the PBO showing the raw multi-instance Related Field alongside your ARI, Count Related Instances, and Sum Related Instances columns for 5 to 10 known records (including one record with zero child rows and one with duplicate child values).
- Test the report as the actual end-user persona or integration account rather than relying solely on an unconstrained administrator view.
When a rollup spans unfamiliar business objects and you do not want to hunt through Business Object Details by trial and error, Kiweely Vine inspects your live tenant metadata, maps the exact one-to-many object path between your report’s PBO and target fields, and builds the ARI, SRI, and CRI chain with your approval. See how Kiweely Vine works against live Workday tenants.
Frequently asked questions
Why does Aggregate Related Instances not let me pick a numeric or currency field to sum?
In Workday, Aggregate Related Instances is an instance-retrieval function, not a math function. It collects single-instance or multi-instance object references across a related business object, removes duplicates, and outputs an alphabetical multi-instance list. To add numbers or currencies, select Sum Related Instances instead.
How do I calculate an average across multi-instance rows on a Worker or Organization?
Workday has no standalone Average Related Instances calculated field function. Build one Sum Related Instances field and one Count Related Instances field using the exact same Boolean condition on the related business object, then divide Sum by Count in an Arithmetic Calculation field with Return Zero on Error enabled.
Why do two users running the same report see different Sum Related Instances or Count Related Instances totals?
Both Sum Related Instances and Count Related Instances evaluate security per row and include only the related instances the running user has permission to view. Check the domains securing the underlying fields with View Security for Securable Item.
How do I count distinct values across a one-to-many relationship in an Advanced report?
Create an Aggregate Related Instances calculated field pointing to the target object field, which automatically strips duplicate instances from the returned list. Then build a Count Related Instances calculated field referencing that Aggregate Related Instances field and selecting All instances.