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 OfficeTimer 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 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 are 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 ) |