Custom Formulas for Data Fields in Visual Reports

Sometimes, you might need to add fields to a chart based on your own custom formulas. Our 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. This guide article will help you to discover the possibilities of custom formulas, so you that you can bring your reporting to the next level.

Formula Editor

To customize settings open the Set up Report editor. In the Data fields section on the left side press the Add field or formula button at the bottom of the fields catalog. Choose the field you require from predefined list or switch to the Formula tab and input new custom formula.

The formula editor popup contains the following controls:

1. Formula name (required): The name of your formula
2. Formula code (required): The text of your formula
3. Predefined Formulas tab: A list of selectable formula templates
4. Your Formulas tab:  A list of formulas you have used before

Please Note: The process of formula creation should be completed by pressing the Save button. Otherwise, the formula you created will not be saved.

Find a formula in the fields catalog and click on it to edit formula:

Available data types and basic functions

There are four primitive data types which can be used in formulas: number, date, text and boolean.

The following numeric functions are available for numeric formulas: "+" for addition, "-" for subtraction , "*"  for multiplication  and  "/" for division.

For example, the progress of a User Story can be calculated with: IIF(Effort > 0, 100 * ((Effort - EffortToDo)/Effort), 0)

Text values can be converted to numbers using the NUMBER function. For example, NUMBER("541") will give us 541.

The following date functions are available:

• TODAY can be used to get the current date
• "+"  or "-" can be used to add or subtract days from the operational date. For example: TODAY - 2 days
•  Text values can be converted to a date using the DATE function. For example: DATE(01 January 2006) or DATE("1 Jan 2016")
• DAYS can be used to compare dates by days. For example: IIF(EndDate, DAYS(EndDate - CreateDate) will show how many days this bug was in the 'Open' state.

Text values can be concatenated using "+".  Numbers and dates can be converted to text using the TEXT function. Your desired format (date formats list and number formats list) can be provided as the second parameter. For example: TEXT(ID) + '-' + TEXT(CreateDate, 'MMM yyyy') + '-' + TEXT(Effort, 'n'). 

Basic boolean operators and functions are AND, NOT, OR, "=" and "!=". Comparison of values for numbers, dates and text using ">=" , "<=" , ">" , "<" , "=" , "!=", "IN" (text type only) and "CONTAINS"(text type only) gives a boolean value and can be used for a boolean expression. Example: NOT((EntityState.IsInitial OR EntityState.IsFinal) AND TimeSpent > 0 AND NAME CONTAINS "LOGIN").

Using autocomplete for adding formula

Place the cursor in the formula code area. An autocomplete popup will appear automatically. Start typing a formula or name to find suitable fields and functions. Understanding the Targetprocess Domain Model and having solid knowledge of Entities and Relations will be quite helpful for creating formulas.

You can get rid of autocomplete suggestions by pressing the "ESC" button. Press "CTRL" + "Space" or "ALT" + "Space" to show the suggestions again.

Conditional functions

Conditional functions are pretty useful for creating the kind of advanced formulas where the desired value is dependent on some conditions.

1. IIF(<boolean expression>, <value when condition is positive>, <value when condition is negative>). Example: IIF('Urgent' in Tags, 'Urgent', 'Not Urgent')
2. IFNONE(<field or expression>, <default value>) is used when you are required to provide some default value for a field that doesn't exist. For example: IFNONE(Name, 'None') will show the field's Name, if it exists. Otherwise, it will return 'None'
3. CASE  WHEN can be used for multiple conditions. For example:
             CASE 
                   WHEN EntityState.IsInitial THEN "Open"
                  WHEN EntityState.IsFinal THEN "Done"
                  ELSE "In Progress"
             END

Coloration functions

Coloration can be used to assign colors to fields or field values.

Assign color to a field

A specific color can be assigned to a field. It helps to split data on the combined charts that show multiple fields of the same entities together. Format is the following:

COLOR(FieldName, ColorName)

For example we would like display entities grouped by Creation Date and End Date. It helps to compare number of new entities vs done ones weekly. Our goal is to highlight done entities with green color. To make it possible let's define new field named Completed On with the following formula:

COLOR(EndDate, Green)

And then put the field Completed On to the horizontal axis of the chart instead of End Date. We'll get something like this:

Assign colors to values of a field

Specific colors can be defined for the values of text fields. Say you have a field named FieldName with values Value 1, Value 2, Value 3. To assign different colors to these values you use a formula with the following format:

COLOR(FieldName, "Value 1": ColorName1, "Value 2": ColorName2, "Value 3": ColorName3, ...)

Say, we'd like to build chart showing number of entities created weekly. On the same chart we'd like apply colors according to current state of an entity. We'd like to show entities in Done state as green and entities in any other state in red. Here's how the formula can be defined.

First, we will split all possible states into two groups. Done state has Final attribute. We'll give "Completed" alias to all final states and "Open" to all other ones using IIF operator:

IIF(EntityState.IsFinal, "Completed", "Open")

Second, we'll put result of calculation into conditional coloring rule. We'll define a new field Is Completed with custom formula:

COLOR(IIF(EntityState.IsFinal, "Completed", "Open"), "Completed": Green, "Open": Red)

And third, we'll put this field to the COLOR axis of the chart. Here is the result:

Quite similar formula helps to highlight entities of different types with your custom selected colors. Define new field Colored Type with the following formula:

Color(EntityType.Name, "UserStory":Green, "Task":Blue, "Bug":Red, "Request":Yellow)

Available colors

Full list of available and supported colors is available here: Recognized Color Keyword Names for SVG Format

Custom Fields

Custom fields are treated as one of the following types: Date, Text, Number, or MultiText. The typed custom field value can be retrieved using these corresponding functions: CustomValues.Text, CustomValues.Number, CustomValues.Date, or CustomValues.MultiText

You can use text custom fields in conditional statements. For example:

IIF(CustomValues.Text("My") CONTAINS "Urgent", "Urgent", "Can be postponed")

Aggregations for inner entity collections

Aggregations can be applied for child collections of entities when it is required to calculate a field by sum, avg, max, min or count. Calculated aggregation can be filtered using the where clause. A lot of examples are available in the predefined formulas section. You can see some of these below:

IIF(Feature.Bugs.Count() > 0, 100 * (Bugs.Count() / Feature.Bugs.Count()), 0)

 – can be used when you want calculate the percent of story bugs in comparing to all feature bugs

IIF(Bugs.Count() > 0, 100 * (Bugs.Count(EntityState.IsFinal) / Bugs.Count()), 100)

 – it is the percent of closed bugs in user story, feature or project

IIF(Project.Features.Where(EntityState.IsFinal).Sum(Effort) > 0, 100 * (Effort/Project.Features.Where(EntityState.IsFinal).Sum(Effort)), 0)

 – stands of percent of feature effort in comparing to all completed features effort

Selecting field from inner entity collections

Fields of inner entity collections can be selected to take part in visualization. The collection field values can be retrieved using Select and filtered using Where. Examples:

All of a Project's unique Epic names can be retrieved using:

Epics.Select('#' + TEXT(Id) + ' ' + Name)

You can retrieve tags for Bugs separately by using:

TagObjects.Select(Name)

It helps to build a distribution of Bugs by each tag like this:

Advanced example: take name of linked blockers for an entity:

RAW_TEXT(MasterRelations.Where(RelationType.Name = "Blocker").Select(Master.Name))

Additional Cases

Use special functions to create reports using the formulas compatible with previous versions of graphical reports.

RAW_TEXT(<Targetprocess DSL text expression>) for text or boolean formulas. For example:

RAW_TEXT("Test")

Strip HTML tags from Rich Text formatted strings (Descriptions, Rich Text custom fields):

RAW_TEXT(Description.ToPlainText)
RAW_TEXT(CustomValues.Text("My Rich Text Field").ToPlainText)

RAW_NUMBER(<Targetprocess numeric expression>) for numeric formulas. For example:

RAW_NUMBER(Int32.Parse("45"))

RAW_DATE(<Targetprocess date expression>) for using text or boolean formulas. For example:

RAW_DATE(DateTime.Parse("3 September"))

RAW_ARRAY_TEXT(<Targetprocess text array expression>) for using text or boolean formulas. For example:

RAW_ARRAY_TEXT(AssignedEfforts.Select(Assignable.Name))