Advanced Filtering and Row Filtering

Accelerate reports include advanced filtering options - for when you need to filter data based on rows.

Row level filtering has the capability to use functions (much like excel) to manipulate data how you need it.

Info: To perform Advanced filtering, ensure the report you are using is a default report.

On a report where you have created a custom report - click the dropdown next to the search-bar and click the ‘Primary Report’ option.

To get your report ready for advanced filtering, configure your report with the columns you require by using the ‘Select Columns’ button in the ‘Actions’ menu.

Then go to ‘Actions’, then ‘Filter’.

In the filter panel, select Row as the filter type

Info: It is important to give a name to your filter expression. This won’t affect the operation of the filter. A useful filter name is important if you need to look at the filter again!

The 'Columns' column on the left gives aliases to all columns in the current report. The 'Functions/Operations' Column lists the different operators and functions that can be applied to the report columns.

Here are the Functions and Operators to use when filtering your reports.

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')`.

Once you have a name for the filter expression, it is time to create the filter expression.

Expand to view the different 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

Click 'Apply' to apply your advanced filtering to your report.

Additional Notes

Function Parameters: Many functions take additional parameters to customise their behaviour. Refer to for specific parameter details.

Error Handling: Be mindful of potential errors (e.g., dividing by zero) and use functions like NVL or COALESCE to handle null values.

Performance: Consider performance implications when using complex expressions in reports with large datasets.

Relationships v2

Accelerate Reports Engine

Opportunity Object v2

Calculated Fields

Search Proposals v2

Expression

Description