Data Merging Charts and Graphs with FF Chartwell

lumbergh

The Data Merge feature of InDesign is great for merging text, but cannot take the text and parse it into a graph or a chart. This feature may be available through plug-ins purchased separately to Creative Suite/Cloud, but having the ability to create data merge projects that feature variable graphs or charts using only InDesign would be welcomed by many users.

In 2011, this site provided a proof of principle that pie charts and bar graphs could in fact be created via InDesign, Excel and Illustrator. Those interested can see those articles here and here.

Despite proving a point, the technique had several flaws:

  • The Excel files contained many formulas that were very complicated for the average user and likely to cause problems if the original database was replaced at any time;
  • The appearance of the graphs were limited to the graphics created in Illustrator, meaning any changes to the appearance of the graphs were complicated;
  • With the setup of the placed images used to make the charts, only one kind of graph could be merged at once.

It would be almost a year until InDesignSecrets co-host David Blatner wrote this piece concerning a solution using the FF Chartwell font created by the FontFont foundry:

Prior to this post, the FF Chartwell font had been considered as a solution but after reading David’s article, the issue was revisited. Reading the instructions for the FF Chartwell font looked promising, and the decision was made to bite the bullet and purchase the font.

Using the font (full instructions are available with the purchase of the font but can also be found here and this video here) has several advantages over my previous solutions:

  • Easy to set up;
  • The same data can presented using different charts in the same data merge record;
  • The appearance of the chart can take advantage of GREP styles, Nested styles, and the effects dialog box;
  • PDF processing time is faster with smaller size PDFs as a result.

There are some limitations that should be spelled out prior to a purchase of this font:

  • Most charts represent figures from 0-100. This is fine for percentile charts such as pie, rose, ring or radar charts, but limits the use of bar and line graphs;
  • While the font allows a pie graph to turn into a donut, be aware that the hole is made using a fill colour rather than being transparent;
  • The data in the charts must be integers (e.g. whole numbers, no fractions) and this means rounding results up or down accordingly. For percentile charts it is also important to make sure the total of figures in the data adds up to 100.

Tweaks or hacks to further improve the charts

There is a bar graph that represents figures from 0-1,000; but the graph appears from left to right and starts with a diamond shape. For those wanting a usual bar chart, here is the workaround.

  1. Use the Chartwell bars font to represent the number between 0-1000, and format according to the Chartwell instructions; but change the fill/stroke of the font to none.
    mergepic1
  2. In the paragraph palette, select paragraph rules and then select above line to the size of the text, using whatever size is felt necessary.
    mergepic2
  3. Now that the chart displays as rectangular start/finish bars, change the rotation of the textbox that contains the chart to 90 degrees rotation.
    mergepic3

A similar solution can be used when using a segmented bar graph, but instead of using the paragraph palette, use the character palette to create individual underlines for each segment. This can be further improved upon using GREP styles.

The illustration below was a bar graph using a paragraph style that had both above and below strokes, and the bottom has been clipped by putting the text box into its own frame.

mergepic3a

To make a piechart have a true donut hole rather than a solid circular fill (as shown in the picture below) follow these steps:

mergepic4

  1. Draw a circle the size of the desired hole and place it where the hole has to appear in the pie chart. Give this hole a paper fill and using the effects palette, set the opacity to 0%
  2. Select both the drawn circle and textbox that contains the pie chart and group them
  3. From the effects palette, check the checkbox that says “Knockout Group”

 

 

Advertisements

Perform multiple find/change (or GREP) queries at once

The find/change dialog box is a useful tool in InDesign until many searches need to be done at once. For example, an imported word processor file contains double spaces, double returns, spaces at the start of lines etc that need to be tidied. Using solely find/change, each item needs to be found and changed before the next item can be found and changed, meaning typing in the find field, then the change field, then change (or change all). This applies to GREP searches as well.

Ultimately, is there a way to perform many find/changes to text at once? Luckily, the answer is yes, there are several. Will they also change more than the text (such as formatting), not all methods outlined will do this. Some solutions listed below are still in Beta testing, while others may or may not work with Creative Cloud. Links to the providers of each solution has been provided for those seeking more information.

First method – default script (with outside assistance)

There is a script that ships with InDesign called findchangebylist.jsx. This script runs many find/change commands that are stored as text in another file called FindChangeList.txt.

By changing specific lines in the script and making and renaming copies of the FindChangeList.txt file, many different “chained searches” can be made and stored for later use.

Because the code used in the FindChangeList.txt file is rather cody, scripter Kasyan Servetsky has created a script that will take whatever is in the find/change dialog box and turn it into code that can be cut and pasted into the FindChangeList.txt file.

For regular visitors to this site, this article may seem like déjà vu, and that is because this solution has been discussed on Colecandoo before:

https://colecandoo.wordpress.com/2011/08/25/make-findchange-behave-more-like-a-word-macro/

Second method – scripts:

GREP Queries Manager by Peter Kahrel

Unlike the first method, this has a user interface and deals with GREP searches rather than both GREP and find/change searches.

For full details on how it works, go to: http://www.kahrel.plus.com/indesign/grep_query_manager.html

Doquery by Mikhail Ivanyushin

Again, this has a user interface and can use find/changes or GREP searches that have been previously used and saved with the find/change dialog box.

For full details on how it works, go to: http://adobeindesign.ru/2012/10/27/doquerylist-programma-obrabotki-teksta-zaprosami/

Batch find and replace by Fabiantheblind

This solution does not have a user interface but rather works in a similar fashion to InDesign’s default script but uses Fabian’s own expression language.

For full details on how it works, go to: https://github.com/fabiantheblind/batch-find-and-replace

Kerntiff’s Xchange

