Using Group Aggregate Expressions in Finished Reports

Group aggregate expressions can be used when a calculation needs to reference the current group level in a Finished Report.

This is useful for calculations such as:

• Counting records within the current group
• Summing values within the current group
• Calculating averages within the current group
• Referencing more than one grouping level in the same expression

For general information about creating group totals or formatting calculated values, see the Related Articles section at the bottom of this page.

Understanding the Current Group Reference

The ^ symbol is used to reference the current group level.

A basic group-level condition looks like this:

[Field] == [^.Field]

For example:

[Client] == [^.Client]

This condition includes records where the Client value matches the Client value for the current group.

Count Records in the Current Group

To count the number of records in the current Client group, use:

[][[Client] == [^.Client]].count()

This returns the number of records where Client matches the current Client group.

Sum a Field Within the Current Group

To total a field within the current group, use:

[][[Client] == [^.Client]].sum([Total_Balance])

This returns the sum of Total_Balance for the current Client group.

Calculate an Average Within the Current Group

An average can be calculated by dividing the group total by the group record count.

For example:

[][[Client] == [^.Client]].sum([Total_Balance]) /
[][[Client] == [^.Client]].count()

This calculates the average Total_Balance for the current Client group.

Note: If division does not return the expected result, the values may need to be converted to a decimal within the expression.

For example:

[][[Client] == [^.Client]].sum([Total_Balance]) /
ToDecimal([][[Client] == [^.Client]].count())

Using Multiple Group Levels

More than one group condition can be included in the same aggregate expression.

For example:

[][[Group1] == [^.Group1] AND [Group2] == [^.Group2] AND [Group3] == [^.Group3]].Avg([YourField])

This calculates the average only for records that match all three current group levels.

Basic Aggregate Syntax

The general syntax is:

[Collection][Condition].Function([Field])

Where:

• Collection — The data collection being evaluated. This can be left empty.
• Condition — Determines which records are included in the calculation.
• Function — The aggregate function, such as count(), sum(), or Avg().
• Field — The field being summarized.

Related Articles

• Creating Group and Report Level Totals in Finished Reports
• Using Format Strings in the Finished Reports Expression Editor
• Expression Editor Function References