Space banner

   

This documentation supports the 19.11 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 of the documentation at IT Service Management Suite .

Performing calculations on fields in a report

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

Note

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.

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.

https://youtu.be/Sv4SBvwM69s

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

https://youtu.be/YNzRmiZDXXI

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

Important

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

Note

Before using Freehand SQL, ensure that the database function has been defined in Remedy AR System Server. For more information, see  Defining database functions for Remedy Smart Reporting in Remedy AR System Server .

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

      DBFN(‘reverse’,`Description`)
    4. Click Validate.

      Important

      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.

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

Note

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:

ParameterValue
ARJdbc-Script-Execution-StatusError
Use-Enum-Field-RecomputeF

For more information, see Defining database functions for Remedy Smart Reporting in Remedy AR System Server .

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
MS SQL
  • CAST
  • CONVERT
  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • NTILE
  • CHARINDEX
  • CONCAT
  • DATALENGTH
  • LEFT
  • LEN
  • LOWER
  • LTRIM
  • REPLACE
  • REPLICATE
  • REVERSE
  • RIGHT
  • RTRIM
  • SPACE
  • STR
  • STUFF
  • SUBSTRING
  • TRANSLATE
  • TRIM
  • UPPER
  • DATEADD
  • DATEDIFF
  • DATENAME
  • DATEPART
  • DAY
  • MONTH
  • YEAR
  • QUARTER
  • STUFF
  • PATINDEX
Oracle
  • LEAD
  • LAG
  • CONCAT
  • REPLACE
  • INSTR
  • TRANSLATE
  • LPAD
  • MONTHS_BETWEEN
  • ADD_MONTHS
  • LAST_DAY
  • EXTRACT
  • NEXT_DAY
  • TO_NUMBER

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

Comments