Say your business practice is to take 10% of the budget as an
advance. You could use a currency field which managers will fill when
creating or modifying projects. The problem is that managers must
manually calculate the advance from the budget and remember to change it
every time the budget changes. This is error prone. To make things a bit
more complicated, assume that the policy is to take an advance of 10%
for projects with budget upto $1000 and 15% above that. This increases
the chances of errors. This is where formula fields come to the rescue.
You would create advance amount as a formula field with the formula
${project.budget <= $1000 ? project.budget * .10 :
project.budget * .15}. For a project with a budget of $500, the
advance amount will show $50 (10% of $500) while that for a budget of
$5000 will show $750 (15% of $5000). This brings many benefits. The
value of this field is auto-calculated from the formula and is not
entered by the user avoiding errors. The amount will be automatically
updated when the budget changes. Most importantly, if the policy changes
all you need to do is update the formula. The advance amount for all
projects will automatically reflect the new value.
Formula fields, like other custom fields, are available as individual columns in reports. You can also filter, sort and group reports using them.
Depending on whether the formula field is attached to Project, Task or Process, different variables are accessible in the formula. For example, for a project field, the variable "project" is available. The table below shows the fields and their scope.
| Variable | Description | Type |
|---|---|---|
| id | Unique ID of the project | Integer |
| name | Name of the project | String |
| start | Start date of the project | Date |
| deadline | Deadline of the project | Date |
| finish | The max finish date of this projects tasks | Date |
| projectedFinish | Projected finish date of the project based on % complete of its tasks | Date |
| percentComplete | Percent complete of the project | Integer |
| budget | Budget | Double |
| fixedPrice | Fixed Price | Double |
| estimatedHours | Estimated Hours of this project | Double |
| actualHours | Actual Hours. This is the sum of all time approved for this project. | Double |
| actualCost | Actual Cost for this project so far. | Double |
| projectedCost | Projected Cost for this project based on the % complete so far | Double |
| spi | Schedule Performance Index | Double |
| cpi | Cost Performance Index | Double |
| acwp | Actual Cost of Work Performed | Double |
| bcwp | Budgeted Cost of Work Performed | Double |
| bcws | Budgeted Cost of Work Scheduled | Double |
| Variable | Description | Type |
|---|---|---|
| id | Unique ID of the task | Integer |
| name | Name of the task | String |
| start | Start date of the task | Date |
| finish | Finish date of the task | Date |
| actualStart | The actual start date of this task | Date |
| actualFinish | The actual finish date of this task | Date |
| milestone | Is this task a milestone | Boolean |
| assigned | Is any resource assigned to this task | Boolean |
| projectedFinish | tasked finish date of the task based on % complete of its tasks | Date |
| percentComplete | Percent complete of the task | Integer |
| budget | Budget | Double |
| estimatedHours | Estimated Hours of this task | Double |
| actualHours | Actual Hours. This is the sum of all time approved for this task.s | Double |
| actualCost | Actual Cost for this task so far. | Double |
| projectedCost | Projected Cost for this task based on the % complete so far | Double |
| spi | Schedule Performance Index | Double |
| cpi | Cost Performance Index | Double |
| acwp | Actual Cost of Work Performed | Double |
| bcwp | Budgeted Cost of Work Performed | Double |
| bcws | Budgeted Cost of Work Scheduled | Double |
| project | The project for this task. You must refer to a sub-property of the project. For example, task.project.name will be the name of the project and task.project.start will be start date of the project. | Same as the type of the project variable |
You can use custom fields created by you in formulas as well.
The syntax to refer to them is slightly different. For example,
project custom fields would be referred to as
${project.custom['45634']}. The numeric number is the
unique identifier for the custom field. The reason we refer to them
with the unique id instead of their name is that if the names
change, you do not need to change formulas that refer to them. For
convenience, the custom fields with their names and unique ids are
shown when creating formula fields.
Forumla fields can either be expressions as in
${project.budget * 0.10}, or contain a mix of expression
and textas in <a
href="http://mywikiserver/project/${project.id}>Wiki
Link</a>. As you will notice, anything enclosed in
${..} is treated as an expression and evaluated.
Everything outside it is copied to the output verbatim. Conditional
logic can also be performed as we will see later.
In addition to the . and [] operators discussed in Variables, the expression language provides the following operators:
Arithmetic: +, - (binary), *, / and div, % and mod, - (unary)
Logical: and, &&, or, ||, not, !
Relational: ==, eq, !=, ne, <, lt, >, gt, <=, ge, >=, le. Comparisons can be made against other values, or against boolean, string, integer, or floating point literals.
Empty: The empty operator is a prefix operation that can be used to determine whether a value is null or empty.
Conditional: A ? B : C. Evaluate B or C, depending on the result of the evaluation of A.
The precedence of operators highest to lowest, left to right is as follows:
[] .
() - Used to change the precedence of operators.
- (unary) not ! empty
* / div % mod
+ - (binary)
< > <= >= lt gt le ge
== != eq ne
&& and
|| or
? :
| Formula | Output |
|---|---|
| ${5 + 3} | 8 |
| ${5 - 3} | 2 |
| ${5 / 3} | 1.67 |
| ${5 * 3} | 15 |
| ${5 % 3} | 2 |
| ${5 > 3} or ${5 gt 3} | Yes |
| ${5 >= 5} or ${5 >= 5} | Yes |
| ${5 < 3} or ${5 lt 3} | No |
| ${5 <= 5} or or ${5 le 3} | Yes |
![[Note]](../../images/note.png) | Note |
|---|---|
The |
The JSP expression language defines the following literals:
Boolean: true and false. For example,
${task.critical == true}.String: with single and double quotes; " is escaped as \", ' is escaped as \', and \ is escaped as \\.
Null: null
A few predefined functions are also available. The purpose of these will be obvious as stated below.
| Variable | Usage | Description |
|---|---|---|
| formatDate | ${formatDate(project.start)} | Prints a date as per the format specified in the user's profile. Note that "user" here refers to the one viewing the data. This is useful when you have users in different time zones and using different styles for viewing dates. |
| formatDateTime | ${formatDateTime(project.start)} | Prints date and time as per user's profile |
| formatCurrency | ${formatCurrency(project.budget)} | Prints a currency field prefixed with the currency symbol as specified in the company profile. |
You can perform conditional logic in the formulas. This is done
using the syntax ${Some boolean condition
? If true, do this : Else, do this}. Boolean
condition means any condition that evaluates to
true or false. The question
mark (?) indicates the end of the
condition. If the condition is true, expressions to left of the
(:) are evaluated. If the condition
is false, the condition to the right of the colon are evaluated. You
could nest the conditions. This means that the If
true and If false blocks could
themselves be conditions.
The formula ${project.actualCost > (project.estCost *
.9) ? Cost Alert! : Cost OK} would check if the actual cost of
a project exceeds 90% of its estimated cost and would print
Cost Alert! if it is and Cost
OK if it is not.
If the formula has a syntax error it is reported while
adding/editing the formula fields. However, other errors cannot be
determined until runtime and hence you will see ERR!
while
viewing the fields in reports and other places. You can move your
mouse over the icon to see the exact error message. Then edit and fix
your formula.
Assume that we have a project with the following data.
| Field | Data |
|---|---|
| id | 1234 |
| name | Sample Project: Setting up office space |
| start | June 10, 2009 |
| finish | Dec 10, 2009 |
| manager | Mark Duncan |
| budget | 5000 |
| estimatedCost | 4000 |
| actualCost | 3800 |
Based on this data, the table below shows sample formulas, the output of that formula and also provides an explanation.
| Formula | Output (In Browser) | Explanation |
|---|---|---|
| ${project.id * 10} | 12340 | Id is multiplied by 10. |
| project.id * 10 | project.id * 10 | Not enclosed in ${} and hence copied to the output verbatim. |
| ${10* 10} | 100 | Formulas need not use variables. |
| <b>${project.name}</b> | Test Project | You can use html tags. |
| ${project.name + 100} | ERR! | Cannot perform arithmetic on String type. |
| <a href="http://mywikiserver/${task.id}">Wiki Link</a> | Wiki Link | If you use a Wiki, you can create hyperlinks to pages in your Wiki. Notice that we have simply used the HTML Anchor tag to generate a hyperlink. |
| ${project.abcd} | ERR! | Formula contains error. No variable project.abcd exists. |
| ${formatDate:(project.start)} | Jan 10, 2009 | Formats the start date as per the user's date format style. |
| ${project.actualCost > (project.estimatedCost * .9) ? 'Cost Alert!' : 'Cost Ok'} | Cost Alert! | |
| ${formatCurrency(project.budget)} | $ 5,000 | Assuming your currency symbol is $ |