On the User Report Details screen, in the Selected Data Fields grid located in the Data Source section, the Aggregate function is available for each data field and provides different options to adjust the outputs of the report. It is a useful tool in a number of scenarios, most commonly with a custom SQL field but can also be used on standard fields.
The most used aggregate functions are:
COUNT function - used frequently to return the number of something based on a specific criteria. It is quite often used when building widgets to return a count of something. The example below counts the number of assignments per Host Country.
CNTDIST function - returns a count of the number of distinct values. e.g. - the number of users who have activated the Mobile Employee Experience App.
The SUM, MIN, MAX and AVG functions will be based on the following example where an assignment record has three bonus records.
SUM function - useful to create totals or sum totals in your reporting.
MIN function - used to find the lowest value or smallest value (numerically) in a list of values in the group. The minimum value is the first non-NULL value that would appear in the column. This function should only be used on fields with numerical values to achieved the desired outcome and this should not be automatically used on Custom SQL calculated field just to ensure that the first row of the user report contains non NULL values.
MAX function - behaves the opposite way of MIN, in that it is used to find the highest value or largest value (numerically) in a list of values in the group. The maximum value is the first non-NULL value that would appear in the column. This function should only be used on fields with numerical values to achieved the desired outcome and this should not be automatically used on Custom SQL calculated field just to ensure that the first row of the user report contains non NULL values.
AVG function - used to return the average value of a data set.
