Group Aggregates in Finished Reports

Matthew Fifield -

To calculate Percentages and Averages inside of a group in Finished Reports, a Calculated Field can be used.

Basic Syntax

[Collection][Condition].Sum("Field")

[Collection][Condition].Count()

Collection is the DataTable that is being used.  A value is not required.

Condition is the criteria that must be met.  A value is not required.  If left empty all records will be included.

The function that returns a value.

The field that the function is summarizing.

Group Level Condition

In order to create a calculated field that retrieves a value for a specific group level, a Condition is required.  An example Condition is below:

[Field] == [^.Field]

This condition is saying, get everything where this Field is equal to the Current Group Level Field.

The following expression is being used inside of a group based on the Client.  The expression is getting the count of records where the Client is equal to the current level of grouping(grouped by Client)

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

Image of calculatedField Expression

  

Image of a Report with a group on the Client and using the GroupAggregate Expression from above to retrieve the count

  

Image of Preview using the Calculated Field

 

Creating an Average Within a Group

  •  Create a Group (Example is using a Field called Client)
  •  Add the group value in the group header.
  •  Add a Summary for the field that will be averaged(Example is using a Field called Total_Balance).  
    • This step is for validation purposes only and is not required.

Image for the above steps

  • Create and add a calculated field, with the data type of decimal, that will return the count for the specific group level(example from the section Group Level Condition).  In this example the Calculated Field is called "ClientCount". The expression used: [][[Client] == [^.Client]].count()
    • This step is for validation purposes only and is not required

Image for the previous step of adding the ClientCount Calculated Field

  • Finally, create and add another Calculated Field, with the data type of decimal, that will calculate the Average of the desired field within the group(Example is calculating the Average Total_Balance).  In this example the Calculated Field is called "ClientAverageTotalBalance".  The expression used is: [][[Client] == [^.Client]].sum([Total_Balance]) / [][[Client] == [^.Client]].count()

 

IMPORTANT NOTE: If you are not receiving any results when using division, you may have to convert the values to decimal inside of the expression. Example shown below.

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

 

Image of the designer after the final step

 

Image of final Preview

 

Have more questions? Submit a request

Comments

Powered by Zendesk