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.

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.

Related topic

Troubleshooting-database-functions-defined-in-AR-System-Server-for-use-in-Freehand-SQL

Creating-reports-with-advanced-database-functions-by-using-Advanced-Authoring

Examples-of-using-predefined-functions-in-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.

icon_play.pnghttps://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.

icon_play.pnghttps://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

  1. On the Data page of a report you are editing, click the + button at the bottom of the field list.
    CalculateButton.png
  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 Simple.
    3. Select the data type from the Resulting Field Type field. The available options are Numeric, Text, Boolean, and Date.

    1. Use the formula buttons and field drop-down list to add components to your calculation.

      You cannot type directly into the calculation area of the window, each part of the formula needs to be added using buttons. Remedy Smart Reporting does not permit you to select combinations of variables that are incompatible; therefore, options that are grayed out become active as you add in elements. The following example shows a sample CASE WHEN formula that you can construct for calculating total number of major incidents:

      SUM ( CASE WHEN KPI Name = 'HPD:INC:Major_Incidents' THEN Value END )

      Note

      If you want to type your calculation, use the Freehand SQL option discussed below.

      Calculated fields.png

    2. Click Validate to ensure your calculation is not missing any components.
    3. Click Save.
      The calculation is available in a Calculated Fields folder at the bottom of the field list on the Data page.


To use a predefined formula

  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 Pre-Defined.
    3. From the Functions list, select the formula you want to apply.
      The selection is specific to the type of database from which your data is sourced.
      You are provided with options to complete your calculation.
      Predefined.png
    4. Select the Resulting Field Type: Dimension or Metric.
    5. For each value, click Define Value, complete the fields that appear, and click OK.
    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.

The following tables lists the Pre-defined formulas that you can use for your calculated fields.

Formula

Description

Character Index/Position, ARJDBC

This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

Convert CLOB to Varchar (Oracle), ARJDBC

This function converts an expression of type CLOB to an expression of type Varchar.

Convert nText to Varchar (MSSQL), ARJDBC

This function converts an expression of type nText to an expression of type Varchar.

Convert String to Number, ARJDBC

This function converts an expression of type String to an expression of type Number.

Current Date, ARJDBC

This function returns the current date and time. The date is returned in the DD/MM/YYYY format.

Date Difference - In Days, ARJDBC

This function returns the difference between two date values, in days.

Date Difference - In Seconds, ARJDBC

This function returns the difference between two date values, in seconds.

Date, ARJDBC

This function returns the current system date.

DateAdd, ARJDBC

This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.

Day, ARJDBC

This function returns the day of the month from a specified date.

First Day of Month, ARJDBC

This function returns the day of the month.

First Day of Quarter, ARJDBC

This function returns the day of the quarter.

First Day of Year, ARJDBC

This function returns the day of the year.

Hour, ARJDBC

This function returns the hour part of the specified time.

Length, ARJDBC

This function returns the number of characters of an input string.

List Aggregation, ARJDBC

This function transforms values from a group of rows into a list of values that are delimited by a configurable separator.

Lower, ARJDBC

This function returns a character expression after converting uppercase character data to lowercase.

LPAD, ARJDBC

This function left-pads a string with another string, to a specified length.

LTRIM, ARJDBC

This function returns a character expression after removing the leading blanks from the specified string.

Minute, ARJDBC

This function returns the minute part of the specified time.

Month Name, ARJDBC

This function returns the name of the month from a specified date.

Month Short Name, ARJDBC

This function returns the name of the month in short format from a specified date. 

Month, ARJDBC

This function returns an integer that represents the month of the specified date.

Quarter, ARJDBC

This function returns the quarter of the year for a specified date.

Replace String, ARJDBC

This function replaces all occurrences of a specified string value with another string value.

RPAD, ARJDBC

This function right-pads a string with another string, to a specified length.

RTRIM, ARJDBC

This function returns a character expression after removing the trailing blanks from the specified string.

Seconds, ARJDBC

This function returns the seconds part of the specified time.

SubString, ARJDBC

This function returns part of a character for given length from start position.

Upper, ARJDBC

This function returns a character expression after converting lowercase character data to uppercase.

Week Day Name, ARJDBC

This function returns the weekday name for a specified date.

Week, ARJDBC

This function returns an integer that represents the week number for a specified date.

Weekday Number, ARJDBC

This function returns an integer that represents the weekday number for a specified date.

Year, ARJDBC

This function returns an integer that represents the year of the specified date.

To use Freehand SQL

Note

  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.
      Calculated fields-FreehandSQL.png

    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:

      DBFN(‘reverse’,`Description`)

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

    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.

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:

Parameter

Value

ARJdbc-Script-Execution-Status

Error

Use-Enum-Field-Recompute

F

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

Example

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.

Database

Supported 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 SQL

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