colmin8r

Piece of Pie – Dynamic Pie Charts using Data Merge

Hot on the heels of my last article “Power Merging“, I’ve been trying to do things in Data Merge which I shouldn’t be able to do, such as graphs and charts. In the previous article, I demonstrated how bar graphs could be made using inline graphics and some very clever spreadsheeting in InDesign.

Well on this occasion, I’ve managed to create dynamic pie charts using Data Merge. Before getting too excited, the charts are accurate within 1% and once again relies on clever spreadsheeting, but this time rely on an array of images which make up the pie chart.

Unlike bar graphs, pie charts can’t use inline text as the pie needs to become a complete circle, so the “Binary solution” used for the bar graphs was not possible. Instead, an array of pie charts from 0% (an illustrator file containing an empty box) through to 100% (a solid circle) and all whole numbers in-between. Slices 1-99% are coloured white but once in InDesign, these images will be assigned a screen effect to go over the top of the 100% circle.

Again, Excel becomes the engine that turns the useless into the useful. The spreadsheet contains the actual raw data values; but in the next immediate column is a formula which takes that data value, divides it by the total, multiplies it by 100 (to make a percentage), rounds it off using standard rounding, and then is assigned the illustrator suffix. What this formula effectively does is tells InDesign to grab the appropriate picture on file for the piechart based on the value in the data. I’ve attached the dynamic piegraphs PDF (containing all links and original workings) so that anyone interested can reverse-engineer what i’ve done.

Ultimately, these latest forays into making InDesign’s Data Merge feature more useful was to actually shift the onus of “usefulness” away from InDesign and towards Excel which prepares the text-only data ultimately imported into Excel. This has opened my eyes to the many functions available in Excel to make data more useful, such as If-Else statements.

The next challenge? Line graphs maybe?

5 comments

  1. Hey!

    I’m a university graphic design student, I am currently working on an interactive design project, I have to create a smart phone application. My idea involves a spreadsheet which would then be used to create a pie chart.

    I’ve read your article and was wondering if you could go into a little more detail as to how to create the illustrator files and how to link the data to create the percentage on the pie chart, also would the data be variable so if the numbers in the spreadsheet change would the pie chart be updated?

    This would be a massive help as I can’t seem to find any information on this at all.

    Best wishes
    Matt Wilson

    • g’day there.

      I’d actually written how to in the article. In short, there are 100 circle wedges which were made by segmenting a circle into 100ths and then saving out all the segments – this part took ages. The spreadsheet is an excel sheet using formulas which are not complicated. The trick to making the circle wedges change colour to denote different values is the Multiply effect within InDesign. Without this effect, the solution won’t work.

      The PDF with the example has attachments of all the files that I used to accomplish this task – look for a paperclip icon within acrobat and you should see all the files I used to make this “proof of principle”. In terms of the data changing and having the piechart change, then yes that is what this proof of principle is meant to demonstrate, that not only can Adobe InDesign display percentage values, but also represent them in unique graphs for each entry.

      After writing this, I learnt that Adobe Photoshop and Illustrator have a similar feature called Data Sets which is used for making Data Driven Graphics – anything from banner ads which allow pictures or text to change; to graphs. Because they do not integrate well with Adobe InDesign though, and there seems to be few resources on combining Illustrator/Photoshop Data Sets with Adobe InDesign, I needed to prepare a solely InDesign-based solution for variable graphs which could be used for Direct Mail campaigns.

      However, this is an Adobe InDesign solution, and I don’t think this solution is applicable for a smart-phone app. Something like this would require coding and is beyond my scope.

      Hope I have been of some assistance.

  2. Hey

    Sorry for the late response been busy trying to get my head around this problem!

    Thanks for the detailed response it helped a lot but it still won’t work for the sort of thing I am trying to achieve but thanks anyways!

    Matt Wilson

This site uses Akismet to reduce spam. Learn how your comment data is processed.