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 TimeLive 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 combime First Name and Last Name in a single field, you could combine both FirstName and LastName by creating a formula like this: (FirstName + ‘-’ + LastName)
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 ) |