Support

Knowledge Base

Browse our knowledge base articles to quickly solve your issue.

Using Calculated Columns

Workbooks reporting allows you to build a Calculated Column using formulae.  The formulae are based on MySQL statements (which is simply a language that can be used to extract information from a database).  Much like formulae in spreadsheets, formulae in Workbooks can be used for many purposes, including:

Tip

Click on Refresh preview each time you add a column, criteria or summarise your data.

This enables you to check your report is building correctly and 'sense-check' that it's displaying the results in the way you require.

  • Calculations (for example, additions, subtraction, multiplication, etc).
  • Comparing data (for example, if two values are the same, display 1 and if they're different display 0).
  • Displaying dates and times in relation to another specified date and time.
  • Formatting data such as dates, times, rounding decimal places, etc.
  • Converting data, such as numbers into strings.
  • Concatenating data (displaying a number followed by a percentage symbol, for example).

If you want to apply a simple calculation that generates any of the following:

  • the total of the values
  • the minimum of the values
  • the maximum of the values
  • the average of the values

you can do this by adding a column for the amount and ticking the checkbox next to Summarise the values.  This will open a field called Calculate where you can use the dropdown picklist to control what's being calculated, as illustrated in the screenshot below.

If you want to add a more complex calculation you should add a Calculated Column.  The table below gives some MySQL statements that you might find useful when putting together Reports in Workbooks.  It is not intended to be an exhaustive list but includes some of the more commonly used expressions. For more information on how these MySQL statements work, we recommend that you consult a specialised manual.  Of course, if you need any help with reporting, try the reporting section on the forum.

Add a specified amount of time to a date 

DATE_ADD( ) 

DATE_ADD(CURDATE(), INTERVAL 7 DAY)

this adds 7 days to the current date

NOTE:  The following interval types can be used in place of DAY:  SECOND, MINUTE, HOUR, WEEK, MONTH, YEAR 

Subtract a specified amount of time from a date 

DATE_SUB( ) 

DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

this subtracts 1 month from the current date

NOTE:  The following interval types can be used in place of MONTH:  SECOND, MINUTE, HOUR, DAY, WEEK, YEAR 

Format a date 

DATE_FORMAT( ) 

DATE_FORMAT(column_name, '%D %M %Y')

eg, DATE_FORMAT(created_at, '%D %M %Y')

would show the date a record was created with the day displayed as an ordinal number followed by a four digit year, for example 1st January 2010.

To display a date as numbers, use the same letters as above but enter them in lower case:

eg, DATE_FORMAT(created_at, '%d %m %y')

which would show the same date as 01 01 10.

There are additional ways to format a date, which can include the weekday, as shown below:

%a

Shows the abbreviated weekday name according to the current locale.

%b

Shows the abbreviated month name according to the current locale.

Insert the current date 

CURDATE() 

CURDATE()

Type in the above to return the current date. 

Insert the current time

CURTIME()

CURTIME()

Type in the above to return the current time. 

Calculate the mean average of a group of numbers 

AVG( )

AVG(column_name)

eg, AVG(amount)

on an opportunity report would return the mean average of the opportunity amounts. 

Add numbers 

+ 

eg, first_column_name+second_column_name

Subtract numbers 

- 

eg, first_column_name-second_column_name

Multiply numbers 

* 

eg, first_column_name*second_column_name

Divide numbers 

/ 

eg, first_column_name/second_column_name

Find the total of a series of numbers 

SUM( )

SUM(column_name)

eg, SUM(amount)

on an opportunity report would return the total of the opportunity amounts. 

Display a specified number of decimal places 

FORMAT( ) 

FORMAT(column_name, integer)

where

integer is the number of decimal places you want to display

eg, FORMAT(AVG(amount),5)

on an opportunity report would return the average opportunity amount to 5 decimal places.

Display text in upper case 

UPPER( ) 

UPPER(column_name)

 

Display text in lower case 

LOWER( ) 

LOWER(column_name)

 
Truncate a string to a specific length LEFT( )

LEFT(column_name, integer)

where

integer is the number of characters you want to limit the string to

Combine two or more fields 

CONCAT( ) 

CONCAT(first_column_name,' ',second_column_name,' ',third_column_name)

NOTE: If any of the fields that are concatenated together return a NULL value, the whole formula returns a NULL value causing your report to show blank rows in its results. If this is the case, see CONCAT_WS below or include a criteria on your report to exclude blank values.
Combine two or more fields CONCAT_WS( )

CONCAT_WS(' - ', first_column_name, second_column_name, third_column_name)

NOTE: This causes the formula to ignore NULL values thus you may see rows in your report that do not make sense e.g. CONCAT_WS(' - ', name, employer). If the employer is blank the formula will return "Jim Bob - ". Also, notice that the SEPARATOR is at the start of the formula and is in quotes. This string value will be added in between each field that is concatenated.

 

More information on this is available at https://www.w3schools.com/, although of course if there are any issues please contact support@workbooks.com. 

Related content

Using Calculated Criteria

How to use Calculated Criteria in order to refine the results to only include records in a report that fit one or another criteria, which don't refer to the same field.