16.05 Formula Column Syntax

Root Folder > UNItekTIME Documentation > 17 Reports

16.05 Formula Column Syntax

Formula columns are derived from user-defined formulas. Users create these formulas by combining columns (fields) available in the report.

When might I need to create a custom column?

Custom columns are useful for creating such numeric fields as:
  • Values with non-standardized modes of calculation that are therefore not included in UNItekTIME as default columns (for example, overhead cost etc)
  • Calculations specific to your organization
  • Highly-specialized calculations

Numeric Formula Examples:

  • A formula for 10% Overhead cost in the [Detail Timesheet Report]: (Amount * 10 / 100)
  • A formula for calculating profit margin in the [Detail Timesheet Report]. (BillingRate – EmployeeRate) * TotalHours

Text Formula Examples:

  • To identify the location and their departments in a single field, you could combine both location and department by creating a formula like this: (AccountLocation+’-’ DepartmentName)

Numeric Operators:

OperatorUse this operator to specify…
+Addition
-Subtraction

Multiplication
/Division
( … )Parentheses
=Two values are equal
<Less than
>Greater than
<=Less than or equal
>=Greater than or equal
!=Not equal
%Modulus
Operators follow standard order of operations rules. For example:
  • 2 + 2 * 2 = 6, but (2 + 2) * 2 = 8
  • 2 + 2 / 2 = 3, but (2 + 2) / 2 = 2

Functions:

FunctionsDescriptionSyntaxExample
IIFGets one of two values depending on the result of a logical expressionIif ( expr, truepart, falsepart )Iif (billingrate>500,‘expensive’,‘dear’ )
LENGets the length of a stringLEN ( expression )Len (EmployeeName)
CONVERTConverts particular value to a specified .NET Framework Type.Convert(expression, type)Convert(total, ‘System.Int32’), Convert(id,‘System.String’)
SUBSTRINGGets a sub-string of a specified length, starting at a specified point in the string.SUBSTRING (expression, start, length )SUBSTRING ( phone, 7, 8 )
Article ID: 80, , Modified: 3/24/2021 at 3:39 AM

Was this article helpful?

Share this article