Custom Calculations and Formula Expressions

Targetprocess is quite powerful with its ability to do advanced calculations. The system can evaluate values using multiple source fields and properties, common math, and string manipulation operations. As a result, you get calculated numeric, date, boolean, text values. They can be displayed on views, presented within tabular and visual reports, saved to custom fields, and exported to external systems.

There are multiple features in Targetprocess that are capable of supporting custom calculations:

• Summary Columns in Tabular Reports
• Custom Formulas and In-Chart Calculations in Visual Reports
• Custom Formulas in Metrics
• Calculated Custom Fields
• Calculations performed within REST API
• Custom Unit Mashups

Similar results may be achieved within different features, and each feature has its own benefits and known limitations. In this article, we describe which features will be more efficient for particular use cases. We'll compare existing calculation capabilities one by one, and we'll use the same use case for comparison.

Use Case

In Targetprocess, Bugs can be attached to a Feature. Effort for Bugs is estimated numerically. It is possible to calculate and show the total effort of related bugs per each feature.

Next, we'll describe how this calculation can be performed within every supported method.

General recommendations for choosing an efficient method of calculation

Here is where you might need to get results of the custom calculations:

• In tabular reports only: use Summary Columns in Tabular Reports, or Metrics
• In visual reports* only: use Custom Formulas and In-Chart Calculations in Visual Reports, or Metrics, or Calculated Custom Fields
• In views* only: use Metrics, or Calculated Custom Fields, or Custom Unit Mashups
• In both views and reports: use Metrics, or set up independent calculations for views and reports
• In exported sets of data only: use Calculations performed within REST API, or Metrics, or export Views / Tabular reports / Visual reports and configure calculations using exact methods supported by them.

Summary Columns in Tabular Reports

Summary columns can be used to count totals, and to calculate maximum, minimum, and average values within Tabular Reports.

Example of configuration

Example of usage

Benefits:

• Authorized users can add and modify summary columns quickly and easily. They don't need help from an Administrator. There is a rather friendly interface with configuration settings.
• Support for basic formulas such as Count, Sum, Min, Max, Avg.
• Allows you to define and use Filters in calculations.

Known limitations:

• The number of supported collections, fields and operators is limited

More information: Summary Columns in Tabular Reports

Custom Formulas and In-Chart Calculations in Visual Reports

Visual Reports Editor has a built-in formula editor which allows you to create formulas based on a variety of mathematical, text, and date operations.

Example of configuration

• Add field with custom formula

• Add field to chart definition

Example of usage

Here's some calculated values in a spreadsheet generated using the Export Visual Report to CSV file feature:

Benefits:

• Authorized users can add and modify summary columns quickly and easily. They don't need help from an Administrator. There is a rather friendly interface with configuration settings.
• Many useful fields and formulas are already pre-saved within the Visual Reports Editor. Most of the time, you won't even need to craft your own formulas. Just reuse an existing field instead!
• Syntax code highlight in formula expression is supported.
• Support IIF and CASE / WHEN / THEN operators.

Known limitations:

• Creation of new custom formula requires advanced skills in Targetprocess data model and reporting engine.

More information: How to add fields or custom formulas to charts, In-Chart Calculations Basics

Custom Formulas in Metrics

Custom formula metrics allow you to populate custom fields of the entities in your system within values calculated according to formula expressions.

Example of configuration

• Add system Custom Field

• Add Metric

• Wait until recalculation is completed

Examples of usage

Calculated values in Custom Fields section on entity details view:

Calculated values on customized cards on board, list, roadmap views:

Calculated values in the spreadsheet generated within Export View to CSV file feature:

Calculated values can be presented within Tabular Reports:

And you can sort entities by calculated values in Tabular Reports:

Benefits:

• Results are saved to existing Custom Fields. They are displayed in detailed views, on cards on board views and in columns on list views, in Custom Tabular Reports, in Visual Reports. It is possible to export calculated values to external systems.
• Supported for Epics, Features, User Stories, Tasks, Bugs, Requests, Test Cases, Test Plans, Test Plan Runs, Impediments, Projects, Releases, Iterations, Builds, Times.
• You can use values of Custom Fields and results of evaluation of other Metrics in formulas.
• You can sort and group items by calculated values in views.
• You can preview results of calculations in the Settings > New Custom formula Metric page.
• Support IIF operator.

