In my ongoing effort to both remember what the heck I have done and to share good ‘pracademic’ ideas, I present a method to track staff training. Hopefully you can use/adapt what you find here and hopefully I can remember how I built it in case I need to do it again in the future!
Microsoft SharePoint Based Training Tracking System
- Employees are our most valuable asset…
- We are a learning organization….
- We train our staff for their next job…
Clichés, corporate mantras, good business or all of the above? The answer is probably all of the above. Training is a critical part of any organization. It can also be expensive and often organizations use a shotgun approach to achieve its result. For courses that are part of a compliance function (e.g. those required for new employees or periodic courses such as information security) having a good record of who took which course when can be the difference between breezing through a compliance audit and scrambling for records at the last-minute.
The following SharePoint based system helps to address the above by integrating the training functions into the budget setting and performance management functions. Because it is reconciled to the general ledger, it also has a strong degree of credibility. It can be built in about a day by a knowledgeable SharePoint user.
Three different flavours are presented depending upon the size, complexity or SharePoint knowledge available:
- Simple, a single SharePoint list: This is more or less equivalent to creating the same thing in a spreadsheet. Skills, experience and/or access needed include:
- Access: Administrator permissions to a SharePoint site (ideally 2013 but 2010 will do).
- Know-How to create: a custom list, choice column types, views, permission groups.
- Normalized, a multi-list SharePoint system: This method normalizes the data for better reporting and accuracy. Access & Know-How from above is required plus:
- Access: site administrator access is ideal.
- Know-How to create: look up of a list as a list column.
- Reporting+: Building on the normalized model, reporting functions are added. Access & Know-How from above is required plus:
- Know-How to create: an enduring SharePoint list in Excel and a Word Mail Merge.
- For those more sophisticated, consider accessing the list via Microsoft’s Power Query tool.
Simple, a single SharePoint list
If you have a small organization, are unfamiliar with the administrative functions in SharePoint or simply need to get something up and running fast – this is way to go. Built to look and feel more or less like a spreadsheet, this SharePoint list has the added features of version history and a single source of truth. Combine it with some of the reporting features discussed below. See the next section for a list of columns to include. Key elements of the list include:
|List||Course Register||Other names include ‘staff development’ or ‘learning list’.|
|Description||The Course Register List tracks, proposed, approved and taken courses for financial, staff development and compliance purposes.||Adjust as required (e.g. drop contractor reference).|
|Navigation||Select Yes if you want the list to show up on the navigation bar.||Depending upon your site, likely select ‘Yes’ for ease of access for your staff.|
Normalized, a multi-list SharePoint system
The specific columns you need may vary from the list presented below. For example, the following list includes columns for both estimates and actuals. If you are not concerned about comparing an estimated cost against the actuals, these columns could be combined. In the same way, separate columns for travel, subsistence (meals), etc. may not be needed in your organization. The attached Microsoft Excel file includes a full data dictionary for a normalized Course Registration system.
The following Microsoft Excel File contains the data dictionary for the above lists as well as a sample reporting tool: SharePoint-CourseMgtSystem-DataDictionary
Reporting+, Normalized plus Reporting functions
Reporting from a SharePoint list is surprisingly easy. The following are 5 different methods of getting information out of a SharePoint list starting with the easiest and ending with the more technically challenging. The focus of this blog is on the first few methods which most users can bang together fairly quickly.
- SharePoint View: The easiest method is to simply create a view based on the key data fields you need. Such views can be user specific and have a fair degree of sophistication. The four views I normally put into a production list such as this ones include the following:
- All Items:
- Simple XLS List Export: for quick and dirty analysis, nothing beats exporting one of the above views to an Excel file. Be aware which view you use however because you may have hidden some critical fields from a view to make it more user friendly. This is why I like to have a Standard View called XLS_Export which includes all of the key fields.
- Formula Derived XLS List Export: An under used aspect of SharePoint is that it can create an enduring link to an Excel file and reports can be built on top of the file. The attached sample file includes a sanitised version of the XLS_Export view linked to a ‘Staging Tab’. In turn, contents from this tab are used in pivot table or other types of reports. A surprisingly complex set of reporting can be created with a Excel Knowledge and ingenuity. SharePoint-CourseMgtSystem-DataDictionary
- Power Query: If your IT Department supports this Excel add in, you may be able to create more complex queries of the SharePoint lists using this tool. First step, talk to your IT folks because this is WAAAYYY beyond this blog.
- Reporting Engine (e.g. Microsoft Access): Microsoft Access, SharePoint and Excel can be used in a surprisingly productive and sometimes flaky manner. This is a teaser as this discussion is worthy of a blog in of its own right.
Conclusion – a Teaser and Good Luck!
The above is a teaser and a How-To manual on constructing a Training Tracking System. Hopefully it has given you the tools to either apply it in this regards or to create another purposes built SharePoint list based system. Drop me a comment if you have been successful.