Reporting 2.0 - Aggregate Values and Totals

Aggregation operators in Reporting 2.0 enable users to total their reports by applying count and count unique. Additionally users can apply average, minimum, maximum, or sum to selected columns. Reporting 2.0 delivers aggregations for the entire data set and outputs the result in the last line of the report.

Aggregation operators are available from the Column Header Properties panel, which is accessed by clicking the icon to the right of a field name in the Table Columns section.

Aggregate values display for each segment of a summarized report. In addition, a grand summary appears as the last row in a summarized report.

Note: Group aggregation is available by summarizing the report. See Reporting 2.0 - Summarize Report.

To access Reporting 2.0, go to Reports > Reporting 2.0.

For more information about Reporting 2.0 permissions, see the following:

  • Permissions in Reporting 2.0 - This provides detailed information about Reporting 2.0 permissions.
  • Permissions List - This provides the list of permissions and their relationships so that you know which permissions users need to have to create and view reports.

Aggregate Operators

The type of operator that is available for a field depends on the field type. All fields can be aggregated by count and count unique, but only numeric fields can be aggregated by sum, average, minimum, and maximum.

Aggregate values are displayed for each segment of a summarized report.

To access aggregation operators, click the icon to the right of the field name in the Table Columns section. This opens the column properties panel. The available operators appear in the Apply Aggregation section:

Aggregation Operators for Numeric Data Types

The following aggregation operators are available for numeric data types:

This displays the sum of all values for the field. When selected, the column name changes to <Sum [Field Name]>. To remove the sum function, select Remove Sum from the drop-down.

This option is available for numeric fields only. This displays the average of all values for the field.

This option is available for numeric fields only. This displays the minimum value for the field.

This option is available for numeric fields only. This displays the maximum value for the field.

Multiple aggregations may be added for the same field as long as they are different aggregate values. For example, the field Training Hours can be added to a report multiples times with all possible aggregations: Min, Max, Avg, and Sum. The system will automatically select the next available aggregate for you, but it may be changed from the column properties after it has been added.

Aggregation Operators for All Data Types

The following aggregation operators are available for all data types:

This option gives you a total count of the values within a field. When you select the Count option, the word "Count" is added to the title of the column.

For example, you are creating a Transaction Custom Report and would like to know the total number of credit and debit transactions in your portal. You add the Transaction User field and the Transaction Type field to the report. You apply the Count option to the Transaction User field, and then you open the Preview panel. The report previews an aggregate of the number of transactions within each transaction type. You return to the Builder tab and filter the Transaction Type field by "Credit" and "Debit." Back on the Preview panel, you find that the report is filtered to show only credit and debit transactions. The count operator at the bottom of the report gives you the total number of credit and debit transactions in your portal.

This option gives you a total count of the unique values within a field. When you select the Count Unique option, the words "Count Unique" are added to the title of the column.

For example, you are creating a Training Custom Report and would like to know the total number of training items that are in each training type. This will help you see which training types have the most training items and which training types have the least training items. To do this, you add the Training Type field and the Training Title field to the report. For the Training Title field, you apply the Count Unique option from the aggregation operators in the column properties panel. You go to the Preview panel and find that the report shows a list of each training type, along with the number of training items within each training type.

Both the Count and Count Unique aggregates can be added to the same field. The system will automatically select the next available aggregate for you, based on what has already been chosen. For example, if you have added the field Training Title with the aggregate Count, if you add the Training Title field again, the system will automatically apply the Count Unique aggregate.