This documentation supports the 19.02 version of Remedy IT Service Management Suite.

To view the latest version, select the version from the Product version menu.

Calculations

Calculated fields allow you to create basic calculations with the fields available in your report. 

Note

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

  • 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 as well

Why Use Calculated Fields?

When an administrator designs a View they may not create all the variables that you are likely to need in the course of your report writing. The intention of a view is to provide you with sufficient fields for you to be able to write the report you need and to use some of these 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.

DescriptionVideo
Creating age bucket – Part 1: This video demonstrates how to calculate the age of an incident.

https://www.youtube.com/watch?v=Sv4SBvwM69s&amp?rel=0

Creating age bucket – Part 2: This video demonstrates how to create age bucket for incidents based on their age.

https://www.youtube.com/watch?v=YNzRmiZDXXI&amp?rel=0

Simple Formula

Using a pre-defined formula you can add calculation to your report that will be based on formula rules defined by an administrator or are system default.

 Click here for instructions...
  1. Click the + button at the bottom of the field list in the Data step of the report builder.
  2. The Calculated Field window will now appear. You will need to define the following:
    1. Provide a name for your formula in the Calculated Field Name box. This will be used as the field name in your table and/or chart output.
    2. Select Pre-Defined from the Formula Type drop down list.
    3. Choose the formula you wish to apply – the selection is specific to the type of database that your data is sourced from.
    4. You will now be provided with options to complete your calculation. Click on each parameter to define it.
    5. Provide a value using one of the options provided. These will vary depending on the type of value required.
  3. Click Save to complete your calculation.
  4. Your calculation will now be available in a folder called calculated fields at the bottom of the field list in the Data step.

Freehand SQL

By using Freehand SQL, you can run and save a Microsoft SQL or Oracle database function, including nested functions. For the list of database functions that you can execute using Freehand SQL, 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, order by, and so on in a single function, use the Advanced Authoring option. For more information, see Advanced Authoring.
    For differences between Freehand SQL and Advanced Authoring, see Freehand SQL Vs Advanced Authoring.
  • You can not use Freehand SQL for executing Postgres SQL database functions.

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 .

 Click here for instructions...
  1. Click the + button at the bottom of the field list in the Data step of the report builder.
  2. In the Calculated Field window, perform the following steps:
    1. Provide a name for your formula in the Calculated Field Name box.
      The formula name is used as the field name in your table or chart output.
    2. Select Freehand SQL from the Formula Type drop-down list.
    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.

      <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. For example, DBFN(‘reverse’,`Description`).

    4. Click Validate.

      Important

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

    5. Click Save to complete your calculation.
    Your calculation will now be available in a folder called Calculated Fields at the bottom of the field list in the Data step.


List of database functions supported for Freehand SQL

The following table lists some of the Microsoft SQL Server and Oracle database functions that you can execute using Freehand SQL:

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

Note

The above list is not exhaustive and lists only few of the database functions supported by AR JDBC.

Freehand SQL Vs 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 AR JDBC compliant SQL query.

Edit a Calculated Field

To edit a calculated field:

  1. Hover over the calculation in the Field List of the Data step and click on the drop down menu to open it
  2. Select the Edit Calculation option


Delete a Calculated Field

To delete a calculated field:

  1. Hover over the calculation in the Field List of the Data step and click on the drop down menu to open it
  2. Select the Delete option


Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Andreas Wiencek

    "Ensure that the table of the fields that you are using in the DBFN is marked as mandatory in the respective view, ..."

    This seems to limit the use cases of this feature immensely.

    Aug 26, 2019 03:15
    1. Vrishali namdev Galinde

      Hello Andreas,


      Thank you for your comment.

      The reason we need to mark the table as mandatory is because, otherwise the query may get the same column name in more than one tables and that leads to the Ambiguous column error.

      An alternative to this would be using the Advanced Authoring option wherein we specify the form name of that field directly in the query.


      Regards,

      Vrishali

      Aug 26, 2019 04:34