Basic Sample Formula Fields

The SuiteAnalytics Workbook Formula Builder enables you to create custom formula fields that generate results for the following output types:

Boolean- Returns True, False, or NULL values
Date- Returns day, month, and year values
Datetime- Returns day, month, year, hour, minute, and second values
Float- Returns values that include a decimal, such as currency values
Integer- Returns values that are in whole numbers only, such count values
String- Returns fixed string character values, such as names
Clobtext- Returns long unicode character strings, such as item descriptions
Percent- Returns numeric values as percentages
Duration- Returns hour and minute values, such as hours worked

Review the following table for some basic formulas you can create for each output type:

Output Type	Base Record	Formula	Description
BOOLEAN	Transaction	`CASE WHEN {transaction^transaction.daysoverduesearch} > 0 THEN 'T' ELSE 'F'END`	Identifies overdue transactions
DATE	Employee	`CASE WHEN {lastreviewdate} IS NULL THEN {hiredate} ELSE {lastreviewdate} END`	Displays the last review date of an employee's salary
DATETIME	Any	`CURRENT_TIMESTAMP()`	Displays a time stamp for each result
FLOAT	Transaction	`{foreigntotal}*-1`	Changes the - sign applied to foreign amounts
INTEGER	Revenue Recognition Plan	`CEIL(MONTHS_BETWEEN(CURRENT_DATE, {plannedrevenue.plannedperiod.enddate}))`	Displays the number of months since the revenue plan was created
STRING	Transaction	`CASE WHEN TRUNC(CURRENT_DATE)-{duedate} <= 7 THEN 'Week 1 (Current)' WHEN TRUNC(CURRENT_DATE)-{duedate} <= 14 THEN 'Week 2' ELSE '...' END`	Displays the number of weeks a transaction is overdue
CLOBTEXT	Item	`{storedetaileddescription}`	Displays a detailed description of the item, which may include HTML code. This can be be displayed to web store users.
PERCENT	Transaction	`CASE WHEN {‌transactionlines.quantity} > 0 THEN {‌transactionlines.quantitybilled}/{‌transactionlines.quantity} ELSE 0 END`	Displays how much of the billing process is complete, as a percentage
DURATION	Time Tracking	`CASE WHEN {isbillable} = 'F' THEN {hours} else 0 END`	Displays non-billable time

Output Type

Base Record

Formula

Description

BOOLEAN

Transaction

                    CASE WHEN {transaction^transaction.daysoverduesearch} > 0 THEN 'T' ELSE 'F'END

Identifies overdue transactions

DATE

Employee

                    CASE WHEN {lastreviewdate} IS NULL
 THEN {hiredate}
 ELSE {lastreviewdate}
END

Displays the last review date of an employee's salary

DATETIME

Any

                    CURRENT_TIMESTAMP()

Displays a time stamp for each result

FLOAT

Transaction

                    {foreigntotal}*-1

Changes the - sign applied to foreign amounts

INTEGER

Revenue Recognition Plan

                    CEIL(MONTHS_BETWEEN(CURRENT_DATE, {plannedrevenue.plannedperiod.enddate}))

Displays the number of months since the revenue plan was created

STRING

Transaction

                    CASE WHEN TRUNC(CURRENT_DATE)-{duedate} <= 7 THEN 'Week 1 (Current)' WHEN TRUNC(CURRENT_DATE)-{duedate} <= 14 THEN 'Week 2' ELSE '...'
END

Displays the number of weeks a transaction is overdue

CLOBTEXT

Item

                    {storedetaileddescription}

Displays a detailed description of the item, which may include HTML code. This can be be displayed to web store users.

PERCENT

Transaction

                    CASE WHEN {‌transactionlines.quantity} > 0
 THEN {‌transactionlines.quantitybilled}/{‌transactionlines.quantity}
 ELSE 0
END

Displays how much of the billing process is complete, as a percentage

DURATION

Time Tracking

                    CASE WHEN {isbillable} = 'F' THEN {hours} else 0 END

Displays non-billable time

For more advanced formulas, see Advanced Sample Formula Fields.

Related Topics