The function column of the Business Intelligence|Results page, enables you to create an aggregate function for a result member. Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions can only be applied against fields whose results occur more than once.
The following table describes the available aggregate functions. The options available will depend on the result member.
Function |
Description |
Average |
calculates the average value of the field across all occurrences within the record |
Avg As Number |
for string fields, treats the values as numbers and calculates the average value of the field across all occurrences within the record |
Cast As Number |
for string fields, treats the values as numbers |
Count |
calculates the number of occurrences of the field (that contains a non-blank value) within the record |
First Word |
returns the first word of a string. The first word is all characters preceding the first space or dash. |
Date |
returns the date in the format set in Registry::Primitives::TSdateTime |
Day |
returns the day in a numeric value (1-31) |
Day of Week |
returns the day of the week in a numeric value (1-7) where 1=Sunday and 2=Monday. |
Hour |
returns the hour on the date type in a numeric value (0-23) where 0=12AM and 1=1AM. |
Max |
calculates the maximum value of the field across all occurrences within the record |
Max As Number |
for string fields, treats the values as numbers and calculates the maximum value of the field across all occurrences within the record |
Min |
calculates the minimum value of the field across all occurrences within the record |
Min As Number |
for string fields, treats the values as numbers and calculates the minimum value of the field across all occurrences within the record |
Month |
returns the month in a numeric value (1 = January and 6= June) |
Sum |
calculates the total value of the field across all occurrences within the record |
Sum As Number |
for string fields, treats the values as numbers and calculates the total value of the field across all occurrences within the record |
Time |
returns the time on the date type in the format set in Registry::Primitives::TStimeFormat |
To Lowercase |
returns the string in lowercase |
To Uppercase |
returns the string in uppercase |
Year |
returns the year in the format YYYY (2024) |
