Spotlight on Reports: Week 3 of 3


Last week in our spotlight on reports, we described how you can add multiple sorts, sub-grouping, and totals to your reports. This week we will be showing how to perform basic calculations within a cell of your report.

Las Vegas Training Event
The seminar details and v4.2 preview are included in the below link. Please click on the image below to download a PDF with additional information.  Many customers have already signed up and received the early bird discount – we look forward to meeting you in Vegas!

clip_image001

Field Calculations:  When building a report, it can be valuable to perform calculations between fields displayed on the report.  In order to accommodate this, Maintenance Connection includes a “Custom Expression” box for each field added to the report (see below).  This box can be accessed by clicking a Display field and subsequently clicking the Edit button under the Display list.

CustomExpressionBy setting the “Custom Expression?” radio button to “Yes”, this allows a user to specify the exact output they desire from this field on the report.   Users may enter the table.fieldname and use standard mathmatetical operators (*,/,+,-) to create a new value.  For example, to calculate the difference between the WO Target Hours and Actual Hours, the following may be populated into a Closed WO List report:  wo.targethours-wo.actualhours

Report

Tip: When using custom expressions, it is advised to rename the field to be more descriptive of the results it will return and to also update the Field Type to match what you are displaying.  See the Field Label (Custom) has been updated to “Hours Difference” and the Field Type (Custom) has been updated to “Numeric”. 

Field Options

An additional Custom Expression that is extremely useful, utilizes some basic SQL coding.   The below SQL will calculate the response time (in days) between the Requested and Complete dates recorded on the work order: datediff(day,wo.requested,wo.complete).   This script may easily be modified to accommodate minutes, hours, months, etc.  The end result is as follows:

Datediff

 

 

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: