Aggregation is an operation that combines values from multiple records into a single value.

Examples of aggregation include taking an average of multiple numerical values or joining multiple text values into a single text string.

Special Aggregate Functions can be used within Template expressions to perform such operations.

Within Template expressions, aggregation is performed on a set of child records belonging to some parent Group. Aggregation is required anytime you need to write an expression that potentially references multiple child records.

Aggregation is performed slightly differently within Model Calculation expressions. Please see the Aggregation in Model Calculations guide for more information.

For general information on OpenGround expressions, please see our Expressions Guide.

Aggregation Example

The concept of Aggregation is easiest to demonstrate with an example.

We want to build a list of all Loggers for a Location. There could be multiple Loggers for each Location stored as individual records in the DepthReleatedExploratoryInformation Group (in case you want to record who logged different portions of a hole), as we see here for BH2:

Since we want to summarize data stored across multiple records, we will need to use an Aggregate Function.

This expression uses joinDistinct() to build the list of unique Loggers from the DepthRelatedExploratoryInformation Group.

joinDistinct([Aggregate.DepthRelatedExploratoryInformation.Logger], '', '+')

See the examples below for additional use cases.

Aggregate Function Syntax & Usage

The ‘Aggregate’ or ‘AggregateAll’ prefix is required when referencing a Group/Header within an aggregate function:

[Aggregate.Group.Header]

[AggregateAll.Group.Header]

For example, note the use of ‘Aggregate’ in this expression from the example above:

joinDistinct([Aggregate.DepthRelatedExploratoryInformation.Logger], '', '+')

Aggregate vs. AggregateAll

‘AggregateAll’ will perform the operation on all child records for a Group. For example, if you wanted to find an average() of Moisture Contents for the Sample Information group, ‘AggregateAll’ could be used to find the average of all Moisture Contents for all Samples.

‘Aggregate’ can be used to perform the operation on the child records for a single parent record. Using the same scenario as above, ‘Aggregate’ could be used to find the average of Moisture Contents for each Sample individually.

The determination of what ‘set’ of records will be used, depends on which Group the expression is executed from. In some scenarios Aggregate and AggregateAll will yield the same results.

This distinction is easier to demonstrate with some examples.

Aggregate and Aggregate All Yielding Same Results

In this first example, we will use joinDistinct() to make a list of PitLengths from the DepthRelatedExploratoryInformation Group for each Location using the expressions:

joinDistinct([Aggregate.DepthRelatedExploratoryInformation.PitLength], 'F2', '+')

and

joinDistinct([AggregateAll.DepthRelatedExploratoryInformation.PitLength], 'F2', '+')

To illustrate this, we’ve set up a Borehole Log template showing the results for the expression when using Aggregate vs. AggregateAll. The template also shows the results when the same expressions are executed from different Groups – in this case the child Depth Related Exploratory Hole Information Group vs. the parent Location Details Group.

In all these scenarios, Aggregate and AggregateAll will yield the same results.

In the left-hand panel, we see that we have 2 Test Pits – TP1 & TP2. TP1 has 2 Pit Length values recorded and TP2 has 3 Pit Length values recorded.

In the right-hand panels, we see the results of the various expressions for both TP1 & TP2. Since this is a Borehole Log template, the expressions are evaluated a single Location at a time so we get different results for Location TP1 vs. TP2. But the results are the same for all scenarios for each Location.

This will generally be the case when using aggregate functions on direct child tables of Location Details in a Borehole Log or Header Sheet template where the reports are output one Location at a time. (See notes below on differences in other types of templates like Project Sheets). The parent is the Location, so the results will be from across the entire Location.

Aggregate and AggregateAll Yielding Different Results

The Aggregate vs. AggregateAll behavior will be different in other scenarios depending on the parent-child relationships of the tables from which you are calling the functions. This is probably easiest to show with a different example.

In this example, we’ll use the max() function to find a maximum ParticleSize value from the ParticleSizeDistributionData (PSD-Data) Group with the following expressions:

max([AggregateAll.ParticleSizeDistributionData.ParticleSize])

and

max([Aggregate.ParticleSizeDistributionData.ParticleSize])

In the first scenario, we’ll call this function from the ParticleSizeDistributionGeneral (PSD-General) Group.

The left column of the report uses AggregateAll, which looks across all the records in the PSD-Data child table. So, you get the same results for all of the parent records.

The right column uses Aggregate, which looks at only the records for each parent record. So, you have parents at 0 and 1.5 ft in PSD-General, and you get different results at each depth.

If we change the Group to be PSD-Data, both expressions return the same results because they are now both aggregating across all records in the PSD-Data Group, rather than filtering by the parent records from PSD-General (and also return a value for each PSD-Data record instead of just the parent records):

Aggregation By Location vs. By Project (Borehole Logs vs. Project Sheets)

Aggregation behavior will be slightly different depending on the type of Template. For example, in a Project Sheet, aggregation can be performed across the entire Project, while in Borehole Logs and Header Sheets, aggregation is performed one Location at a time.

The general concepts are the same when deciding between Aggregate and AggregateAll within a Project Sheet in that you need to consider the parent-child relationships between the Group for which the expression is being evaluated and the Group used in the aggregate function.

(See the Filtering section below for an example of aggregation in a Project Sheet.)

Conclusions

In summary, 'Aggregate' will aggregate by individual parent records and 'AggregateAll' will aggregate across all records in a child Group. If you are not using them in a parent/child scenario, Aggregate and AggregateAll generally work the same – i.e. if the Group and Expression reference the same Group, or you're working with a direct child table of LocationDetails on a Borehole Log template. In that case, they both look across all records in the Group and should return the same result. (There are also some edge case scenarios where Aggregate might not return anything, in which case you can just use AggregateAll. This would typically occur in situations where aggregating by parent records is not relevant.)

If in doubt about using Aggregate vs. AggregateAll, we recommend testing both to confirm the desired behavior. There are some edge case scenarios, so it's a good idea to sufficiently test such expressions.

Using Filter Groups to Sort & Filter

Filtering

It is possible to use Filter Groups to filter within an aggregate expression. This is easiest to show with an example.

In this example, we'll use a Project Sheet. On Project Sheets, it is possible to aggregate across all Locations in a Project (whereas a Borehole Log or Header Sheet will perform the aggregation within the single Location.)

We'll use an aggregate expression to count all the Samples in the Project. And then we'll use an aggregate expression to count just the Rock Core (RC) Samples in the Project.

To count all the Samples, we'll use the count() function and this expression:

count([AggregateAll.SampleInformation.Type])

To count just the 'RC' Samples, we'll first need to create a Filter Group:

Then we can use the expression below.

Important - Note that the Filter Group name 'RCs' is used in the expression in place of the actual Group name 'StratumDetails'. It is not sufficient just to select the Filter Group in the dropdown.

