Creating custom columns using formulas
Looking for help with this feature in Polaris? Check out Creating custom columns using formulas in the Polaris help.
Custom columns are report fields that are derived from user-defined formulas. Users create these formulas by combining columns (or fields) available in the report with operators and functions. Formulas can be either numeric or text strings.
Examples of custom column formulas
A numeric formula
A formula to calculate the billable total amount in the Project Team Billing report:
(TotalActualCost - HourlyCostAmount) / ActualBillableHours*100
A text string formula
A formula to identify a user and their substitute in the User Detail report:
(UserName) + (UserSubstituteUserName)
Usage examples
These are some examples of what Replicon customers use custom report columns for:
- Alert managers about projects that have exceeded hours estimates
- Show the number of days a timesheet was late by
- Show the number of days a timesheet took to be approved, once submitted
- Identify which employees adhered to their schedule
- Split billable amounts by primary, secondary, and tertiary funding sources
- Rate projects based on their resource draw, and other custom criteria
- Mark employees as FMLA eligible
- Categorize expenses as debits or credits
- Show the day of week when project time was entered
- Flag projects whose end date has changed
Usage example #1: Alerting managers when projects exceed hours estimates
Say you want to alert managers when projects exceed hours estimates.
To do this, you could set up a custom field called Alert in a report based on the Project template, that uses this formula:
IF(TotalActualHours/ProjectEstimatedHours>1.3,"RED",IF(TotalActualHours/ProjectEstimatedHours>1,"YELLOW",""))
Then, this column will show YELLOW if the project hours exceed the estimate, and RED if hours are more than 130% of the estimate.
Adding a custom column
To add a custom column:
- Go to Reports, and select the report you want to add a column to. Or, add a new report.
- Click the Settings button.
A Report Settings window displays.
- Select the Custom toggle.
- Click the
icon next to Add a new custom column.
icon next to Add a new custom column.
A bubble displays.
- Give the column a name and select the format that should be used when the column is displayed.
- Select a field to include in the formula from the Formula drop-down. You can also type the field name into the formula field, to filter and search the list of available fields.
Only fields from the report you’re using are available in the drop-down.
- Click Insert.
- Add additional fields, and any required operators and functions to the formula.
Refer to Operators and functions you can use in custom columns for a list of all functions and operators supported for use in formulas.
- Click Add, and then click Done.
To edit an existing custom column, click the 
icon next to its name.
About the number format options
|
Format |
Description |
|---|---|
|
Automatic |
Replicon will base the column formatting on the values included in the formula. Automatic formatting may not accurately reflect the number type, especially when a formula is complex. |
|
Currency |
If you include a currency value in a formula, the result of the formula will be displayed in the system's default currency along with its symbol. Non-currency values will not display in the report. |
|
Numeric |
The result from the formula is displayed as a numeric value, with no symbols |
|
Percentage |
The result from the formula is automatically multiplied by 100, and is displayed with a % sign (for example, 73.5%) |
FAQs
An error code displays in a custom column cell when I generate a report. What does it mean?
Replicon detects most formula errors when the formula is saved. However, some errors – called run-time errors – are indicated by an error code that displays in the report cell after the report is generated. The meaning of each error code is outlined in the tables below.
Numeric Run-Time Error Codes
|
Error code |
This code indicates that... |
|---|---|
|
(#DIV/0!) |
The formula specified that a number be divided by a column whose value is zero, therefore the calculation could not be made. |
|
(#NUM!) |
An overflow error has occurred, meaning a number involved in the calculation was too large for the computer to handle. |
|
Null reference |
The formula includes a reference to another column that has no value. |
String Run-Time Error Codes
|
Error code |
This code indicates that... |
|---|---|
|
(#RANGE!) |
The substring requested falls out of the string's range. |
How do we indicate hard-coded values in text string formulas?
You can include both variable string fields and constant "hard-coded" text strings in formulas. To indicate that text you enter in the formula should be treated as a "hard-coded" string, enclose the text in double-quotation marks. For example, the formula "Empty Cell" will display as Empty Cell in the report.
How do we include quotation marks in hard-coded text strings?
If you want to include quotation marks in a hard-coded text string, include a backslash symbol (\) before each quotation mark you want to include. For example, the formula "Project \"Sunshine\"" will display as Project "Sunshine" in the report.
If you want to include a backslash (\) in a constant string, use two backslashes (\\). For example, the formula "Costs \\ Expenses" will display as Costs \ Expenses in the report.
Can I create an empty field using custom columns?
You can create an empty custom column by entering a double set of quotation marks ("") as a custom column formula. This is useful if you plan to export Replicon data to an application that requires certain fields to be empty.
Can I use the custom columns I create anywhere else in Replicon?
No. Custom columns are only available in reports.
I want to use a formula I created in several reports. How can I do this?
If you need to use a field in more than one report, simply copy and paste the formula.
Can I use reporting features like summary options with custom columns?
Standard report features such as summary options and column ordering can be used with custom columns.
Will the custom columns I created display in PDF and Excel exports?
Yes, they will. However, in Excel exports, they are stored as values, not as formulas.
Related topics
Operators and functions for use in custom columns
Report custom column formula usage examples
Using Replicon reports (video)
Using Replicon reports
Scheduling a report to be emailed regularly
