A common request in project management is to be able to create a dashboard for each project that shows the status of specific defined deliverables that must be created. For example, the project manager must be able to see that a SOW or proposal has been posted to the site. The project manager must also post a status report to the project each week – or get flagged as being out of compliance.
In prior versions of SharePoint, this was typically performed through the use of custom code. For this post, I decided to sit down and see if it could be done with a little SharePoint Designer workflow. Turns out it wasn’t hard at all.
To create this solution, we’ll need:
- A custom SharePoint list for the dashboard
- A document library
- A couple custom content types
- One list workflow on the document library
- One site workflow on the dashboard list
This is a simple list with a couple of fields:
Note that Status is a drop down with Red, Yellow, and Green as the available options. (Red is the default).
I modified this list to add conditional formatting using SharePoint Designer 2010 as in this blog post. It looks like SharePoint conditional formatting is being deprecated in 2013, so this is a bit of a hack where I had to install SPD2010 to get this to work. I’d trust smarter folks than I to figure out a better way of sprucing up the dashboard – i.e. adding icons or leveraging some other method to create the conditional formatting. For today’s post, this will do the job.
Add list items to your list for each deliverable you will be tracking. I used the same names as the content type in the document library to facilitate the workflow finding the correct item to update.
For the document library, I added a couple of content types and an Approved field.
Dashboard Update Workflow
I then created a simple workflow on the document library. This is set to trigger whenever a document is created or modified.
Adding some additional detail to the picture above:
- Line 1 picks up the UID (as a string) of the dashboard item corresponding to the content type of the document in the document library. This allows some minimal error handling by skipping the items that will throw an error if it can’t find a dashboard entry corresponding to the document content type.
- Line 3 establishes the URL of the document in order to populate that in the document dashboard – so the user can click directly from the dashboard to the document.
- The if….then clauses flag the dashboard item as green or yellow depending on if the document has actually been approved.
Here’s a shot of the step that updates the dashboard item. (This is for approved documents. The other one will flag the item as yellow.) Note I’m copying the Last Modified date into the dashboard Updated field.
That will update our dashboard list per our requirements. One more quick item, and we’re good.
Status Report Monitoring Workflow
For the final step, I want to add a bit of logic to flag the status update column yellow if no status update has been posted in the last week. To do this, I’ll create a site workflow that will wake up every 24 hours, check to see if a new status update has been posted, and flag the item as yellow if it’s been a week since the last update.
That workflow looks like this (if anyone can suggest a better way to present SPD workflow in a blog post that’s not too tedious, let me know.)
- Line 1 is pickup up the GUID for the status report item in the dashboard list.
- I’m basically picking up the Updated date, adding 7 days, then comparing it to today’s date to determine if the item must be shifted from green to yellow.
And there you have it…one compliance dashboard. If anyone has suggestions on how to clean up the look and feel of the dashboard itself, feel free to post in the comments section.