(In fact, you don't actually need to select the Filter Group in the dropdown. You really only need to use the Filter Group name in the expression. This is important to be aware of because you can only select Filter Groups for the selected Group, but your expression might be referencing a Child Group - so you wouldn't be able to select the Filter Group. The Filter Group will still work in the expression even though you can't select it in the dropdown in that scenario.)

count([AggregateAll.RCs.Type])

If we look at the data in the Project, these counts agree with what we expect. The Sample Information grid contains a total of 35 records. When we filter Sample Type for 'RC' Samples, it contains 4 records:

Sorting

For some aggregate expressions, the order in which the child records are aggregated is important. This applies to list-building functions like join() or joinDistinct(), as well as functions like first() or last().

In these scenarios, the default ordering of child records may not be what you expect. In which case, Filter Groups can be used to sort the records as desired. This is easiest shown with an example.

In this example, we'll create a 'list' of Descriptions for a Location in a Header Sheet the join() function.

First, we'll use this expression:

join([AggregateAll.StratumDetails.DescriptionByDepth],'','\n')

But when we look at the results, we see that the Descriptions are out of order, rather than sorted by depth as we would hope:

To correct this, we'll first create a Filter Group.

For this Filter Group, we won't do any Filtering so we'll leave the 'Filters' tab blank, but we will use the 'Grouping' tab. Specifically, we'll use the Group By Records option, the List option, and sort Ascending by Depth Top. We don't need to change any other default settings:

Then we can use this expression. Note that as with the Filtering example above, it is important to use the Filter Group name 'Description_Sorted' in the expression rather than the Group name 'StratumDetails'. Then, we get the Descriptions sorted by depth as desired:

join([AggregateAll.Description_Sorted.DescriptionByDepth],'','\n')

(For additional context, this example uses a custom header named 'DescriptionByDepth' that uses a Model Calculation to concatenate the Depth Top/Base to the Description using this expression:)

concatenatewith(
  ': ',
  concatenatewith(' - ',text(DepthTop,'0.00'),text(DepthBase,'0.00')),
  Description
)

Additional Examples

Aggregate Expressions in 'Hide Conditions'

Hide Conditions can be used to conditionally hide Strips.

Often these Hide Conditions will be set up to hide the Strip if there is no relevant data  for the current Location.

This example is from the 'Strip - Fines Content - NAM' strip template in our North America Metric configuration pack. The expression uses the count() function and hides the strip if the count of PercentageFines values is 0.

count([AggregateAll.ParticleSizeDistributionGeneral.PercentageFines]) = 0

Filter Groups can be used in Hide Condition expressions. For example, in the 'Strip - PID - NAM' strip template in our North America Metric configuration pack, a Filter Group is used to filter the EnvironmentalData group for just PID records, and then this Filter Group is referenced in the Hide Condition using the expression below:

(count([AggregateAll.PID_PPM.Result])=0) and (count([AggregateAll.PID_PPM.Concentration])=0)

Lab Test Results By Sample

This example is from the ‘Strip – Lab Results Concatenated – NAM’ strip template in our North America Metric configuration pack. It uses a more complex aggregate expression to summarize certain lab test results for each Sample.

The expression is executed from the SampleInformation Group and uses the Aggregate prefix to perform the aggregation for each Sample individually.

It uses the count() function for multiple lab test Groups and adds up the results. If the total count is not 0, it means that there are lab test results for that Sample, in which case it uses concatenatewith() to build a list of the available lab test results.

For the individual lab tests, if the count of results is not 0, it prints a test prefix such as ‘MC=’ along with the results. joinDistinct() is used to build a list of the results for each test in the event a test was performed multiple times on the same Sample.

if(
  count([Aggregate.MoistureContent.MoistureContent])
  +count([Aggregate.ParticleSizeDistributionGeneral.PercentageFines])
  +count([Aggregate.LiquidAndPlasticLimit.PlasticLimit])
  +count([Aggregate.LiquidAndPlasticLimit.PlasticLimitValue])
  +count([Aggregate.LiquidAndPlasticLimit.LiquidLimitText])
  +count([Aggregate.LiquidAndPlasticLimit.LiquidLimit])=0,
  '',
  [SampleInformation.SampleReference]+': '
)
+
concatenatewith(
  ', ',
  if(
    count([Aggregate.MoistureContent.MoistureContent])=0,
    '',
    'MC='+joinDistinct([Aggregate.MoistureContent.MoistureContent],'F1','/')+'%'
  ),
  if(
    count([Aggregate.ParticleSizeDistributionGeneral.PercentageFines])=0,
    '',
    '-200='+joinDistinct([Aggregate.ParticleSizeDistributionGeneral.PercentageFines],'0','/')+'%'
  ),
  if(
    count([Aggregate.LiquidAndPlasticLimit.PlasticLimit])=0,
    if(
      count([Aggregate.LiquidAndPlasticLimit.PlasticLimitValue])=0,
   '',
      'PL='+joinDistinct([Aggregate.LiquidAndPlasticLimit.PlasticLimitValue],'0','/')+'%'
    ),
    'PL='+joinDistinct([Aggregate.LiquidAndPlasticLimit.PlasticLimit],'0','/')
  ),
  if(
    count([Aggregate.LiquidAndPlasticLimit.LiquidLimitText])=0,
    if(
      count([Aggregate.LiquidAndPlasticLimit.LiquidLimit])=0,
   '',
      'LL='+joinDistinct([Aggregate.LiquidAndPlasticLimit.LiquidLimit],'0','/')+'%'
    ),
    'LL='+joinDistinct([Aggregate.LiquidAndPlasticLimit.LiquidLimitText],'0','/')+'%'
  )
)