Follow

What are calculated fields and how can I set them up?

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.

Calculated fields which have been used include:
  • 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.
Format
The formula that you provide should use standard SQL syntax and should reference the underlying field names as they exist in the PPO database. It, therefore, does require some knowledge of the database structure as well as of SQL.
 
Limitations
Any valid SQL can be used, except literal strings, comments and command delimiters.  These limitations are there to protect the database from SQL injection attacks.
 
Risks
PPO allows any valid SQL in a calculated field and although PPO tries its best to ensure that formula's do not break the PPO instance, we cannot check for all eventualities.  Some of the things that you should keep in mind are:
  • 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. 
General
The following general guidelines may be useful: 
  • 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. 
Examples
Here are some simple formula examples to begin exploring the calculated fields functionality

Simple arithmetic

When doing simple arithmetic the two most important things to keep in mind is to ensure that you are handling NULL values correctly and converting to the correct types.
  • 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))

Ratios

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

Date calculations

  • 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-ups

  • Look-up the project manager: (SELECT Manager FROM tblProjects s WHERE s.ProjectKey = IssueProjectKey)

Aggregates

  • 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))

Users

  • 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) 
To set up a calculated field go to Administration >> Data Fields and follow these simple steps:
KB_datafields_hidden2.jpg
  1. Select the applicable entity;
  2. Select one of the unused data fields;
  3. Complete the caption and description;
  4. Specify the category;
  5. Tick the calculated checkbox;
  6. Enter the formula in the textbox provided (see above examples);
  7. 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.);
  8. 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.
  9. Click the In Use checkbox; and
  10. 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.

FAQ_calculated_step2.png

Additional Considerations
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.
FAQ_calculated_step3.png
All field names in PPO are unique, so you generally do not have to worry about ambiguous field names when tables are joined. The only exceptions are the special fields called Created (the last time the item was updated) and UserKey (the user that updated the item) which are common across all tables. If you want to use these fields in a formula you need to prefix it with the table name (the letters tbl and the entity name) e.g. tblIssue.Created.
  • 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

Was this article helpful?
3 out of 3 found this helpful
Have more questions? Submit a request
Article is closed for comments.
Start a 30 Day Free TrialClick ClickNo Credit Card and No Obligation
Powered by Zendesk