2009 has been a year of sharing here at Juice. First there was our long-used DTP methodology for interactive Excel reporting. Then we released our JuiceKit SDK. Today, I want to share another bit of trickery we've used to solve a common PowerPoint presentation problem.

The challenge

Let's say you need to produce the same presentation month after month, updating the data each time. Or maybe you have a set of slides that need to go to a bunch of different audiences each with their own specific market, product, business line, or industry.

Updating all the slides by hand can be tedious, slow and error-prone. The presentation is basically the same, you simply want to swap out the underlying data. You need something that acts like a "mail merge" for PowerPoint.

Our approach

When we've helped clients with this situation, our approach has been to create re-usable PowerPoint slides (i.e. templates) that link directly to a database. This gives us the ability to stamp out new presentation by changing the raw data underneath. Simple enough to say; not quite so simple in practice. Here are a few of the hairy bits:

1. Data structuring. We populate the data into a Windows-accessible SQL database such as MS Access or SQL Server so we can use SQL queries to define the data needed for our charts and tables.

2. Slide templates. We create slides with charts, tables, and text boxes that are formatted to account for the variance in the data that may need to be displayed. Ensuring that the charts always look good is surprisingly hard.

3. Connect templates to data. Originally we rolled our own solution by creating a "templating" language that we embedded in the notes section of the slides. More recently, we discovered PTReportGen, a tool that extracts data from a data source and populates it into PowerPoint. PTReportGen allows you to connect objects in the slides (i.e. charts, tables, text boxes) to results from SQL queries from our data source. For each slide, there is a .PTR file that connects the contents of the slide to the database.

4. Scripted production. PTReportGen gives command line control, allowing us to write Python scripts to cycle through our data and populate the charts and tables in our template slides. Because we are interested in generating dozens (sometimes hundreds) of versions of a single slide, our script iterates over the database to pull different results across multiple dimensions. Below is a bit of pseudo-code to give a sense of how the scripting works to produce slides by market and by demographic:

markets = ('Market1','Market2','Market3')
demographics = ('Demo1','Demo2','Demo3')
PTRFileName = 'C:\Documents\UserName\Desktop\MyReportGenerator.ptr'

for demo in demographics:
    for market in markets:
        ReportFileName = 'PathName\FolderName\demo\market.ppt'
        cmd = 'PPTReport.exe PTRFileName -demo -market'

1. Post-processing. While most chart and data table designs can be achieved by clever template layouts, some advanced designs involve additional intervention to achieve the desired level of polish. A python script combs through the result template and adds coloration and layout improvements.

It isn't simple, but once constructed this "slide factory" is a valuable capability that can free up an enormous amount of time from presentation grunt work. Here's a short video that gives you a sense of what the process looks like. Personally, I find the production of slides vaguely hypnotic.

Other approaches and resources

We are not the first people to encounter or solve this problem. Below are a few other resources on the topic. I'd be curious if there is a native MS Office solution that I could include in this list.

• PowerPoint Automation Toolkit: "With the PPTATK, PowerPoint becomes a best-case union of a presentation tool and a report writer. With the Tookit, you can build presentations which combine static slides from a slide library and data-driven slides which display charts, tables, and graphs from structured data sources."

• PresentationPoint: "Generate new up-to-date multimedia reports with 1 click only - put real-time data in your presentations."

• Microsoft Help: "Working with PowerPoint Presentations from Access Using Automation. Create a PowerPoint slide presentation from scratch using Access data."

• Stack Overflow discussion on "PowerPoint Automation from MS Access…queries to chart?"