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.
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.
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.
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.
Additional resources
By accepting you will be accessing a service provided by a third-party external to https://calemeam.com/