Home > Knowledge Base > Reports > Using calculated columns

Using calculated columns

28.12.2020

Reports

#calculated columns # reports

Applies to version 2020.1.3; author: Michał Kastelik

Calculated columns enable you to display additional information in WEBCON BPS reports. They are created by adding a new column in the report and then inserting a SQL formula. They allow the report to be supplemented with additional fields that are not present in an application. A calculated column only works within a given report.

Creating calculated column

After opening the report in which you want to add a column, press the settings button, and then go to the "Columns" tab.

Then, press the button "Add calculated column."

A new column configuration window will be displayed.

The language in which the formula should be defined is SQL. In the calculated column, use the field names from the database (e.g., WFD_AttText1) - it will return the value from the form field for a given instance.

Fig.3 Selecting data type returned in the column

Calculated columns can have one of the defined types:

Text - the result of the formula will be a text value,
Floating point number - the formula will return a floating-point number,
Date - the result will be a value of the data type
Lamp - depending on the value, the formula will be red, yellow, or green,
Link - the formula will return a link, e.g., to an item.

List of fields used in the formula

Before you start creating a formula for a calculated column, it's a good idea to download the field list in your application. To do this, open one of the workflow instances, and press the "Admin" button. If the "Admin" button does not appear on the application form, you need to obtain additional application privileges from the platform administrator.

Fig.4 Enabling the administrative mode on the form

Once you have opened the admin mode, select "HTML template" from the menu.

Fig.5 Downloading the HTML template containing the form fields' names

The downloaded template will contain database fields for the form fields and some additional system fields. An example template looks like this:

Fig.6 Example of downloaded HTML template with database field names

The list of fields can also be found in WEBCON BPS Designer Studio. To access it, expand the process tree in the application, and press "Form fields." Please note that this view will not display system form fields.

Fig.7 Preview of the database fields in Designer Studio

Additionally, the list of form fields of a given application and the names of their fields in the database is also available in the rules editor (when switched to the advanced mode).

Fig.8 Enabling advanced mode in the rule editor

Example of calculated column formulas

The formula has to be written in SQL, using form fields or system fields.

Type Lamp

The column returns colors depending on the value:

Red - when the formula value = 0,
Yellow - when the formula value = 1
Green - when the value is 2

The result of applying the formula

Fig.9 The effect of using a calculated column of the lamp type

Formula's code in the editor:

case

when dbo.clearwfelem (WFD_AttChoose1) = 'Low' then 2

when dbo.clearwfelem (WFD_AttChoose1) = 'High' then 0

end

The whole formula checks if the priority is set to "Low" and then assigns the lamp's value as 2 (green). When the priority is high, the lamp value takes the value 0 (red color).

The example shown here refers to a column value for presentation purposes. In environments, it is recommended to use the ID value in queries.

Text type

The formula will return a text value. It allows you to use a text description, e.g., for explaining the priority or urgency of a task.

In the formula below, the column contains an additional priority. It is assigned based on the difference between the current date and the deadline for completing the task. The list of items can be sorted by this column.

case

when getdate () - WFD_AttDateTime1> 5 then '1 Priority'

when getdate () - WFD_AttDateTime1> 3 then '2 Priority'

when getdate () - WFD_AttDateTime1> 1 then '3 Priority'

end

Link type

Using a formula in this type of column, you can create links that open a static page link, e.g., instructions. You can also open associated elements based on the form data, such as a client card or an employee profile.

Fig.12 The effect of using a link column

Example of a formula opening a specified page in a new window:

'link:https://www.webcon.com;displayname:WEBCON WebPage;target:_blank'

Using the built-in formula 'StartElementInNewWindows (),' you can create a link that starts a specific workflow and assigns values in the form.

The code below creates a column with a link that starts an item in a given workflow and assigns a task to the user green.adm

Fig.13 The effect of applying a column that starts an element

'StartElementInNewWindow('+CAST(wf_id as varchar(12)) +', '+CAST(dtype_id as varchar(12)) +', "Start task for admin", "AttLong2=Task created from admin user&AttChoose3=green.adm" )'

In a link formula, you can also use a function that opens a specific element in a new window with the function' OpenElement (). '

Summary

Calculated columns let you add new columns to reports wherever they are necessary without changing anything in Designer Studio. You can also sort and filter reports with them.