This documentation supports the 20.08 version of Remedy Smart Reporting, which is available only to BMC Helix subscribers (SaaS).

To view an earlier version, select the version from the Product version menu.

Performing calculations on fields in a report

With calculated fields, you can create basic calculations with the fields available in your report. 


You cannot use a calculated field across multiple reports. A calculated field is unique to a single report. If you create a set of calculated fields that you want to use across multiple reports, you can do one of the following things:

  • Request your administrator to include the calculations in the View.
  • Copy the report containing the calculated fields using the copy function. The copied report will contain the new calculated fields.

When an administrator designs a view, they might not create all the variables that you need when writing reports. A view should provide sufficient fields to write the report you need and to use some fields as the basis for more complex calculations.

Best practice

To avoid performance issues, request your administrator to include frequently used calculated fields in a View so that you can use them crossed multiple reports.

The following videos demonstrate how to use a calculated field to compute the age of an incident.

Creating age bucket – Part 1: This video (4:17) demonstrates how to calculate the age of an incident.

Creating age bucket – Part 2: This video (4:22) demonstrates how to create an age bucket for incidents based on their age.

Types of formulas

You can use any of the following formula types to create your calculation:

  • Simple formula—Created with the formula builder button interface. Simple formulas range from simple addition or multiplication to CASE statements.
  • Predefined formula—Based on formula rules defined by an administrator or are a system default.
  • Freehand SQL—Enables you to run and save a Microsoft SQL or Oracle database function, including nested functions. (See List of database functions supported for Freehand SQL.)


Use Freehand SQL for running simple database functions only. For running advanced database functions that have multiple parts (such as over, partition by, and order by in a single function), use the Advanced Authoring option. For more information, see Creating reports with advanced database functions by using Advanced Authoring. For differences between Freehand SQL and Advanced Authoring, see Freehand SQL versus advanced authoring (below).

To create a simple formula

To use Freehand SQL


  • Before using Freehand SQL, ensure that the database function has been defined in Remedy AR System Server. For more information, see 

    Error rendering macro 'link-window'

    Failed to transform the HTML macro template for display. Nested message: The XML content could not be parsed. There is a problem at line 4, column 169. Parser message: Duplicate attribute 'remedy'. at [row,col {unknown-source}]: [4,169]

  • The Remedy Smart Reporting supports the database functions that are defined in Remedy AR System Server. But, the reporting module in Innovation Suite do not support database functions.
  1. On the Data page of a report you are editing, click the + button at the bottom of the field list.
  2. In the Calculated Field window, complete the following steps:
    1. In the Calculated Field Name field, enter a name for your formula.
      This name is the field name in your table and chart output.
    2. From the Formula Type drop-down list, select Freehand SQL.

    3. Select the data type from the Resulting Field Type field. The available options are Auto Detect, Numeric, Text, Boolean, and Date. The default value is Auto Detect.

    4. In the Freehand SQL editor, enter the following syntax to run the database function:

      DBFN(‘<function_name>’,`<argument1>`,`<argument2>`,…`<argument10>` )

      where <function_name> is the name of the database function, and <argument_number> is the input parameter required by the database function. The argument must be the AR System field name for which you are calling this function. You can provide up to 10 arguments for a function. An example of this code is:

    5. Click Validate.


      Ensure that the table of the fields that you are using in the DBFN is marked mandatory in the respective view; otherwise, you will receive the "Ambiguous column name" error. In a view, you can mark only table as mandatory.

    6. Click Save to complete your calculation.
      The calculation is available in a Calculated Fields folder at the bottom of the field list on the Data page.


  • If you are upgrading from Remedy Smart Reporting version 19.02, ensure that the Remedy AR System server administrator reruns the PERFORM-CUSTOM-DB-FUNCTION-CHANGE function on the new version through the java driver. If the PERFORM-CUSTOM-DB-FUNCTION-CHANGE function is already run on version 19.02, the AR System server administrator must first set the following Centralized Configuration parameters and then rerun the PERFORM-CUSTOM-DB-FUNCTION-CHANGE function on the new version:

For more information, see

Error rendering macro 'link-window'

Failed to transform the HTML macro template for display. Nested message: The XML content could not be parsed. There is a problem at line 4, column 169. Parser message: Duplicate attribute &#39;remedy&#39;. at [row,col {unknown-source}]: [4,169]



Consider the following example of using Freehand SQL for Min( SubmitDate ) Over (PartitionBy Assigned Group ):

DBFN('min;over;partition by',3, '#KWD#(', `HPD:Help Desk`.`Required Resolution DateTime`,'#KWD#)',1,'#KWD#(',2, `HPD:Help Desk`.`Assigned Group`,'#KWD#)')

Database functions supported for Freehand SQL

The following table lists some of the Microsoft SQL Server and Oracle database functions that you can execute by using Freehand SQL. The list is not exhaustive and lists only a few of the database functions that AR JDBC supports.

DatabaseSupported functions
  • CAST
  • RANK
  • LEFT
  • LEN
  • STR
  • TRIM
  • DAY
  • YEAR
  • LEAD
  • LAG
  • LPAD

Freehand SQL versus advanced authoring

The following table shows the key differences between FreehandSQL and advanced authoring:

Freehand SQLAdvanced authoring
You can use Freehand SQL for running simple database functions only.You can use Advanced Authoring option for running simple and complex database functions containing multiple parts. For more information, see Examples of how to execute complex database functions having multiple parts.
In Freehand SQL, you can call a function using DBFN only.In Advanced Authoring, you can write any SQL query that is compliant with AR JDBC.

To edit a calculated field

  1. Hover over the calculation in the Field List on the Data page, and click on the drop-down menu.
  2. Select Edit Calculation.

To delete a calculated field

  1. Hover over the calculation in the Field List on the Data page, and click on the drop-down menu.
  2. Select Delete.
Was this page helpful? Yes No Submitting... Thank you