Calculate Summary - Make a Tracking Report
Making an automated tracking report is the perfect use of the Calculate Summary action. Normally, you only see current condition on a SharePoint list with records, but you don't have any kind of historical tracking.
If you are building your own Help Desk System, Project Tracking System or any kind of tool in which records are continuously updated, it is very helpful to track performance to see how things progress on a day to day basis. With this data, you can do reporting and charting to see trends over the long term and to analyze overall use your tool.
Achieving this kind of tracking isn't obvious, but it is suprisingly easy and can be replicated wherever this reporting is needed.
In order to make a tracking report, we first need to create a new SharePoint list to be used specifically for this purpose.
In this list, we want a new record to be generated on a daily basis to record summary data from our source list.
In this example, we have an Approval System that has a Status for each record. We want to record the total number of records by Status once each day. This will allow us to see when there are more approval requests, when they are overdue and when the are complete. Over time, we can view trends to see when the performance is bettter or worse than expected.
Start by making a new Custom List named Approval Tracking.
In the new list, set the Title field not required since this won't be needed.
Add a date field Summary Date and three more number fields to store totals Draft, Approval and Complete. (Note that this assumes a source data list with a Status field with possible values of Draft, Approval or Complete.)
We now need an action in the tracking list to create a new record once a day to store this tracking data.
Set up an action to create a new record once daily at 6 AM as shown.
Note that for timer-based actions, it is required to add a condition. The record needs to be created from the context of another existing record. We will add in a first seed record which will be a context from which each successive record will be generated. This is just a way to ensure that this timed action happens once each day.
Each day, when the tracking record is created, we are going to have 3 Calculate Summary actions to get a total record count for approval records matching the specified status. Use the pattern shown below to set up actions to check for the different Status values on the records to get a filtered count.
On the Action Settings selec the source list and apply the appropriate Items filter as shown.
Use "Update" for the Resultvalue. The Column selection isn't significant in this case because we are simply get a count of records and not a sum or average. Use the Count Operator and select the column in your tracking list to store the value.
Use this same pattern for each column that needs a summary total.
Be sure to creat one initial record to see your list. This is needed for the timer-based action that we set up.
Once you have the above steps down, it is very easy to use this strategy anywhere that you need to report on trends. Once you have accumulate several weeks worth of tracking, you can add charting to add visual reporting to share with team members. This is a great way to add accountability and historical information to assess progress.