Categorize report data with aggregations

Setting an Aggregation on a field will group like rows together based on other columns.  Aggregation options are available for Date, Money, Number, and ID fields. By default, no aggregation is selected for any field types.  If an Aggregation option is selected for a field, you will find the type of aggregation in parenthesis next to the field.

Where applicable, follow the steps below to change the aggregation for each field:

  1. Select Reports  > Builder.

  2. Use the Show All button to view all custom reports. Or use the Search Bar to further drill down your search. 

  3. Select the Report from the list. 

  4. Right click on the field after it has been dragged onto the Columns area and select the desired option from the Aggregation menu .


Date Field Aggregation

For Date fields, the following aggregation options that can be chosen :

  • Maximum: Displays the most recent date.

  • Minimum: Displays the earliest date.

In the example, a report was created showing the Patient ID, Patient Last Name, Patient First Name, Patient Balance, Last Payment Date, and Claim From Date.

If no aggregation is specified for any date related fields, the report may display the following data: 



If Aggregation is set to Maximum for the Claim From Date the report will only display the most recent Claim From Date.  

Money Field Aggregation 

For Money fields, the following aggregation options that can be chosen:

  • Sum:  Displays the summation (total) of the values.

    • Average: Displays the average of the values.

    • Maximum: Displays the largest value.

    • Minimum:  Displays the smallest value.

In the example, a report was created showing the Patient ID, Patient Last Name, Patient First Name, Patient Balance, Claim ID, and Charge Amount.

If no aggregation is specified for any money related fields, the report may display the data:

If Aggregation is set to Average for the Charge Amount field the report will only display the average value for the Charge Amount. For example:

 If Aggregation is set to Sum for the Charge Amount field the report will display the sum value for the Charge Amount. For example:


Check Your Understanding:

Notice the two like rows have been grouped together based on the non-aggregated fields and the Charge Amount is now reporting the average value for the grouped like rows. The last row on the report was not grouped with the first two rows because the Claim ID value was different (i.e. this charge was associated with a separate claim).


Number Field Aggregation 

For Number fields, the following aggregation options that can be chosen :

  • Sum: Displays the summation (total) of the values.

    • Average: Displays the average of the values.

    • Count All: Displays the count of all occurrences of the value.

    • Count Unique: Displays the count of all unique occurrences of the value.

    • Maximum: Displays the largest value.

    • Minimum: Displays the smallest value.

In the example, a report was created showing the Patient ID, Patient Last Name, Patient First Name, Claim ID, and Charge Units.

If no aggregation is specified for any number related fields, the report may display the following data:

 

If Aggregation is set to Sum for the Charge Units field the report will only display the sum value for the Charge Units. 

If Aggregation is set to Maximum for the Charge Units field the report will only display the largest value for the Charge Units. For example:


Check Your Understanding:

Notice that the two like rows have been grouped together based on the non-aggregated fields and the Charge Units is now reporting the summed value for the grouped like rows. The last row on the report was not grouped with the first two rows because the claim ID value was different (i.e. this charge was associated with a separate claim). The Charge Units field now represents the total number of units billed on the claim regardless of the number of line items.


ID Field Aggregation 

For ID fields, the following aggregation options that can be chosen :

  • Count All: Displays the count of all occurrences of the value.

    • Count Unique: Displays the count of all unique occurrences of the value.

In the example, a report was created showing the Patient ID, Patient Last Name, Patient First Name, Claim From Date, and Charge ID.

If no aggregation is specified for any ID related fields, the report may display the following data:

 If Aggregation is set to Count Unique for the Charge From Date field, the report will only display the total count of the Charge ID(s). For example:


Check Your Understanding:

Notice that the two like rows have been grouped together based on the non-aggregated fields and the Charge ID is now reporting a count of the unique values in the Claim ID field for the grouped like rows. The last row was not grouped with the first two rows because the Claim From Date value was different. This is because the charge was associated with a separate claim. The Charge ID field now represents the total count of unique charges (or line items) for each claim.