Applying aggregation functions

In the Reports app, aggregation functions perform a calculation on a set of values in a field and return a single value. You can apply aggregation functions to report data to summarize fields.

How it works

An aggregation function takes several values and returns a single, summarized output. You can apply an aggregation function to a field using the Report Builder. 

Examples

Below are examples of some aggregation functions you can apply to fields in Reports.

Permissions

Only Report Admins and Report Writers can apply aggregation functions to report data.

Apply an aggregation function to a field

1. Open the Reports app.

   The Browse page opens.

2. Open a report and navigate to the Data tab in Report Builder.
3. Locate the field you want to apply an aggregation function to by doing any of the following:
   • Enter a keyword in the Search field.
   • Expand the folders to navigate to the appropriate field:
     • Dimension attributes of your data that describe records, such as Likelihood or Severity.
     • Metric values to be measured in the report, such as Cost Impact or Project Budget.
     • Date represent date values, such as Control Created or Control Updated.

       For more information, see Creating reports.

4. Drag the field from the data fields list to the Rows, Columns, Filters or Sections list.
5. Click the down arrow beside the field name.
6. Select Aggregation and select the appropriate aggregation function.

   Result The aggregation function is applied to the field.

   Note

   Using aggregations, such as Count, in reports built on "by Entity" Views may produce misleading results. Unless you specifically want to use the Entity field in your report, Diligent recommends building reports from Views that are not suffixed with "By Entity". For more information, see View reference.

   Tip

   You can also apply an aggregation function to a field in a chart, which can be different from the aggregation function applied to the field in the table. For more information, see Visualizing report data using charts.

   Note

   You can apply advanced functions in combination with an aggregation function to transform results by applying post processing calculations to the initial query results. For more information, see Applying advanced functions.

Available aggregation functions

Aggregation function	Description
None	clears the aggregation applied
Average	returns the average value in the field
Count	returns the number of values in the field
Count Distinct	returns a count of unique values in the field
Maximum	returns the highest value in the field
Minimum	returns the lowest value in the field
Sum	returns the addition of values in the field

Why is my aggregation not working?

All aggregation functions ignore null values. This occurs because Reports cannot add a null value to a number. Null values display when a field in Projects, Results, or Strategy is not required.

Example calculations

• COUNT(1, 2 , 2 , 3 , null ,null) = 4
• COUNT DISTINCT (1, 2, 2, 3, null, null) = 3
• SUM (1, 2, 2, 3, null, null) = 8
• AVG (1, 2, 2, 3, null, null) = 2
• MIN (1, 2, 2, 3, null, null) = 1
• MAX (1, 2, 2, 3, null, null) = 3

How do I fix this?

You can fix this issue by doing any of the following:

• creating a calculated field
• creating a filter
• specifying a value for each record in the field

Creating a calculated field

You can fix null values by creating a calculated field that displays 0 if the value is null and apply an aggregation function on the calculated field:

Creating a filter or creating a calculated field

If you are reporting on timesheets data, you may need to either filter out time entries that belong to the organization, or create a calculated field to replace the Project Name value with the name of your organization.

Specifying a value for each record in the field