Building and Scheduling an Unable To Locate Report

Overview

Understanding the how/when/why PM WO's end up in an Unable To Locate status is key to running an efficient department. With the Query Writer module, managers and directors can get a daily, weekly, monthly, or yearly look at how their department is performing with Unable To Locate WO's. Query Writer allows for a set it up once, automate it forever approach.

This article will cover how to create and schedule a monthly and weekly Unable To Locate report.

1. Creating a quick report
2. Selecting fields & group/sort order for the report
3. Filtering the report
4. Setting the report layout
5. Testing the report
6. Scheduling the report

Creating a quick report

1. Login to query writer. On the top toolbar select New -> Quick Report
   
2. Give the report a Name, a Comment to describe what the report does (optional), a Tag to organize the report in a folder(optional) , and select the Data Sources you would like the report to run against (most instances will have one Data Source, hemsent)
   

 

Selecting fields & group/sort order for the report

When selecting fields for a report, try to keep the tables used at minimum. The more tables added to a report, the more complex the report becomes. If possible, select all fields from the same table.

1. On step 2, select the Fields you would like to display on the report, from the appropriate Table. To select a Field click the "+" icon. For this report, we will be selecting Fields from the WO with Equipment Information Table. To re-arrange the order of the selected Fields, drag the Field to the desired location.
   
2. On step 3, select how you would like the Report laid out by defining any Grouped Fields, and the Group/Field Sort Order by dragging the appropriate Field to the appropriate box. In this example, the report is Grouped by the EQ Type, and sorted ascending by the Status/Close Date. To change the Grouped fields setting, select the Field. 
   

Filtering the report

On step 4, set the filter conditions. When selecting the Table to run the filter condition against, it is best practice to select the Table that the selected Fields in the report are from. If using more than one Table, select the same Table used for that Field in the report. 

1. To add a Filter Condition to the report, click the left "+" icon, to add a Filter Group to the report, click the right "+" icon.
   
2. It is best practice, to have the first Filter condition define which service areas you want this report to run for out of the AP Service Area Properties Table. If you intend to run this for your entire system, set the condition to does not equal Shared Information. 
   
3. With this report being created with the intention to be scheduled, it cannot have any Ask at runtime conditions. Ask at runtime will prompt the user to define the Filter condition(s) when the report is run. When a report is intended to be scheduled, select the Compare to Expression option.
   
4. To define the date, when setting an Expression, click the icon with three dots. 
   
5. When setting a date Value, either click the Calendar icon, or type the Date.
   
6. For this default filter set, we will be filtering the Status/Close Date between First day of previous month to the Last day of previous month. Click the three dots icon next to the first Field. From the Commonly used expressions drop down, select First day of previous month, click Insert Function, and then click the Save icon. Repeat this for the second field, this time selecting Last day of previous month. 
   
   Once you have both dates set, the date filter set should look like this. 
   
7. This default filter set is now set to run for all service areas that does not equal Shared Information, for the WO Type ROUTINE, for the Subcode UNABLE TO LOCATE, and the Status/Close date is within the previous month.
   
8. Next a second Filter set will be setup for the previous week. Click the "+" icon next to the Filter Set drop down.
   
9. Name this new filter set Previous Week, and click OK
   
10. For this Previous Week filter set, we will be filtering for a Status/Close Date between First day of previous week to the Last day of previous week. Click the three dots icon next to the first Field. From the Commonly used expressions drop down, select First day of previous week, click Insert Function, and then click the Save icon. Repeat this for the second field, this time selecting Last day of previous week. 
    
    Once you have both dates set, the date filter set should look like this. 
    

Setting the report layout

1. On step 5, configure the report layout and data options. Since this report is intended to be run on a schedule, it is important to set the Always run setting as desired. If this setting is on (check mark) then the schedule will run and send regardless of if there is any data on the report. If it is off, and the report does not have any data, the schedule will run, but not send the report out.
   
2. Once you are finished with the report layout, click the green Finish icon to save the report. 
   

Testing the report

1. run the report to verify the output is as desired, by either clicking on the report name, or clicking to the right of the report name and then the preview icon.
   
2. Since this report has more than one filter set, you will be prompted to select the filter set you would like to run. Select the filter set and click OK.
3. Once the report is run, you have the option to Export. Select Export options.
4. In the first drop down, select the file format you would like the report to export to.
   
5. To Download this report, click the save icon. To Email the report, first define the Email Settings, and then click the "@" icon. To upload to a FTP, first define the FTP Settings, and then click the "FTP" icon.

Scheduling the report

Scheduling the monthly report

1. Select Scheduler on the top toolbar and click the "+" icon
2. On step 1, give the report a Name and set the Start Date & Time. It is best practice to not run overnight reports at midnight.
3. For the frequency, select Monthly - Day of month. Select Day 1, and select the month(s) you would like this to run.
4. On step 2, select the Unable To Locate report, with the Default Filter Set selected
5. On step 3, select the Output type. For emailed reports, set the email settings. For FTP reports, set the FTP settings. Set the desired File Name, and File Type and click the Finish button to save the schedule.
6. Test run the schedule by clicking on the Lightning Bold icon. 

Scheduling the weekly report

1. Select Scheduler on the top toolbar and click the "+" icon
2. On step 1, give the report a Name and set the Start Date & Time. It is best practice to not run overnight reports at midnight.
3. For the frequency, select Weekly Select Every 1 weeks, and select Sunday as a week in this application runs from Sunday to Saturday
4. On step 2, select the Unable To Locate report, with the Previous Week Filter Set selected
5. On step 3, select the Output type. For emailed reports, set the email settings. For FTP reports, set the FTP settings. Set the desired File Name, and File Type and click the Finish button to save the schedule.
6. Test run the schedule by clicking on the Lightning Bold icon.