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

 

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.

BMC Remedy IT Service Management Suite 19.02 ... Using Using BMC Remedy Smart Reporting BMC Remedy Smart Reporting reports Creating a custom report using BMC Remedy Smart Reporting Report Builder - Data step Fields

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

Simple Formulas allow you to build calculations using the formula builder button interface. These may range from very simple addition or multiplication through to CASE statements.

  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 Simple from the Formula Type drop down list.

    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. BMC Remedy Smart Reporting will not permit you to select combinations of variables that are incompatible – therefore options that are greyed out will 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 wish to type your calculation you may need to use the Freehand SQL option discussed later.

  3. Once finished click the Validate button to ensure your calculation is not missing any components.
  4. Click Save to complete your calculation. It will now be available in a folder called calculated fields at the bottom of the field list in the Data step.

Pre-defined 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.

  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.

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

Formula

Description

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.


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 Open link .

  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
Last modified by Vrishali namdev Galinde on Aug 10, 2020
smart_reporting freehand_sql using videos

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

Log in or register to comment.

Grouping
nText fields
© Copyright 1991 - 2019 BMC Software, Inc. © Copyright 1991 - 2019 BladeLogic, Inc.
Legal notices

Powered by Atlassian Confluence and Scroll Viewport

© Copyright 2005-2023 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.