How to Set up Weekly PM Reports

​This blog is an exercise of the scheduled reports in Calem discussed in the blog: How to Set up Scheduled Reports in Calem. You may review the blog to familiarize with the scheduled reports in Calem.

The exercise will add two PM reports generated on Friday. 

• PMs due this week (from Last Saturday to this Friday)
• PMs due next week (from this Saturday to next Friday)

Step 1. Create Shared Search

The first step is to create shared searches to be used in the scheduled reports. Log into Calem as a user with "Search admin" permission (Menu path: Organization | ACL Profiles | User list).

The PM reports will be based on work orders generated from PMs. So, we will use All WO reports as the basis to build our searches. 

• Calem generates PMs with due dates of seven days into the future. For instance, today is January 9, PMs due by January 16 will be generated into work orders.
• So, we can use work orders for PMs due next week. 
• Go to All WO report at Work Order | Report | All WO
• Enter a start date to launch the report. The start date is not included in the shared searches we're going to create. Enter a start date of last year will allow one to verify search results.
• All WO report shows.

​Click the "Search" button (highlighted in red) to launch search screen.

 Click "+" to create a shared search. To edit an existing search, check the search then click "Edit" button to start editing.

Populate a search name "PMs Due Last Week", and save the search as "Shared by everyone" for the search to be available in scheduled reports. 

​We will set "PM?" to be "Checked" so only PM work orders are shown. Set "Needed by" to be "BETWEEN" the last Saturday and this Friday. We will use MySQL's functions to derive last Friday based on current date. The parentheses "${" and "}" tells Calem to pass through the expression to MySQL. The time portion " 16:00:00" is in GMT and is determined based on local time. In this case, the local time will be 11:00 am US Central time, or mid-night China Time.

• The following expression is to start from last Friday's mid-night (excluding Friday) to this Friday's mid-night (including Friday) China Time.

Start from last Friday 16:00:00 GMT:
${concat(date_sub(curdate(), INTERVAL WEEKDAY(curdate()) + 3 DAY), " 16:00:00")}

To this Friday 16:00:00 GMT:
${concat(date_add(date_sub(curdate(), INTERVAL WEEKDAY(curdate()) + 3 DAY), INTERVAL 7 DAY), 
  " 16:00:00")} 

Populate the from and to date fields with the expressions above and save the shared search. Then, repeat the process to create another shared search "PMs Due Next Week" with the following expressions: 

Start from this Friday 16:00:00 GMT:
${concat(date_add(date_sub(curdate(), INTERVAL WEEKDAY(curdate()) + 3 DAY), INTERVAL 7 DAY), 
  " 16:00:00")}

To next Friday 16:00:00 GMT:
${concat(date_add(date_sub(curdate(), INTERVAL WEEKDAY(curdate()) + 3 DAY), INTERVAL 14 DAY), 
  " 16:00:00")}
 

2. Create Scheduled Reports 

We can now create PM weekly reports based on the searches created above.

• Create a scheduled report for PMs Due This week
• Menu path: Integration | Scheduled Reports
• Set report to "CmWoAllReportList" and a note
• Set owner site to be your site
• Set search Id as "PMs Due Last Week" created above. It is shown in the edit screen as an Id generated by Calem.
• Set profile "Administrator" to select from all work order sites.
• Set the report to run every week on Friday with the initial start date on January 10, 2020 (a Friday).

• Save the report. 
• Next go to the Recipient tab and click "+" to add users to receive the reports in email.

• Repeat the same process to create PMs Due Next Week. 

Additional resources

• How to Set up Scheduled Reports in Calem
  
• Saved Search in Calem
• How to Track Outbound Emails in Calem
• How to Customize List|Report in Calem
• How to Set Up Workflow Notifications for Work Orders
• User Guide and Admin Guide (customer account required)
  
• Calem Enterprise Training Site
  
• Calem Enterprise Blogs
  
• Calem Enterprise demo