Known limitations:

• Only Administrators are authorized to add and modify settings of Metrics. Configuration of new formulas and metrics requires advanced skills in Targetprocess data model.
• Users cannot change settings of calculation on the fly.
• You have to create blank Custom Fields as holders for your calculated values in advance.
• Not supported for Users, Teams, Team Iterations.
• Metrics are recalculated only when data change. You cannot schedule recalculations. As a result you cannot use references to current moment such as Now, Today, last N days / weeks / months / years in custom formulas for Metrics.
• Do not support CASE / WHEN / THEN operator. Use nested IIF operators instead.

More information: Configuring Metrics

Calculated Custom Fields

It is possible to create custom fields that are evaluated according to specified formula just in time when you browse an entity with these fields. To do so, create a Custom Field with a Calculated field type and specify calculation formula.

Example of configuration

Examples of usage

Calculated values in the Custom Fields section on entity details view:

Calculated values on customized cards on board, list, roadmap views:

Calculated values in the spreadsheet generated within Export View to CSV file feature:

Benefits:

• You can use references to current moment such as Now, Today in custom formulas for Calculated Custom Fields.

Known limitations:

• Only Administrators and Process Admins are authorized to add and modify settings of Calculated Custom Fields. Configuration of new formulas requires advanced skills in Targetprocess data model.
• Users cannot change settings of calculation on the fly.
• You cannot results of Calculated Custom Fields in formulas.
• You cannot see calculated values in Custom Tabular Reports.
• You cannot sort / group items by calculated values in views.
• Values are not shown on board views with multiple entity types.
• Not supported for Times.
• Do not support CASE / WHEN / THEN operator. Use nested IIF operators instead.

More information: Calculated Custom Fields

Calculations performed within REST API

REST APIs v.1 / v.2 are public services that allows you to quickly extract data from Targetprocess. They can be easily used with any programming language and development platform. Data is extracted in JSON (API v.1, v.2) or XML (API v.1) formats.

Examples of API request configuration

POST /api/v1/Features?include=[Id,Bugs-Effort-Sum]&format=json
POST /api/v2/Feature?select={id,bugsEffort:Bugs.Sum(Effort)}

Example of API request output

Benefits:

• Reporting and data exporting activities can be automated. As a result they can be performed without need of human interaction.
• Reporting and data exporting activities can be scheduled to afterhours. As a result you reduce load on the system and whole application works faster during peak hours.
• The API calls are very flexible in terms of customization and configuration.

Known limitations:

• Creation of new REST API scripts requires advanced skills both in general software development and in Targetprocess data model.
• It is not possible to apply filters by calculated values within API v.1, v.2 calls.
• API v.2 is primarily intended for internal usage of Targetprocess application. Its support for customers is limited. Use it at your own risk. Don't hold us responsible for any future damage. Consider using API v1 for more stable public surface.

More information: Appends and Calculations in API v.1, Custom Calculations in API v.2

Custom Unit Mashups

Custom units are mashups (add-ons) that tweak the Customize Cards feature for your Board, List, and Roadmap views. All mashups are scripts written in Javascript language. You can create a mashup from scratch, but we recommend using an existing mashup as a foundation.

Custom unit mashups retrieve data using REST API v.2 selectors and expressions.

Example of configuration

• Add Mashup

   var units = [{
   id: 'feature_bugs_effort',
   name: 'Bugs Effort',
   classId: 'tau-board-unit_type_feature_bugs_effort',
   types: [types.FEATURE],
   template: ['<div class="tau-board-unit__value"><%= String(this.data.bugsEffort || 0) %></div>'],
   sizes: [sizes.M, sizes.L, sizes.XL, sizes.LIST],
   sampleData: {nameLength: 5},
   model: 'bugsEffort:Bugs.Sum(Effort)'
   }];

• Customize Cards on view

Example of usage

Benefits:

• The mashups are very flexible in terms of customization and configuration.
• There is a library with plenty of mashups ready to use.

Known limitations:

• Creation of a new mashup requires advanced skills in both general software development and in Targetprocess' application engine.
• Users cannot change settings of calculations for their particular views on the fly.
• You cannot sort entities by calculated values.
• Calculated values cannot be exported.

More information: Custom units