Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Alteryx is a fantastic number cruncher and can connect to many data sources. Many of our users leverage Alteryx’s capabilities to connect to data sources, transform data, make predictions, and push the results to interactive dashboards in Tableau or PowerBI. These dashboards allow users to browse data, filter results, and display insights in ways most helpful for them.
But what if you routinely need to create PowerPoints? Some of our clients have remarked that “from the remaining 15 users of Blackberries in our country, 10 are in executive positions in our company” and are requiring PowerPoints for meetings. Alteryx can also output insights in PowerPoint, but complex layouts or using custom templates can become quite tricky.
How about we combine the power of Alteryx with a way to automate even complex PowerPoints? Alteryx, R and the “officer” R package from David Gohel to the rescue! This allows you to combine the ease-of-use and powerful connections capabilities we all love from Alteryx with advanced, custom creation of PowerPoints. Many of our users know R and leverage it for complex models, and once you see how easy it is to generate custom PowerPoints, it’s hard not to be impressed.
How does it work? At a high level, the “officer” package allows users to create PowerPoint presentations (or Word documents) by adding text, tables, or images. When leveraging an existing template, users can fill empty placeholders in the template sequentially. This opens entirely new possibilities: one can build an empty PowerPoint template, which contains all key layout elements, and by leveraging some simple code, the output can be highly customized. A full list of capabilities can be found in the Officeverse documentation. The general idea is that all the pre-processing can be done in Alteryx, and once the data set is ready, we push it into the R tool in Alteryx and assemble the PowerPoint in R.
That’s where the officer package comes in handy. Four steps are needed: First, we need to create a new presentation. Second, we need to insert a slide. Third, we need to put content on the slide, and lastly, we need to save the presentation. These four steps accomplish this for a generic PowerPoint:
my_pres <- read_pptx()
my_pres <- add_slide(my_pres, layout = “Title and Content”, master = “Office Theme”)
my_pres <- ph_with(my_pres, value = “Hello world”, location = ph_location_type(type = “title”))
print(my_pres target = “static/reports/first_example.pptx”)
The officer package has many additional capabilities, and I can highly recommend checking out the documentation.
Let me show you an example! For this project, you will need Alteryx with predictive tools installed. You will also need to install the R package “officer” (instructions are here).
Next, we need a PowerPoint template. PowerPoint wizards know that you can build custom templates; this post has a template included that we will use. Opening it up by going to View – Slide Master and showing the names of the layout elements by clicking on Home – Arrange – Selection Pane (red box in the screenshot below), you can see all elements on the slide. This slide will include business results across four different segments, so we added four placeholders. The blue boxes indicate that the blue “Consumer” placeholder has been named “Text Placeholder Consumer,” so this is the box where the Consumer results will have to be inserted.
Copy the PowerPoint template into C:\Temp (or adjust the file path in the R-tool in Alteryx). Since the main focus is on demonstrating the capabilities, let’s keep the Alteryx workflow simple. Using a built-in demo data set, we will summarize and sort the data in Alteryx and then use R to generate the monthly sales presentation of “ACME Conglomerate.” We assume that the company has four divisions and, in this month, provides a store-level detailed report into the 10 Consumer stores (which hereby sell donuts, just to make this presentation a bit visually appealing).
After running the workflow, you get a slide with performance by segment – its simple design should make it easy to follow how to fill the various PowerPoint placeholders. To spice things up a bit, the next slide contains a pie chart with a sales breakdown, plus some automated commentary – for a production workflow, much more elaborate commentary can be fully automated. And lastly, we put together some more creative, donut-themed slides for the store reports.
Digging into the R code, given that all the data prep is done in Alteryx, we can keep the R code pretty short and simple. The only complexity is the ggplot object generated in the beginning, which to new users may be unfamiliar.
This post barely scratches the surface: by combining Alteryx’s data prep with R’s visualization capabilities, even complex PowerPoint layouts can be automated. And, bonus tip: Tired of boring tables? Using officer and the flextable package by the same author allows R users to create tables and insert them into PowerPoint slides using the same basic techniques. It is easy to use and offers amazing customization - check out the gallery here.
Let us know what you think!
Acknowledgments: Without Dan Hilton’s (@DanH) help in figuring out how to install custom R packages on the Alteryx Server, this post would have never happened.
