How to retrospectively automate an existing PowerPoint report using Displayr
With Displayr it’s possible to create and automate your PowerPoint reports as shown in this post.
It is also possible to take an existing PowerPoint report and retrospectively automate it. In this post, I will show you how to do that using an example report. The example report is based on a tracking study that I wish to set-up to be automatically updated with the latest wave of data.
The report, as shown below, consists of two slides with 6 data objects all of which can be automated.
These objects are:
- A table showing the number and % of respondents for the last 13 months
- A Pie chart showing the Gender split for the last month
- A Bar chart showing the Age split for the last month
- A sorted Bar chart with Food Categories that have been eaten or bought in the last month
- A trended Line chart showing Food Categories that have been or bought in the last 13 months
- A Text box showing the question label and last months base size
- Create a new Displayr document and import your data
- Create dynamic time filters
- Create data tables replicating the outputs in the PowerPoint report
- Linking the Displayr outputs to the PowerPoint report
- Updating the data set with the latest wave of data
- Updating the PowerPoint report with the latest wave of data
Step 1: Create a new document and import your data
- Create a new Displayr document
- In the Ribbon, choose Insert > New Data Set and select the data file you wish to use.
Step 2: Create dynamic time filters
The PowerPoint report consists of outputs showing either the last month of data or the trend of the last 13 months of data. Instead of re-creating filters for each wave, this can be automated by setting up a dynamic time filter.
To do that, go to the Ribbon > Insert > Filter > New Filter.
From the Data dropdown menu select the Date/Time type variable and under Operator select Within last period and define the time period of 1 Month. Change the label to LastMonth.
To set up a filter for the last 13 months, repeat the above process but this time set the With in: to 13 Months and change the Label to Last13months.
The above approach will also work for data sets with less than 13 months of data. For example, if the tracker only has 10 months' worth of data Displayr will add a month worth of data each month until reaching 13 months.
Step 3: Create data tables replicating the outputs in the PowerPoint report
Start by creating a page of results.
Next, create three tables showing months, gender, and age. To do this select each of the variables separately from the Data Set window and drag and drop it onto the page. By doing that Displayr will automatically create the table.
To show both percentages and counts for the first table select the table, go to the Object Inspector > Inputs > Statistics > Cells > tick Count.
Filter the table to show the last 13 months of data only by going to Inputs > Filter > Select the Last13Months filter. Repeat this step for the remaining two tables using the LastMonth filter to show the last month of data only.
Check the results against the figures in the PowerPoint report to ensure you have set-up the tables correctly.
The second PowerPoint slide consists of a sorted table, a trended table, and a text box with a base size description.
For automation purposes, it doesn’t matter on which page or where on the page the outputs are created. However to make the document easier to navigate and aligned to the PowerPoint report I created a page to correspond to each PowerPoint slide.
For that purpose, add a new page by going to the Ribbon and select Home > Pages > New Page.
To create a table which will auto-sort with each new wave of data:
- Go to this document.
- Select the table on the first page and click Home > Copy.
- Go into the Displayr document where you want the sorted table and press Home > Paste.
- Change the data selected for the table.
In case you need more than one auto-sorting table you can re-use this table by selecting the table and going to Home > Duplicate and changing the data in the duplicated table.
To create a trended table showing the last 13 months:
- Drag and drop the variable you wish to show in Rows onto the page to create a table.
- Select the table > Object Inspector > Data source > Columns > select the time variable (e.g. Month)
- Go to Inputs > Filter > Select the Last13Months filter
And, to create a Sample size description matching the PowerPoint report:
- Select Insert > More > Data > Sample Size Description.
- Click on the Complete Data Variable in the Object Inspector on the right, and choose the same variable as above
- Go to Inputs > Filter > Select the LastMonth filter
- To match the description used in the PowerPoint report you will need to customize the widget by going to Properties > R Code and replacing the last line of code with:
paste0("Sample size: n=",n)
Note that it’s not necessary to create charts to link the outputs to PowerPoint. It’s possible to use tables instead. What is necessary is to ensure that the layout of the data tables in Displayr and the PowerPoint chart match – that is that the rows and columns of tables are positioned in the same way.
To check the required layout, open your PowerPoint document, select the chart, right-click, and select Edit data.
In the above example, the table layout in Displary and PowerPoint don’t match and need to be aligned.
To do this you can either swap the rows and columns in PowerPoint or Displayr. It’s easier and faster to do it in Displayr by selecting the table and going to the Object Inspector > Inputs > Data Source > Switch rows and columns, and under Statistics > Cells > change Column % to Row %
Step 4: Linking the Displayr outputs to the PowerPoint report
The next stage of the process is linking the outputs to the PowerPoint report.
Every object that can be updated from a Displayr document has a GUID (Globally Unique Identifier code). To access the GUID select the object and go to Properties > GENERAL > GUID. Select the GUID by using your mouse by clicking and dragging and then right-clicking on it and selecting Copy.
For Displayr to update something in PowerPoint, it is necessary to record the matching GUID in the Alt Text field in PowerPoint. In PowerPoint select the corresponding object, right-click and select Edit Alt Text... Then paste the GUID into the Alt Text field as below.
Repeat this for all three objects on the first slide and the two charts on the second slide.
When it comes to the sample size description the process is slightly different as we have only set-up the second line of the text box content. If we were to add the GUID to the text box in PowerPoint Displayr would overwrite all of it.
Instead, we need to create two separate text boxes, one showing only the question label and the second only the sample size description. To do that: copy and paste the text box. In the first box delete the second line and in the second box delete the first line. Next, add the GUID information to the second box only.
Step 5: Updating the data set with the latest wave of data
You will need a file consisting of all waves of data. If your file only consists of the latest wave of data you first need to merge the files as outlined here.
To update the data set follow the below steps:
- Select the Data Set's name in the Data Tree.
- Press the Update button, and select the new file containing waves of data.
Displayr will automatically update all the outputs. In the example below, it added two waves of data, showing results up to December 2017.
Step 6: Updating the PowerPoint report with the latest wave of data
To update the PowerPoint report:
- From the Ribbon select Export > Document > PowerPoint.
- Select Update existing document and drag and drop the PowerPoint document created following the above steps into the blue box.
- Click Export.
Dispayr will scan the PowerPoint document comparing the GUID information in the document to the GUID information in Displayr. When matched Displayr will update the data.
A pop-up window will appear containing information on the changes made. In our example, Displayr updated 4 charts, 1 table, and one text box showing sample size.
Displayr will create a new PowerPoint document saving it in the same folder as the original document.
All six objects in our document have been updated showing the latest data.
If you have any questions or need help you can always contact the technical support team by dropping them an email at firstname.lastname@example.org.