Creating Calculations

Calculations are easily created, as long as all the data you need is available in the report.

To begin creating a calculation:

Click ‘Actions’ -> ‘Format’ -> ‘Compute’.

Note: It is important to give your new calculation a name if you want to use it again later! Enter a name into the 'Column Label' field.

Once you have named your calculation, it is time to create a computation expression. This is where you can enter the expression you wish to perform on the report columns of your choice. To help you with your expression there are three handy tools:

The ‘Columns’ list displays all columns in the current report
‘Keypad’ contains mathematical operators and integers that can be used in your expression
‘Functions/Operators’ contains a list of useful mathematical operators and functions

Comparison operators

Operator	Meaning	Examples
!= (Not Equal To)	Tests if two values are not the same.	`Sales != 0` filters for all rows where sales are not zero.
< (Less Than)	Tests if the first value is smaller than the second.	`Hire_Date < '01-JAN-2023'` filters for employees hired before January 1, 2023.
<= (Less Than or Equal To)	Tests if the first value is smaller or equal to the second.	`Salary <= 50000` filters for employees earning $50,000 or less.
= (Equal To)	Tests if two values are the same.	`Region = 'North America'` filters for sales in North America.
> (Greater Than)	Tests if the first value is larger than the second.	`Quantity > 100` filters for orders with more than 100 items.
>= (Greater Than or Equal To)	Tests if the first value is larger or equal to the second.	`Profit >= 0` filters for profitable products.

Mathematical functions

