Calculated fields are a powerful feature within PPO that allow you to calculate the value of a field on the basis of a formula. Although this feature can be very useful, it should be used with care.
- Net present value (NPV);
- Gross profit % per project;
- Balance of budget available;
- Automatic prioritisation ranking;
- Schedule Performance Indicator (SPI);
- Cost Performance Indicator (CPI);
- Return on investment (ROI); or
- The number of days an issue has been open.
- There is a performance impact, especially when you are referencing other tables or doing aggregates. On large instances, this impact could be considerable. If you experience slow page loads, complex calculated fields are usually to blame. Contact the PPO Support Desk to assist in optimising the calculation or adding appropriate indexes.
- If you reference other fields in a calculation and you then take that field out of use or change its field type, the calculation is likely to break. Therefore, if you have many calculated fields, consider the impact on calculated fields before making any changes.
- Complex calculated fields are difficult for users to understand. Make sure that you put a good description in the field details so that users can understand how the values were derived.
- Fields may contain NULL values, especially in the case of custom fields. It is usually a good idea to explicitly convert NULL values to 0 when using the field in a calculation by using the syntax ISNULL(P01,0);
- When working with custom fields, it is generally necessary to convert the field to a type that is compatible with the field type used in PPO. For example in the case of a currency field in PPO, you should convert the field to a money field e.g. CONVERT(money,C01). When working with decimals, you should use CONVERT(decimal(17,2),P01). For integers, you can use CONVERT(int,P01)
- When calculating percentages or doing any division, you should check that the denominator is not 0 (or NULL). You can do this with a CASE statement e.g. CASE WHEN C02 = 0 THEN 0 ELSE C01/C02 * 100 END. When incorporating the rules above, the full statement would be CASE WHEN ISNULL(C02,0) = CONVERT(money,0) THEN 0 ELSE CONVERT(money,ISNULL(C01,0)) / CONVERT(money,ISNULL(C02,0)) * 100 END
- When referring to other tables within a calculation, it is usually prudent to fully qualify field names, especially when referring to fields that are used on all tables like MasterKey and Created. e.g. CONVERT(money,ISNULL((SELECT SUM(c.CostBudget) FROM tblCost c WHERE c.CostProjectKey = tblProjects.ProjectKey AND c.MasterKey IS NULL),0))
- A calculated field cannot refer to another calculated field. If you, for example, want to calculate both a variance field and a variance percentage field, the variance percentage field has to recalculate the variance.
- Subtract currency fields: CONVERT(money,ISNULL(C01,0)) - CONVERT(money,ISNULL(C02,0))
- Add decimal fields: CONVERT(decimal(17,2),ISNULL(P01,0)) + CONVERT(decimal(17,2),ISNULL(P02,0))
Remember to check for division by 0 errors when calculating ratios.
- Simple ratio based on currency fields: CASE WHEN ISNULL(C02,0) = CONVERT(money,0) THEN 0 ELSE CONVERT(money,ISNULL(C04,0)) / CONVERT(money,ISNULL(C02,0)) * 100 END
- Cost variance %: CASE WHEN ISNULL(C02,0) = CONVERT(money,0) THEN 0 ELSE (CONVERT(money,ISNULL(C02,0)) - CONVERT(money,ISNULL(C04,0))) / CONVERT(money,ISNULL(C02,0)) * 100 END
- Last updated: Created
- Number of days between the date on which a record was last updated and today: DATEDIFF(day,Created,GetDate());
- Task lag (difference in days between planned and actual start date): ISNULL(DATEDIFF(d,TaskFrom,ActualTaskFrom),0)
- Days overdue (difference in days between issue follow-up date and current date): -(CASE WHEN I01 < GETDATE() AND IssueStatus NOT IN (466) THEN DATEDIFF(d,I01,GETDATE()) ELSE 0 END)
Update / creation history
- Date on which project was created: (SELECT MIN(Created) FROM tblProjects s WHERE ISNULL(s.MasterKey,s.ProjectKey) = ISNULL(tblProjects.MasterKey,tblProjects.ProjectKey))
- Captured by (person who initially added a project): (SELECT u.EmployeeKey FROM tblUsers u WHERE u.UserKey = (SELECT TOP 1 UserKey FROM tblProjects s WHERE ISNULL(s.MasterKey,s.ProjectKey) = ISNULL(tblProjects.MasterKey,tblProjects.ProjectKey) ORDER BY s.Created))
- Updated by (person who last updated the record): (SELECT TOP 1 EmployeeKey FROM tblUsers u WHERE u.UserKey = tblProjects.UserKey)
- Look-up the project manager: (SELECT Manager FROM tblProjects s WHERE s.ProjectKey = IssueProjectKey)
- Total budget for a project: CONVERT(money,ISNULL((SELECT SUM(c.CostBudget) FROM tblCost c WHERE c.CostProjectKey = tblProjects.ProjectKey AND c.MasterKey IS NULL),0))
- Check whether or not an employee is a user: (SELECT CASE WHEN EXISTS (SELECT 1 FROM tblUsers WHERE tblEmployees.EmployeeKey = tblUsers.EmployeeKey AND tblUsers.Active = -1) THEN -1 ELSE 0 END)
- Select the applicable entity;
- Select one of the unused data fields;
- Complete the caption and description;
- Specify the category;
- Tick the calculated checkbox;
- Enter the formula in the textbox provided (see above examples);
- Select the field type (The field type will determine in what form your answer will be provided. Special care should be taken to select a field type that is compatible with the entered formula.);
- Specify whether the field should be hidden or not. Making a field hidden means users will not be able to see the field from the front-end.
- Click the In Use checkbox; and
- Determine whether you need the field to appear as a column on the list page (include in list).
Please be aware that if there is a problem with your formula PPO will prevent you from saving the calculated data field and will report an error in the formula.
If you have a decent knowledge of SQL functions you can write the easier formulas yourself. Complex calculations that require assistance from the Support Team are available and can be requested by logging a support call from your PPO Home Page.
The following tips and guidelines may be of assistance when creating your own formulas:
- You can determine the actual field name for a field (as opposed to its caption) by referring to the PPO Field Name on the Data Fields >> List page. It is the PPO Field Name that needs to be used in formulas.
- The maximum length of the formula is 255 characters. If you need more complicated functionality, it is recommended that you create a user-defined function on the database and call that instead.
- If there is a possibility that the value of a field that you reference in your formula could be NULL it is recommended that you use the COALESCE function to provide a default value.
- If you use formulas that are not wholly dependent on the data of the underlying issue, e.g. GETDATE(), you should have an understanding of the caching that is done in PPO to ensure that the formula has the intended results. Contact the PPO Support Team for more information on this.
- Although the validity of the formula is checked against the table at the time that it is created and subsequent field changes are checked to ensure that it does not impact on the validity of a formula, care should be taken when using formulas.
- Ensure that the field type that you choose for the calculated field is compatible with the result generated by your formula.
- When working calculated fields for currency and integers, you have to explicitly convert them since they are defined as text (varchar) fields in the database.
- The underlying data values for RAG indicators are as follows: 0= Grey, 1=Red, 2=Amber, 3=Green.
For more information please consult the Technical Fact Sheet