Calculations
Calculated fields allow you to create basic calculations with the fields available in your report.
Note
A calculated field that you create in the report builder cannot be used across multiple reports. It is unique to your report only. If you create a set of calculated fields you wish to use across multiple reports you can either:
- Request the calculations to be included in the View by your administrator or
- Copy the report containing the calculations 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.
Description | Video |
---|---|
Creating age bucket – Part 1: This video demonstrates how to calculate the age of an incident. | |
Creating age bucket – Part 2: This video demonstrates how to create age bucket for incidents based on their age. |
Simple Formula
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.
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. |
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. |
Freehand SQL
As BMC Remedy Smart Reporting uses custom JDBC driver to connect with the AR platform, BMC recommends you to use the Pre-Defined formula type instead of Freehand SQL.
Edit a Calculated Field
To edit a calculated field:
- Hover over the calculation in the Field List of the Data step and click on the drop down menu to open it
Select the Edit Calculation option
Delete a Calculated Field
To delete a calculated field:
- Hover over the calculation in the Field List of the Data step and click on the drop down menu to open it
- Select the Delete option
Comments