Functions

Many different types of functions can be programmed into report columns so that they show the exact information you are seeking. Borrowing from the MySQL language, we will list several common and helpful functions that you can utilize. 

When opening or adding a column to your report, the Add/Edit window that pops up has an “Expression” field - this is where your code will be placed: 

String Functions: 

A ‘string’ is a value that is made up of letters, characters, and sometimes numbers. These string functions will work to manipulate any string values that a column contains. 

A full list of available string functions can be found here: https://mariadb.com/kb/en/string-functions/

CONCAT()

It can be useful to have a column that displays a combination of values. You can accomplish this with the CONCAT() function.

Example:

The values of value1 and value2 are pulled in and are combined with the strings that are defined between the single quotes ‘ ‘ (in this case, whitespace), together into one string.

NOTE: This function will return NULL if any argument is NULL.

CONCAT_WS()

Example:

The values of value1 and value2 are pulled in and are combined with the separator that is defined at the beginning of the function (in this case, ‘ / ‘), together into one string.

Date Functions:

Date functions are functions that manipulate any date or datetime values that a column contains. (Examples of date and datetime values include: 2020-05-24, and 2020-05-24 18:27:42)

A full list of available Date functions can be found here: https://mariadb.com/kb/en/date-time-functions/

DATE()

Sometimes in a report, you only need a date value, rather than the entire date and time. This function transforms a datetime value into a date value.

Example:

YEAR()

This function returns the year of a given date.

Example:

MONTH()

This function returns the month of a given date in the range of 1 to 12, for January to December.

Example:

MONTHNAME()

This function returns the full name of the month for a given date.

Example:

DAY()

This function returns the day of a given date in the range of 1 to 31.

Example:

NOW()

This function returns the current date and time.

Example:

Aggregate Functions:

Aggregate functions are functions that perform calculations on all the values of a column when those values are grouped together. These functions work when they are used with a Group By statement. (Note that in most cases, a Group By statement is used in the report view, not the report itself, or in aggregate datasets.)

• Setting up aggregate functions with Group By statements

When using aggregate functions, it is important to understand how Group By statements behave. We will use the following result set as an example:

If we use a Group By statement to group by person_id, the result set will be grouped by all of the unique values of the person_id column. Because these columns will be compressed down into single rows based on the person_id, the rest of the columns will have random values, since there can be no guarantee over which value will be displayed and used:

(Note that person_name will also be random, but in this case, it will most likely contain either "John" or "Mary", since there is only one name for every person_id in this example.)

• Using aggregate functions with Group By statements

For all other columns, in order to return exact values rather than random ones, aggregate functions need to be used. For example:

• MAX(amount) will return the highest gift amount for every person.

• SUM(amount) will return the sum of all gifts for every person.

• AVG(amount) will return the average amount of all gifts for every person.

• COUNT(gift_id will return the number of gifts for every person.

• MIN(date) will return the oldest gift date for every person.

• GROUP_CONCAT(gift_id SEPARATOR ', ') will return all gift IDs, concatenated with the separator (the default separator is ',').

A full list of available aggregate functions can be found here: https://mariadb.com/kb/en/aggregate-functions/

• SUM(SUM({column})) OVER()

Original Text:

Expressions like SUM(SUM({column})) OVER () are used when you have a Group By in the view, which means the Amount column (not the Total column) is written like SUM({column}).

You can consider the expression as SUM( [whatever the amount column is] ) OVER().

New Text:

We have looked at simple aggregate functions that work with Group By statements. such as SUM({column})....

• SUM(SUM({column}) OVER (PARTITION BY({column}))

Example: SUM(SUM(c_708)) OVER (PARTITION BY(c_700))