Who is this article for?

Users who want to add calculated fields to Legacy BI reports

Reporting authorities, roles, and record access controls restrict data visibility.

Legacy 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 and Measures are available in the advanced options of Other report. This feature allows users to create new fields and measures, specific to the active report, where the value is derived from values in other fields. 

The new fields are created connecting Fields and Measures to functions. 

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

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