×
 
 
  •  
$helper.renderConfluenceMacro('{bmc-global-announcement:$space.key}')

 

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 Open link .
Remedy Smart Reporting 19.11 ... Using Creating advanced reports

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

Using subqueries to append or join report data

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

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

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

    4. Click Validate to ensure your calculation is not missing any components.
    5. 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.
    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.

FormulaDescription
Character Index/Position, ARJDBCThis 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), ARJDBCThis function converts an expression of type CLOB to an expression of type Varchar.
Convert nText to Varchar (MSSQL), ARJDBCThis function converts an expression of type nText to an expression of type Varchar.
Convert String to Number, ARJDBCThis function converts an expression of type String to an expression of type Number.
Current Date, ARJDBCThis function returns the current date and time. The date is returned in the DD/MM/YYYY format.
Date Difference - In Days, ARJDBCThis function returns the difference between two date values, in days.
Date Difference - In Seconds, ARJDBCThis function returns the difference between two date values, in seconds.
Date, ARJDBCThis 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, ARJDBCThis function returns the day of the month from a specified date.
First Day of Month, ARJDBCThis function returns the day of the month.
First Day of Quarter, ARJDBCThis function returns the day of the quarter.
First Day of Year, ARJDBCThis function returns the day of the year.
Hour, ARJDBCThis function returns the hour part of the specified time.
Length, ARJDBCThis function returns the number of characters of an input string.
List Aggregation, ARJDBCThis function transforms values from a group of rows into a list of values that are delimited by a configurable separator.
Lower, ARJDBCThis function returns a character expression after converting uppercase character data to lowercase.
LPAD, ARJDBCThis function left-pads a string with another string, to a specified length.
LTRIM, ARJDBCThis function returns a character expression after removing the leading blanks from the specified string.
Minute, ARJDBCThis function returns the minute part of the specified time.
Month Name, ARJDBCThis function returns the name of the month from a specified date.
Month Short Name, ARJDBCThis 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, ARJDBCThis function returns the quarter of the year for a specified date.
Replace String, ARJDBCThis function replaces all occurrences of a specified string value with another string value.
RPAD, ARJDBCThis function right-pads a string with another string, to a specified length.
RTRIM, ARJDBCThis function returns a character expression after removing the trailing blanks from the specified string.
Seconds, ARJDBCThis function returns the seconds part of the specified time.
SubString, ARJDBCThis function returns part of a character for given length from start position.
Upper, ARJDBCThis function returns a character expression after converting lowercase character data to uppercase.
Week Day Name, ARJDBCThis function returns the weekday name for a specified date.
Week, ARJDBCThis function returns an integer that represents the week number for a specified date.
Weekday Number, ARJDBCThis function returns an integer that represents the weekday number for a specified date.
Year, ARJDBCThis function returns an integer that represents the year of the specified date.

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 

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]

.

  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

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]

.

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
Last modified by Vrishali namdev Galinde on Jul 22, 2021
smart_reporting freehand_sql videos using calculated_totals best_practices

Comments

Log in or register to comment.

Creating advanced reports
Creating reports with advanced database functions by using Advanced Authoring
© Copyright 2014 - 2019 BMC Software, Inc.
Legal notices

Powered by Atlassian Confluence and Scroll Viewport

© Copyright 2005-2024 BMC Software, Inc. Use of this site signifies your acceptance of BMC’s Terms of Use . BMC, the BMC logo, and other BMC marks are assets of BMC Software, Inc. These trademarks are registered and may be registered in the U.S. and in other countries.