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:
| Operator | Use 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:
| Functions | Description | Syntax | Example |
|---|
| IIF | Gets one of two values depending on the result of a logical expression | Iif ( expr, truepart, falsepart ) | Iif (billingrate>500,‘expensive’,‘dear’ ) |
|---|
| LEN | Gets the length of a string | LEN ( expression ) | Len (EmployeeName) |
|---|
| CONVERT | Converts particular value to a specified .NET Framework Type. | Convert(expression, type) | Convert(total, ‘System.Int32’), Convert(id,‘System.String’) |
|---|
| SUBSTRING | Gets 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, Created: 3/24/2021 at 1:10 AM, Modified: 3/24/2021 at 3:39 AM