Report Features – 2 of 3


In this second blog in the series on Report Features and Tips, we will explore creating custom expressions to modify the display of a field or perform calculations in report columns. Two common examples of modifying the display of a field include changing the “case” of a text field (displaying in all upper case) or altering the format of a date display (removing the time stamp).

A common mathematical calculation includes determining elapsed time, such as days to complete work order or hours to respond. You can perform mathematical calculations on individual fields, or perform calculations between fields (e.g., sum costs).

Create Custom Expressions

As a simple example, let’s calculate the number of days a work order has been open:

  • Access Report Setup for the report on which the calculated field is to be displayed. To create a calculated field, you must specify a “placeholder” field on which the calculation will be specified.
    • Select a field from the Available Fields List to use as a placeholder (field cannot already be shown in the Display Fields List).
    • For our example, select  “[WO]UDF Field 10″ and move it to the Display Fields List.
  • Select UDFField 10 in the Display Fields List and click Edit, just below the field list. The “Report Field Options” dialog will display for this field:


  • Enter the desired “label” for the report column in the “Field Label (Custom)” prompt. In our example, you could enter “Days Open“.
  • Select the appropriate field type for your calculated result from the Field Type dropdown. In our example, you should select “Numeric“, as we will be calculating the “number of days” into this field.
  • Click the “Yes” control in the “Custom Expression?” field prompt toward the bottom of the dialog, letting the system know that a custom calculation or expression will be used.


    • The expression for the calculation must be placed in the “Custom Expression Box“. In our example, we will create a custom expression that calculates the number of days between “today” and the date the work order was “requested.” A function called “DATEDIFF” is used to calculate elapsed time between dates. The format of this function is as follows:
      • DATEDIFF(increment to display – such as ‘DD’ for days, field/value to be subtracted, field/value to subtract from)
      • To tell the system to calculate from “today”, we must use the expression “GETDATE()” to signify the current date. The correct expression would be:

      DATEDIFF(DD,WO.REQUESTED,GETDATE())

  • In our example, the custom expression box might appear as follows:


  • Click Apply to return to the Report Setup Window.

Sample Custom Expressions

Advertisements

No comments yet... Be the first to leave a reply!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: