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 .

Examples of using predefined functions in reports

This topic explains the examples of using predefined functions in reports.

Example: Display the week day for the incident reported date

This example explains how you can display the week day for the incident reported date.

  1. Create a report by using the Incident Management view.
  2. Add a Predefined calculated field.
  3. Select the Week Day Name and the ARJDBC function and apply it to Reported Date field.
  4. Add the Incident IDReported Date, and the Calculated field (Week Day Name) to the columns.

The report displays Incidents ID, its Reported Date, and the Week Day name of the reported date.

Example: Use the substring function

To apply the substring for a character, first use the Char Index function to find the position of the string. The Char Index function then displays the position of the string and applies the substring on top of the result of char index.

For example, follow these steps to apply a substring for the Assigned Group field based on the - string.

  1. Create a report by using the Incident Management view.
  2. Add the Incident ID and Assigned Group fields to report columns.


  3. Use the Char Index function in the view to remove the characters before '-'.
  4. Edit the view and create the pre-defined formula calculation by using the Char Index function on the Assigned Group field.
    This calculated field searches for the position of the '-' string.

  5. Click Save and publish the view and return to report.
  6. Create a new pre-defined calculated field and select the Substring function.




    The Substring function removes the characters before the '-' string.

Example: Use the DateAdd function

The DateAdd function adds a number to a specified date part of an input date and returns the modified value. The predefined DateAdd function is available in the Smart Reporting calculated fields.

To add +7 days to the Reported Date

In the Calculated Field window, complete the following steps:

  1. In the Calculated Field Name field, enter Reported Date+7 days.
  2. From the Formula Type list, select Pre-Defined.
  3. For Functions, select DateAdd, ARJDBC.
  4. Click Save.

    The result adds +7 days to the Reported Date.

To add -7 days to the Reported date

In the Calculated Field window, complete the following steps:

  1. In the Calculated Field Name field, enter Reported Date-7 days.
  2. From the Formula Type list, select Pre-Defined.
  3. For Functions, select DateAdd, ARJDBC.
  4. Click Save.


    The result adds -7 days to the Reported Date.

Example: Get the Status History information in a report

You cannot directly query the Status History information in Smart Reporting, since the Status History data is stored in the form's History (H) table in the Remedy AR System database and the History tables are not accessible from Smart Reporting.

Follow these steps to get the Status History information in Smart Reporting. 

  1. Use the following query at the Remedy AR System database level to identify the Schema ID of the form on which you have enabled the Status History. For example, HPD:Help Desk. 
    s
    elect name, schemaId from arschema where name = 'HPD:Help Desk'



  2. Identify the H table by using the schemaId shown in the preceding image.  
  3. Use the following query to review the information related to Status History on the HPD:Help Desk form:
    select * from h[schemaId] OR select * from H1510


    The T columns are related to the DateTime fields and the U columns are related to user account. Each column corresponds to a Status value (for example, New, Assigned, In Progress). All the T fields are in an EPOCH format, so, you must convert those fields into a readable format.
  4. In Developer Studio, create a view form by using the H table.
    For more information, see  Creating and modifying view forms .
  5. Add permissions to the fields and form.
    For more information, see  Creating and modifying view forms .

You can now use the View form in Smart Reporting Views to collect the required information. For more information, see Views.

Was this page helpful? Yes No Submitting... Thank you

Comments