Functions	Meaning	Examples
ABS	Returns the absolute value of a number.	`ABS(-123)` returns 123
CEIL	Rounds a number up to the nearest integer.	`CEIL(3.14)` returns 4
COS	Calculates the cosine of a number (in radians)	`COS(0)` returns 1
EXP	Calculates the exponential value of a number (e raised to the power of the number).	`EXP(1)` returns approximately 2.718 (Euler's number)
FLOOR	Rounds a number down to the nearest integer.	`FLOOR(3.99)` returns 3.
GREATEST	Returns the largest value from a list of values	`GREATEST(10, 5, 25)` returns 25
LEAST	Returns the smallest value from a list of values	`LEAST(10, 5, 25)` returns 5
LOG	Calculates the natural logarithm of a number	`LOG(10)` returns approximately 2.303
MOD	Returns the remainder after division of one number by another	`MOD(10, 3)` returns 1
POWER	Raises a number to the power of another number.	`POWER(2, 3)` returns 8 (2 cubed)
ROUND	Rounds a number to a specified number of decimal places.	`ROUND(3.14159, 2)` returns 3.14
SIGN	Returns -1 if a number is negative, 0 if zero, and 1 if positive	`SIGN(-5)` returns -1
SIN	Calculates the sine of a number (in radians).	`SIN(0)` returns 0
SQRT	Calculates the square root of a number	`SQRT(16)` returns 4
TRUNC	Truncates a number to a specified number of decimal places.	`TRUNC(3.14159, 2)` returns 3.14.

Date and time functions

Functions	Meaning	Example
ADD_MONTHS	Adds a specified number of months to a date.	`ADD_MONTHS('01-JAN-2023', 6)` returns '01-JUL-2023'.
CURRENT_DATE	Returns the current date.	`CURRENT_DATE` (today) might return '07-AUG-2024'
CURRENT_TIMESTAMP	Returns the current date and time.	`CURRENT_TIMESTAMP` (right now) might return '07-AUG-2024 09:04:00'.
LAST_DAY	Returns the last day of the month for a given date	`LAST_DAY('15-FEB-2024')` returns '29-FEB-2024'.
MONTHS_BETWEEN	Calculates the number of months between two dates.	`MONTHS_BETWEEN('01-JAN-2024', '01-JUL-2024')` returns 6.
NEXT_DAY	Returns the date of the first weekday named that is later than a given date.	`NEXT_DAY('05-AUG-2024', 'FRIDAY')` returns '09-AUG-2024'.
SYSDATE	Synonym for CURRENT_DATE.
SYSTIMESTAMP	Synonym for CURRENT_TIMESTAMP

Text Functions

Functions	Meaning	Examples
CHR	Returns the character corresponding to a specified ASCII code.	`CHR(65)` returns 'A'.
INITCAP	Capitalizes the first letter of each word in a string	`INITCAP('hello world')` returns 'Hello World'
INSTR	Searches for a substring within a string and returns its position.	`INSTR('Oracle APEX', 'APEX')` returns 6.
LENGTH	Returns the number of characters in a string.	`LENGTH('APEX')` returns 4.
LOWER	Converts all characters in a string to lowercase	`LOWER('APEX')` returns 'apex'.
LPAD	Pads the left side of a string with a specified character up to a given length.	`LPAD('5', 3, '0')` returns '005'.
LTRIM	Removes leading spaces from a string.	`LTRIM(' APEX')` returns 'APEX'.
REPLACE	Replaces all occurrences of a substring within a string with another substring.	`REPLACE('Oracle APEX', 'APEX', 'Application Express')` returns 'Oracle Application Express'.
RPAD	Pads the right side of a string with a specified character up to a given length.	`RPAD('5', 3, '0')` returns '500'.
RTRIM	Removes trailing spaces from a string.	`RTRIM('APEX ')` returns 'APEX'.
SUBSTR	Extracts a portion of a string.	`SUBSTR('Oracle APEX', 1, 6)` returns 'Oracle'.
TRANSLATE	Replaces specific characters in a string with other characters.	`TRANSLATE('123-456-7890', '-.', ' ')` returns '123 456 7890'.
TRIM	Removes leading and trailing spaces from a string.	`TRIM(' APEX ')` returns 'APEX'.
UPPER	Converts all characters in a string to uppercase.	`UPPER('apex')` returns 'APEX'.

Other Functions and Operators

Operators	Meaning	Examples
AND	Logical AND operator.	`Sales > 10000 AND Region = 'North America'` filters for sales over $10,000 in North America.
BETWEEN	Tests if a value is within a specified range.	`Salary BETWEEN 40000 AND 60000` filters for salaries between $40,000 and $60,000.
CASE	Allows conditional expressions (similar to IF-THEN-ELSE logic).	SQL CASE WHEN Sales > 10000 THEN 'High' WHEN Sales > 5000 THEN 'Medium' ELSE 'Low' END This categorizes sales as 'High', 'Medium', or 'Low'
COALESCE	Returns the first non-null value from a list of values.	`COALESCE(Commission, 0)` returns the commission amount, or 0 if it's null.
DECODE	Compares a value to a series of search values and returns the corresponding result value.	DECODE(Region, 'North America', 'NA', 'Europe', 'EU', 'Other') This converts region names into codes ('NA', 'EU', 'Other').
ELSE	Used in CASE statements for alternative conditions.
END	Marks the end of a CASE statement.
IN	Tests if a value is contained within a list of values.	`Region IN ('North America', 'Europe')` filters for sales in North America or Europe.
IS	Used to check for NULL values.	`Commission IS NULL` filters for rows with missing commission data.
LIKE	Tests if a string matches a pattern (using wildcards like % and _).	`Product_Name LIKE '%Phone%'` filters for products containing the word "Phone".
NOT	Logical NOT operator.	`Region NOT IN ('North America', 'Europe')` filters for sales not in North America or Europe.
NOT BETWEEN	Tests if a value is outside a specified range.	`Salary NOT BETWEEN 40000 AND 60000` filters for salaries below $40,000 or above $60,000.
NOT IN	Tests if a value is not contained within a list of values.	`Region NOT IN ('North America', 'Europe')` filters for sales not in North America or Europe
NOT LIKE	Tests if a string does not match a pattern.	`Product_Name NOT LIKE '%Phone%'` filters for products not containing the word "Phone".
NULL	Represents a missing or unknown value.
NVL	Synonym for COALESCE
OR	Logical OR operator.	`Sales > 10000 OR Region = 'North America'` filters for sales over $10,000 or in North America.

Regular Expression Functions

Functions	Meaning	Examples
REGEXP_INSTR	Similar to INSTR, but uses regular expressions for pattern matching.	`REGEXP_INSTR('The quick brown fox', '[aeiou]{2}')` returns 12 (position of the first occurrence of two consecutive vowels, "ow").
REGEXP_LIKE	Similar to LIKE, but uses regular expressions for pattern matching.	`REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$')` checks if the Email value is in a valid email format.
REGEXP_REPLACE	Similar to REPLACE, but uses regular expressions for pattern matching	`REGEXP_REPLACE('The price is $1,234.56', '[$,]', '')` removes dollar signs and commas, returning 'The price is 1234.56'.
REGEXP_SUBSTR	Similar to SUBSTR, but uses regular expressions for pattern matching.	`REGEXP_SUBSTR('Invoice #12345', '\d+')` extracts the numeric part '12345' from the string.

Data Conversion Functions

TO_CHAR	Converts a date or number to a string	`TO_CHAR(SYSDATE, 'DD-MON-YYYY')` might return '07-AUG-2024'.
TO_DATE	Converts a string to a date.	`TO_DATE('07-AUG-2024', 'DD-MON-YYYY')`.
TO_TIMESTAMP	Converts a string to a timestamp.	`TO_TIMESTAMP('07-AUG-2024 09:04:00', 'DD-MON-YYYY HH24:MI:SS')`

Common filters expressions and their descriptions

Expression	Description
trunc(CU 'mm')	truncate column CU (calculation Date) to month level
trunc(sysdate)	Today (00:00 to 23:59 of the current day)
trunc(sysdate, 'w')	The current calendar week
trunc(sysdate - 7, 'w')	The previous calendar week
trunc(sysdate, 'mm')	The current calendar month
add_months(trunc(sysdate, 'mm'), -1)	The previous calendar month
trunc(sysdate, 'yyyy')	The current calendar year
add_months(trunc(sysdate - case when sysdate > add_months(trunc(sysdate,'yyyy'), 3) + 6 then 0 else 365 end, 'yyyy'),3) + 5	The current tax year

Info: Format Mask can be used if your expression needs to be displayed at a certain precision or if a date, in a certain format.

Note: If the format you require isn't on the list then don't panic! you can manually enter your own mask within the text box.

Once you are happy with your calculation, click the ‘Apply’ button.

Viewing Calculations

To access your calculation click ‘Actions’ then ‘Select Columns’.

The following menu will now display your calculation in the list of available columns. Calculations are denoted by ‘**’ at the beginning of the column name.

Ensure that calculation is under 'Display in Report', then click 'Apply'. Your calculation will be displayed in your report.

Cashflow Object v2

Advanced Filtering and Row Filtering

Formatting Reports

Calculated Fields

Creating Calculations

Expression

Description

Viewing Calculations