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 ID, Reported 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 BMC Helix ITSM: Smart Reporting(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 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 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. 
   select 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.

Example: Calculate Average of columns and rows in a Cross Tab report

1. Create a cross tab report; for example, with fields Assigned Group and Status.
   
   
   
   
2. To enable row and column totals, click on the drop down for Assigned Group and Status and select Row Totals and Column Totals respectively.
   
   
3. Drag and drop the No. of Incidents field in the Columns section.
4. To enable average on the No. of Incidents field, do one of the following:
   1. Click the No. of Incidents drop down and select % Totals > % Average.
      
   2. On the top menu, click the Column Formatting icon.
      1. For the Number of Incidents field, go to the Summary section.
      2. In the Total Aggregation section, select Average.
         
         

The report displays the average of the Number of Incidents field.