How to completely automate your HR reports using R, R Markdown and Shiny

Authors note: Although the contents of this article target HR/People Analytics practitioners specifically, the technical steps and advice apply to any type of analytics and is not specific to People/HR data.

Many HR analytics practitioners out there are frustrated. They can't tackle the cool, interesting stuff because they or their teams are too busy generating the day to day updates and reports that they need for the business.

For example, some teams are still generating standardized, regular reporting in tools like Microsoft Excel. This kind of repetitive reporting in clunky spreadsheet tools can take up a lot of time in non-value adding tasks for talented analysts.

In the same way, enterprise-wide data analytics and visualization tools that are built on top of databases can cause an expensive and time consuming spiral of clunky design and maintenance, as well as wrestling with complex schema and relational tables.

In many cases, the opportunity is out there to reduce this burden substantially, if not entirely, by using open source tools to automatically process, visualize and serve data to internal clients with almost no human effort whatsoever. And at no cost!

What you need:

1.   An existing report which you want to automate, for which all required raw data exists in network databases that you have access to.

2.   Coding skills in R and R Shiny

3.   Capacity on a Shiny Server, either internal or in the cloud (eg shinyapps.io), depending on the confidentiality of the data.

4.   A task scheduler, either on the server (eg Cron in Unix/Linux), or on your local machine (eg taskscheduleR)

Step 1: Write an R script to grab the raw data and process it into visualization-ready form

Using a local raw data source file to start with, write an R script which performs all the calculations you need on the raw data and outputs precisely the data you need for your visualized report. The output can be in many formats but csv and RData are the most popular. If your output file is unusually large, you could also consider the feather format (new, extremely fast but greedy on disk space).

Step 2: Write a Shiny App or an R Markdown document to visualize/display the results from your output file.

Shiny is a package that allows R programmers to create HTML/Javascript based visualizations and functionality to be accessed/viewed via a web browser. R Markdown is a simple language which integrates text and R code into a single output in HTML (and also pdf and other formats). If you have not come across these before, take a look at some of the Shiny apps that people have developed using public data to get a sense of what they can do. Here is a fun one which helps you decide which cocktails you can make based on the ingredients you have.

An R coder with experience in Shiny should be able to write an app which designs a pleasant dashboard visualization of your report and pulls the data to populate this dashboard from the output files from Step 1. Critically, this code is completely separate from the data files used to populate the dashboard, and once fully developed, can sit permanently on the server with no adjustment or maintenance required.

One of the joys of the R Markdown language is its ability to integrate R code into plain text. This allows you to include conditional statements which change according to the values in the dashboard, offering an opportunity for tailored interpretation of the dashboard with no human involvement. Here is a simple example of how that looks in practice:

Other packages I recommend in developing your visualization are Plotly (a great graph library!) and Flexdashboard (a flexible dashboard format with lots of awesome features).

Step 3: Place your visualization app on a Shiny Server

Either get a Shiny Server set up locally on your network (if your data is internal and confidential) or utilize a cloud service like shinyapps.io if your data is public. Setting up a Shiny Server is free (at least for the basic version) – all you need is server capacity. If cost is not a concern, you may want to take a look at RStudio Connect which is an excellent platform for managing Shiny and Markdown documents, as well as controlling access to them.

Load your visualization files onto your Shiny Server and make sure that they point to where your data is going to be placed. Easiest is to place the data in the same folder as the visualization files, but this may be different if you want several apps to access the same data files.

Step 4: Adjust your script from Step 1 to grab raw data directly from the database and dump output to the Shiny Server

Assuming you started out with a local raw data file, you now need to make your connection to the source database (eg via an ODBC connection) and set up your database query to grab the raw data from your database. This emancipates your script from any local dependencies.

Also, now is the time to ensure that you are writing your output files to a place where your visualization app can find them on the Shiny Server.

Step 5: Tidy up packages and versions and set up your scheduling job

First make sure that all the packages and versions you need are present where you need them to be, especially on Shiny Server. For best results, ensure your packages on the server align with those you are using on your local machine.

Second, set a schedule for your R script to run automatically to generate the refreshed data for your visualization app to access. If you are running your script directly on a Linux/Unix server, Cron is a simple way to do this. Just find your Crontab file, and edit it appropriately. For example, to set your script to run at 2am every Sunday, edit your Crontab file with a line as follows: 

(If you need reassurance that it all ran OK, just edit the ‘mailto:’ line at the top of your crontab file to get the results of the script execution emailed to you.)

If you are running your script locally that’s fine too. Just use a neat plugin like taskscheduleR to set your schedule (using RStudio). However, you will also need your script to have a system command to upload your output files onto the Shiny Server before it completes. Oh, and your computer will need to be running when the task is scheduled. (It’s OK if you are having a drink at the bar while this is all happening! In fact that's the whole point of this really!)

So there you go.  Fully automated reporting with no human effort needed. It can be done. And it can be done at no cost. I think it’s critical for HR and People Analytics functions to start to embrace some of these tools if they are to take seriously their role in generating new, dynamic and exciting analytics for their businesses. Freeing up the capacity of talented analysts by reducing their involvement in day to day reporting is critical to achieving this. The technology is there already.

I lead McKinsey's internal People Analytics and Measurement function. Originally I was a Pure Mathematician, then I became a Psychometrician. I am passionate about applying the rigor of both those disciplines to complex people questions. I'm also a coding geek and a massive fan of Japanese RPGs.

You can message me on LinkedIn or engage with our People Analytics group within McKinsey's Organization Practice.

All opinions expressed are my own and not to be associated with my employer or any other organization I am associated with.