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
- Click the + button at the bottom of the field list in the Data step of the report builder.
- The Calculated Field window will now appear. You will need to define the following:
- 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.
Select Simple from the Formula Type drop down list.
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.
- Once finished click the Validate button to ensure your calculation is not missing any components.
- 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.
- Click the + button at the bottom of the field list in the Data step of the report builder.
- The Calculated Field window will now appear. You will need to define the following:
- 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.
- Select Pre-Defined from the Formula Type drop down list.
- Choose the formula you wish to apply – the selection is specific to the type of database that your data is sourced from.
- You will now be provided with options to complete your calculation. Click on each parameter to define it.
- Provide a value using one of the options provided. These will vary depending on the type of value required.
- 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.
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