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:

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.

2017-11-11_2147

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.

* Views and Visual Reports can be embedded to Dashboards and shared with external users.

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

2017-11-08_2340

Example of usage

2017-11-08_2342

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:

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-formula

    2017-11-08_2244

  • Add field to chart definition

    2017-11-08_2257

Example of usage

2017-11-08_2250

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

2017-11-08_2317

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 chartsIn-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.

Use Metrics carefully. They affect system performanance and can make whole application work slower. Metrics have been designed and released as a replacement for Calculated Custom Fields feature. We recommend prefer Metrics over Calculated Custom Fields whenever possible.

Example of configuration

  • Add system Custom Field

    2017-11-08_2325

    2017-11-08_2326

  • Add Metric

    2017-11-08_2327

  • Wait until recalculation is completed

    2017-11-08_2329

Examples of usage

Calculated values in Custom Fields section on entity details view:

2017-11-08_2331

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

2017-11-08_2333

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

2017-11-08_2334

Calculated values can be presented within Tabular Reports:

2017-11-08_2336

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

2017-11-08_2339

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.

Use Calculated Custom Fields carefully. Avoid using references to them in Visual Encoding rules. The fields affect system performanance and can make whole application work slower. Please use Metrics instead of Calculated Custom Fields whenever possible.

Example of configuration

2017-11-08_2304

2017-11-08_2305

Examples of usage

Calculated values in Custom Fields section on entity details view:

2017-11-08_2307

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

2017-11-08_2308

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

2017-11-08_2311

Benefits:

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

Known limitations:

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

2017-11-08_2222

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 Timeline 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

    2017-11-08_2256

     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

    2017-11-08_2237

Example of usage

2017-11-08_2233

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

Still have a question?

We're here to help! Just contact our friendly support team

Find out more about our APIs, Plugins, Mashups and custom extensions. Join our community of passionate users and even discuss directly with our developers.

Try for free

Account url: *.tpondemand.com
How many people would be using Targetprocess?
  • Myself
  • 2–20
  • 21–100
  • 101–1000
  • 1000+
By clicking 'Log into Targetprocess' you agree to our Terms of service and Privacy policy