This solution has a user interface and can perform many find/changes at once, whether regular find/change or GREP.

For full details on how it works, go to: http://www.kerntiff.co.uk/free-stuff/xchange-strings-xstrings

Third method – plug-ins

Automatication

This plug-in has a user interface and what the find/change dialog box in InDesign should really have shipped by default. Nevertheless it is cheaper than a slab of beer and will last much much longer.

For full details on how it works, go to: http://automatication.com/

Action Recorder (Rorohiko)

While still in the Beta stage, Rorohiko is attempting to make what is arguably missing from the InDesign menu: the Actions palette.  Other than running find/change commands, this extension allows much more chaining and automation of other non-text based tasks, but at the time of writing is still too early to tell.

For full details on how to become involved, go to: http://www.rorohiko.com/wordpress/2013/08/06/action-recorder-beta-released/

 

Features or Speed… why not both InDesign?

porquenolosdos

After a recent post discussing the complications of indexing a rather large InDesign file (1600+pp), it is worth mentioning another issue encountered with the project, namely the reduced speed of InDesign.

There are already several articles concerning “slowdowns” while working with InDesign, namely:

http://indesignsecrets.com/why-is-indesign-soooo-slow.php

http://forums.adobe.com/message/4815713#4815713

Put simply, the larger the file became, the harder it was to work on. Little things such as placing the text cursor between words resulted in a spinning beachball of death for five minutes before the cursor once again became a text cursor.

Disabling several features in InDesign made the file somewhat workable:

1. Display performance – to these settings :

slowdown01
2. Pages panel – turn off the preview for the actual pages:

slowdown04
3. Turn the preflight panel off:

slowdown05
4. Live screen drawing – never:

slowdown03
5. Dynamic spelling off:

slowdown02
6. Do not use GREP styles or Nested styles
7. Do not use index entries or cross references

While this made the file workable, it was at the expense of the good features of InDesign. The slowdown was to the stage where using a word processor to accomplish the same task was considered!

Within the preferences, there is no ability to control the amount of RAM reserved for InDesign to use, nor is there the ability to control how often InDesign autosaves to its backup… something that is possibly slowing InDesign down even further.

Some of the features of InDesign were not necessary for this task as the project was completely black text supplied by the client, so having a lower quality display performance without seeing the page previews was not an issue. Preflight was not a concern with this particular file given that it was black text within one rather long text-frame and the spelling was to remain as the client provided the artwork.

Initially there was concern that turning GREP styles off would limit the control of “runt lines”. GREP styles had also been used for formatting of particular words, but because no type was going to be added, performing a one-off find/replace using the relevant GREP was able to remove the need for GREP styles. It was amazing to see the difference in speed when the file had the GREP styles turned on, opposed to when they were not applied – in this project the GREP styles were a major contributing factor to the file’s slow performance.

Followers of this blog will be familiar with several GREP styles that have been used to correct names or details within variable data campaigns. After this experience, it would appear that GREP styles are better suited to projects where content will be added dynamically (such as a Data Merged file) or constant alterations need to be made; rather than static documents – especially where no new content will be added.

The project DID have to be indexed (as discussed in a previous post) and found that once the file was indexed, the speed of the file slowed to a crawl.

Wrangle up InDesign index entries… without InDesign.

A recent project involved creating an enormous index… in fact there were over 100,000 index entries to create.

Creating index entries is normally a chore. To create just one index entry, the normal procedure is to:

  • Highlight the text to be indexed
  • Select “New Page Reference” from the index palette (or command + 7)
  • Enter the details and click Add (or Add All) then OK

indexref1

indexref2

In a normal book, indexing is something that is done carefully by the author or staff dedicated to the task – entries in the index often refer to certain instances of a word rather than every instance of its use. This project however used the index as a lookup table instead, so the more advanced features of the index palette (e.g. see also references, index levels) were not necessary.

For this project, the items to be indexed were restaurant names. The name appeared in the same line as the description, so using a Paragraph Style to identify the item for an index entry could not be used. However, the restaurant names DID have a Character Style associated with them.

Because there were 100,000 index entries in this book and each entry had its own character style, there were easier methods to perform this task. There are several scripts online that can create index entries from character styles:

For this project, because there were two character styles used to identify the restaurant names, I used Peter Kahrel’s script. While testing the script on a sample chapter, everything appeared to work correctly… it took time but all the names in character styles were added to the index.

However, when the time came to apply this script to a document 1,628 pages long, the script would run, and then the spinning beach-ball of death would appear. Assuming I was not allowing the script enough time to finish its tasks, an attempt was made to let the script run over a weekend on the fastest machine in the office. Sadly, this did not work. Put simply, there were just too many entries for the machine to handle.

Enter Textwrangler…

Luckily, all the text for this project, while many pages long, was all in one text frame. This provided the option to enter the index entries while the document was in a raw text format. To do this, the text was exported as an Adobe InDesign tagged text file by placing the cursor anywhere in the text and selecting File/Export (command + E).

indexref3

The newly saved text file was then opened in Textwrangler. The a find/change using Textwrangler’s GREP was then made for the following:

indexref4If the code is hard to see in the picture, here is the type:

Find:

(<cstyle:placename>)(.+?)(<cstyle:>)

*placename above refers to the style to index.

Replace:

<Idx:=<IdxEnType:IdxPgEn><IdxEnRngType:kCurrentPage><IdxEnDispStr:\2>>\1\2\3

(make sure the Grep checkbox is ticked)

Once the changes in the text files were saved, the type was imported in place of the old text in the InDesign file, and within moments the document was completely indexed as required.

The only other part that took time was to run the “Generate Index” function from InDesign itself, and considering the amount of index entries in the document, took an hour to generate.

%d bloggers like this: