Who is this article for?

Enterprise or Decani users and administrators who want to add calculated fields to Legacy BI reports

No special access or permissions are required

Overview

Legacy BI reports are generated through a third-party tool called JasperSoft. This tool presents an opportunity to create reports that contain child-level information.

Search and reporting apply filters to object data, creating reports that can be viewed as a List, Table, Calendar, Trend, and Chart and even setting up some notifications. 

Calculated fields

A calculated measure applies a formula to selected field values and measures to generate a new report value. The resulting value is calculated at runtime and displayed in the report only. It is not stored as a field in the underlying object.

A calculated measure uses a formula to combine a field value with one or more measures. For example, a Due Date field can be used with a Days Due measure to calculate a value that is displayed in the report.

Calculated Measures are available in the advanced options of an Other report. 

Common measures include:

• Count
• Days Open
• Days Due
• Priority
• Cost
• Score

Generalization of Examples

Please note, the fields and measures described below are examples of commonly used values. For example, any references to a date field, like Date Created might be called Initiated On in a different solution.

Use ASCII quotes

In the following exercises we will create calculated fields and measures. As the calculated fields are built, ASCII single and double quotes will be used. It is important to note that there is a key difference in the following two examples:
["Title"] vs [“Title”]

The first example ["Title"] was copied and pasted from the Report tool and uses the ASCII quotes. The second example [“Title”] was typed directly in Microsoft Word. The quote marks in the second line are not recognized by the Report tool. 

Keep this in mind when cutting-and-pasting from Microsoft Word. Using a different application, like Notepad, is recommended. 

What is the difference between a Measure and a Field?

• A Measure always contains a numerical value.
• Anything else, like text, or a checkbox, is a Field.

Built-In Calculated Measures

Some of the more popular calculated measures are those created using the Elapsed Days function associated to:

• Days Open
• Days Overdue
• Days Until Due

These measures rely on the common fields Due Date, Created On, and Date Closed being mapped to corresponding common fields in a module and need to be defined in all modules that users want to search using these built-in measures.

Training videos

There are many functions available. Below are links to helpful videos showing how to use some of the more popular functions using the Classic UI. New Skills videos are coming soon.

• Percent Of - Useful in Crosstab reports as you can add a column that depicts, per row value, what percentage it is of the group subtotal, the column total, or the overall total.
• Concatenate - Allows you to create a new field that strings together field values as well as user-defined text. This is useful in creating a new "Title" field that can combine many fields, eliminating the need to use a column for every needed field on the report. You can separate each of these fields with user-defined text, such as "Title:", "Date Created:", "Status:", and "Assigned To:". For more information see
• DayName - Returns the day of the week (Sunday, Monday, Tuesday, etc.) based on the designated date field.
• Case - A nested "If" function. You use it to analyze a defined field for specified values, and if they are found, populate the new field with a value you specify. For example, if we had a field that contained the day of the week, like Sunday or Monday, we'd have the new field populated with a "1" for Sunday and a "2" for Monday.
• Elapsed Days - Calculates the number of days between two date fields. One of the date fields can be the (Today) function, so it always uses the date that the report is run.

Calculated Fields are specific to the report in which they are created. If needed, consider using the "Save As" feature of saved searches to create additional reports that include the Calculated Fields.

Continuous improvement of BI search and reporting tools
The JasperSoft reporting tool represents the "Report" option in the classic user interface. This tool is being integrated into the Ideagen Platform to support child-level reporting. Ideagen has already released many of the JasperSoft features, such as charts and report grouping, into native BI search and filters. 

With release 2.64.4, child data reporting will be made available to new customers as well as existing customers who have completed the search index conversion and are utilizing the redesigned user interface. 

As part of this change, child data can be added to both new and existing reports.

Please refer to Unlocking native child data reporting for additional details.