Automated Weekly and Monthly Reports to Stakeholders
An automated weekly report can allow you to send out exports of the actions for the coming week in one simple document.
For monthly reports, you can demonstrate the status and high-level details of the projects that are currently running.
This example uses a project tracking app with a child app for actions on this app. This means that both the weekly and monthly reports will have information from the projects app with an attachment for the actions in the coming week and month. For a video on this please click here
Actions (Child) App
To find out what week and month the action is due, you must have a target close date field that records when the action should be complete. It is also suggested to have a bit field to record whether the action is closed or not - I have named it Closed? And it runs off of an expression to see if there is data in the actual close date field. This expression is as follows:
if([ActualCloseDate] <> '', true, false)
You will then need to find the week and the month that this corresponds to. This is done through two number fields - one for week and one for month.
The week number field would be called something like Week of Date Close with an expression in to find the week of the target date:
weekNum([DateClose])
Create another number field called Month of Date Close with a similar expression to get the month of the target close date:
month([DateClose])
These two fields will now extract the week and the month when the action is due, but you will also need fields to find the week and month now so that you can compare these to find the actions that are due this week or month.
Create a date field for today’s date and input the following expression:
DateNow()
Similarly, two more number fields to find the week and month of today’s date:
weekNum([TodaysDate])
month([TodaysDate])
Now, you can use a bit field for week and month to see if the target date week is the same as today’s date and the same for the month.
The week bit field I called This Week:
if([WeekNow] = [WeekofDateClose], true, false)
And then one for This Month:
if([MonthNow] = [MonthofDateClose], true, false)
This means that any actions that are due this week, the ThisWeek bit field will be true and the same for the month.
Using these, you can set up a report in the action app that has a base filter for if the action is due this week and another for this month.
These reports will be used in the templated export to send to the stakeholders.
Copy the default report that you have or create a new report with the fields that you want stakeholders to see.
Name one This Week’s Actions and the other This Month’s Actions and put in a base filter for ThisWeek = true and another for ThisMonth = true respectively. For both, also add into the base filter to remove any marked as closed so that the actions that are done do not show up in the report.
Ensure that both reports are marked as hidden in the visibility section:
You have now created hidden reports to be used in exports and fields to calculate whether the action is due this week or month.
Project (Parent) App
For the project app, you need a way to count whether there are actions attached that are due this week or month. This will mean that you can filter the records so that only the projects with actions due soon will be used in the reports.
You will create two number fields to count the actions that have a ThisWeek true and a ThisMonth true.
One is named Actions for this week and has the following expression in to count the number of actions with This Week marked as true:
childCount('Actions','{"ThisWeek" : true}')
Set up another number field called Actions this month with a similar expression in to count the number of actions with the This Month marked as true:
childCount('Actions','{"ThisMonth" : true}')
You will also want a way of knowing whether the project is closed. In this example, there is a percentage complete multi-state Harvey Ball field. This would mean that if a project is not marked as 100%, it is not complete.
You will want a way to store stakeholder emails - I have chosen to make a grid field with one column which is a person field so users can add in the key stakeholders of the project so that these can be used in the email report.
Templated export:
You will need to create two templated exports - these need to be a word/pdf document with the key details in. This needs to be tailored to your app, we have a support article on how to create exported template reports here.
For both templated exports, you will need the key fields from the project app in.
Then underneath, you will need to reference the hidden reports in the actions app - the weekly template will use the This Week hidden report of the actions app and the monthly will use the This Month hidden report of the actions app.
Add these documents to the templated export section of the app, naming them weekly and monthly respectively.
Workflow
This will be run from the parent app (Projects). You will have one weekly and one monthly, and both will be scheduled workflow.
The weekly one will look like this - you want to run it probably every Monday morning before people start work. Set it recurring for Mondays and make sure you select a user that has access to all records in the app - possibly an admin.
Then you want a filter with two sections - one to rule out any complete records, so this example is:
[PercentComplete] <> '100%'
And then the other section to count in only projects with actions for this week:
[Actionsforthisweek] <> 0
So the overall filter is
[PercentComplete] <> '100%' && [Actionsforthisweek] <> 0
The action node is Send Email and you want to send to all stakeholders in the app. Input the information that you want to send to the stakeholder - here is an example:
The following project has actions that need to be completed this week as per the target close date:
Project Name: @Softools.FieldValue("Title")
Click on this link to access the record @Softools.RecordLink()
Attached is the report for this project and the actions that need completing.
Then attach the templated export for the weekly actions.
Overall workflow:
Copy this for the monthly report - set it recurring for every month to be sent on the 1st of the month.
The filter will be similar to the weekly but with the monthly action field rather than weekly:
[PercentComplete] <> '100%' && [Actionsforthismonth] <> 0
Again, add in an action node for Send Email to stakeholders and input the information you want to be seen.
Overall workflow:
This will mean that every Monday, any project that is not closed and has actions that need completing that week will be sent to stakeholders and on the first of the month, a similar report will be sent for non-closed projects with actions to be completed that month.
Thanks for reading!
Please sign in to leave a comment.
Comments
0 comments