• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Pie graph with labels using data merge

Community Beginner ,
Mar 27, 2017 Mar 27, 2017

Copy link to clipboard

Copied

Good morning. I'm hoping someone here can help. I have a pie graph, which uses data merge. The pie graph requires labels, not in a key, but on the outside of the graph. The labels use the same data merge fields as the graph. As the values in the pie graph change/move, we want the labels on the outside to move also. I've tried grouping the text boxes, but the values remain stationary and I have researched for a few days and haven't found a solution that works. Is this possible in InDesign?

Views

6.5K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , Mar 31, 2017 Mar 31, 2017

jleigh987  wrote

… Uwe, I started reading about text-to-paths and to see if I can separate the fields. I didn't have enough time to accomplish anything, but will hopefully be able to return to that on Monday also.

Hi Jodi,

you need to do nothing with the converted text-to-paths.


My script will convert text to path without changing the original text formatted with Chartwell Pie.
And it will throw away the group of paths after the calculation of labels is done.

And more: You even need not provide any te

...

Votes

Translate

Translate
Community Expert ,
Mar 27, 2017 Mar 27, 2017

Copy link to clipboard

Copied

Hi,

I cannot think of a procedure where you can achieve what you want with data merge.

If I understood your request right, only a custom script would be able to do that.

How is the pie chart done? Adobe Illustrator?

Can you show a screenshot of that pie chart with attached labels?

Before the merge. After the merge. Hidden characters and frame edges showing.

Use the forum's "Insert Image" to do an inline graphic with your post. JPEG and PNG file formats.InsertInlineImage-ScreenCapture-AdobeForums.png

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 27, 2017 Mar 27, 2017

Copy link to clipboard

Copied

Hello! Thank you for the reply. The graph was created in InDesign using the Chartwell font. I moved my graph into a new document and created some fake data to represent what we are attempting to do. I don't see an option to attach the indd file and the data? I have added screenshots below. Thank you for the "insert image" help too

Before using preview:

IDF1-17-0327.jpg

Preview, record 1, the labels (6, 18, 76) are accurate with the pie sections:

IDF2-17-0327.jpg

Preview, record 2, the labels are not accurate with the pie sections:

IDF3-17-0327.jpg

Preview, record 3, the labels are accurate with the pie sections, but only by chance:

IDF4-17-0327.jpg

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Mar 27, 2017 Mar 27, 2017

Copy link to clipboard

Copied

As Laubender​ touched on - I think this would also require use of custom script to do the way we have understood you.

Otherwise - as I'm sure you are aware - you are looking at a manually updating the charts either by:

  • Directly copy and pasting the file into InDesign and repeating whenever needing updates if there are only a one or two in use.
  • Copying and pasting to Illustrator if you want to make the chart prettier then placing that into InDesign.
  • Exporting the charts from Excel to PDF using the "Save as Adobe PDF" feature and then selecting all sheets that have charts... Then placing this into InDesign. You would need to remember that each data sheet from e.g. Excel would act as a flattened image. Therefore only include one chart per sheet.

However without a script you would have to repeat this every time. So scripting is again the best method for now.

You could submit a feature request if you think that you should be able to link and place charts directly from excel into InDesign.

Feature Request/Bug Report Form

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Mar 27, 2017 Mar 27, 2017

Copy link to clipboard

Copied

Ahh this is what happens when you get distracted and post a reply late!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 27, 2017 Mar 27, 2017

Copy link to clipboard

Copied

Hi

If I had to do it, I would forget FF Chartwell and do it all in Excel:

piec.jpg

export.jpg

Then create a custom Macro. Something like this one would save all graphics, then save "Export" sheet as .txt or .csv for data merging.

Sub Test()

Dim sht As Worksheet, cht As ChartObject

Dim x As Integer

    For Each sht In ActiveWorkbook.Sheets

        x = 1

        For Each cht In sht.ChartObjects

            cht.Chart.Export "C:\Users\vs\Desktop\test\" & sht.Name _

                              & "_" & x & ".jpg", "JPEG"

            x = x + 1

        Next cht

    Next sht

   

    Sheets("export").Select

    ActiveWorkbook.SaveAs Filename:="yourpath\graph.txt", _

        FileFormat:=xlText, CreateBackup:=False

       

        ActiveWorkbook.Close savechanges:=False

End Sub

I fully understand that:

- this does not answer the OP's request

- this would rasterize everything

- this is not an Excel forum ^^

- and that I forgot to add the .JPG extension in my example. I should correct this, but I got to go...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 27, 2017 Mar 27, 2017

Copy link to clipboard

Copied

Thank you for your response and apologies for not responding right off. I kept receiving a redirect error when logging into the forum. I will try your suggestion! It has to be less painful than moving the fields for every unit, which is where the path is leading to currently.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 27, 2017 Mar 27, 2017

Copy link to clipboard

Copied

Hi together,

to the OP: Thank you for posting your screenshots and to mention that the charts were done with Chartwell.

Vinny's approach is looking good.

But it comes with some problems and some plus points as well !

Problem 1: RGB colors perhaps.

Problem 2: Pixel graphics. But only perhaps. Maybe there is a way to save to PDF and get vector ones?
Can resolution be controlled? A big question mark here, because the labels would also be rasterized.

On the plus side:
Plus 1: Controllable labels.

Plus 2 ( and that's a very big plus for a lot of users 😞 Support of floating numbers.
Chartwell will not support floats as far as I know. Just integers. And that's a big minus on the Chartwell side of things.

So back to the Chartwell solution if missing floats are no issue:

Would it be possible to transform a chart done by Chartwell to paths using InDesign's text-to-path feature?

Doing a duplicate on top of the selected text maybe could be done by holding the alt key when using the menu command.
Did you ever tried this?

I'm looking for a chance, that a script is able to do the labels in a post-processing step after datamerge.
For that we could need—maybe not necessarily?—the individual segments as individual objects.

The text contents for the individual labels would be clear from looking at the range of text Chartwell is forming the graph from.
And also is provided by the datamerge.

Don't know, but maybe someone already tackled the problem somewhere on the net?

It seems, that doing a proper legend with Chartwell generated graphs is a pressing need for all people using the font.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 28, 2017 Mar 28, 2017

Copy link to clipboard

Copied

Uwe, you are correct. I played around with Vinny's suggestion, but we realized this will not work for us either. In Excel, the percentage values moved to the inside of the pie on occasion and the quality isn't as crisp as we need it to be for print production.

I tried grouping the four text boxes and also anchoring, but neither worked for this issue. I should also mention that I am teaching myself ID. I have a lot of experience with Photoshop and Illustrator, so not a huge learning curve, but enough that Google and I are best friends. I have not tried the text-to-path feature and duplicating on top of the selected text. I will do some reading.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 28, 2017 Mar 28, 2017

Copy link to clipboard

Copied

Hi,

just test the following to convert text to path as duplicates.

First have a test on text not formatted with Chartwell to get used to it…

If you are comfortable:
[1] Select the text that is rendering the pie chart.
[2] Go to menu Type > Convert To Path but do not immediately release the menu command.

[3] Hold down the alt key ( when on a Mac )—I see you are not, so press the modifier key for making duplicates—and click the menu action.

If all goes well the selected text should be converted to paths as duplicates.

You now should have access to a combined path object with several path segments of the pie chart.
If that would not work you may end up with something like this converted to paths: 76 + 6 +18
( Don't know where Chartwell starts with the pie segments. At 12 o' clock position perhaps and building the pie segments clockwise… )

Then a script would need other means to identify the center of the chart and its diameter.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 28, 2017 Mar 28, 2017

Copy link to clipboard

Copied

Hello Uwe,

Thank you for your suggestion and instructions. You are correct. The first field, using the Chartwell pie font, begins at 12 o'clock. I'm not exactly sure what you're talking about, but I will follow your instructions and report back!

Jodi

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 29, 2017 Mar 29, 2017

Copy link to clipboard

Copied

Hi Jodi,

please ask specifics if you did not understand something.

What I really want is to explore some options with doing a script that could run after the datamerge is done and applies the labels to all merged charts in one go. And as a start it could be of opportunity to get access to pie chart segments of the actual charts without calculating too much. I do not have Chartwell fonts available to me so I am relying a bit on guesswork and some videos on the web that are showing Chartwell at work.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 30, 2017 Mar 30, 2017

Copy link to clipboard

Copied

Hi guys

I'd like to come back to the Excel workaround.

Possibly Uwe's solution will work great for you but maybe other users - not using Chartwell - may be interested in an Excel workaround.

So basically, we had two issues: (not talking about RGB... not an issue really)

- Quality/rasterization:

Well, afaik you can't save a chart only in PDF but you can place the chart in an empty sheet and export the sheet as PDF. This requires quite a few adjustments, so I won't detail it. You can find many macros Googling "Excel VBA export PDF". Here's one: Excel VBA to Save As PDF

However, you can very easily save a high quality image increasing the zoom in the VBA macro. In the example below, I used PNG instead of JPG.

- Label positioning:

That was the tricky part. However, everything is explained in this great tutorial: https://teylyn.com/articles/excel-doughnut-chart-with-leader-lines/

Don't forget to set the lines as invisible if you don't want them.

The idea is to create a single chart, and to write a macro that does:

1- change the source data of the chart

2- export the chart in high-quality PNG

3- copy the name of the file on an "export" sheet

4- loop to 1 as long as there are existing data

5- save the xlsx file

6- Finally export "export sheet" as tabbed .txt for data merging

Here it is:

Sub test()

'

' test Macro

'

' Set a non-empty rows counter (based on column data1):

Dim rows As Integer

rows = (Application.WorksheetFunction.CountA(Columns(2)) - 1)

' Set a variable for looping through chart series formula:

' Set to 2 in this example because 1 is the header

Dim i As Integer

i = 2

'While loop: non-empty rows

While rows > 0

   

    '   Select graph and change range values:

        ActiveSheet.ChartObjects("Graph").Activate

        ActiveChart.SeriesCollection(2).Select

        Selection.Formula = "=SERIES(,,datas!R" & i & "C2:R" & i & "C4,2)"

        ActiveChart.SeriesCollection(1).Select

        Selection.Formula = "=SERIES(,,datas!R" & i & "C2:R" & i & "C4,1)"

    '   Export graph as image:

    '   Increase next line zoom for higher size

        ActiveWindow.Zoom = 175

        ActiveChart.Export "C:\Users\vs\Desktop\graphs\" & range("E" & i & "").Value, "PNG"

        ActiveWindow.Zoom = 100

   

    ' Paste and copy image name to export sheet

        Sheets("datas").Select

        range("E" & i).Select

        Selection.Copy

       

        Sheets("export").Select

        range("A" & i).Select

        Selection.PasteSpecial Paste:=xlPasteValues

       

        Sheets("datas").Select

    '   iterate:

        i = i + 1

        rows = rows - 1

Wend

'save file

    ActiveWorkbook.Save

'Now lets select "export" Sheet and save a tabbed .txt file for data merging. Then close it

    Sheets("export").Select

    ActiveWorkbook.SaveAs Filename:="C:\Users\vs\Desktop\graphs\graph.txt", _

    FileFormat:=xlText, CreateBackup:=False

    ActiveWorkbook.Close savechanges:=False

   

End Sub

Don't forget to name your chart (see line 22)

Here's a link to the excel file (but... French version!) : https://we.tl/7g1i15g2DM

Final result:

final.gif

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 30, 2017 Mar 30, 2017

Copy link to clipboard

Copied

Hi Vinny,

great! Unfortunatey I'm on Mac OSX so VB seems to be no option for me.
Or is Excel on a Mac supporting enough VB to test your script? Provided the file paths are changed of course.


Behind the scenes Jodi provided one chart converted from Chartwell formatted text as duplicate to outlines. That made me confident enough to start writing a script in ExtendScript for InDesign that is doing the labeling for Chartwell pie graphics …

And I'm making good progress with that.

Just a feasibility study for now.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 30, 2017 Mar 30, 2017

Copy link to clipboard

Copied

Hi uwe.

I have absolutely no idea on how Excel macros work on a Mac... What I know is that the formulas use the French functions, so they must be replaced with local ones.

Keep on study the chartwell solution... That's cool!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 30, 2017 Mar 30, 2017

Copy link to clipboard

Copied

Vinny, that is awesome! I exported images as gif and jpg a couple of days ago. I toyed with trying to export the maps as PDF, but I decided not to because I thought that would bring me back to one-offing using "place" functionality right?

I may not be able to return to working on this until Monday, but I want to see if I can export PNG (the map needs to remain on a transparent background) images and will check the quality. French functions might be an issue also, but maybe my buddy Google can help with that as well.

Uwe, I started reading about text-to-paths and to see if I can separate the fields. I didn't have enough time to accomplish anything, but will hopefully be able to return to that on Monday also.

Thanks to both of your for your help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 31, 2017 Mar 31, 2017

Copy link to clipboard

Copied

Hi Jodi

If you want to export a transparent PNG, don't forget to remove both default white background and stroke of your chart.

I've tested it, and it works fine

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 31, 2017 Mar 31, 2017

Copy link to clipboard

Copied

jleigh987  wrote

… Uwe, I started reading about text-to-paths and to see if I can separate the fields. I didn't have enough time to accomplish anything, but will hopefully be able to return to that on Monday also.

Hi Jodi,

you need to do nothing with the converted text-to-paths.


My script will convert text to path without changing the original text formatted with Chartwell Pie.
And it will throw away the group of paths after the calculation of labels is done.

And more: You even need not provide any text frames before the datamerge process for the labels in the template.
The script would create them on-the-fly using an object style one could provide with the InDesign template document.

Below just a sequence of screenshots showing a sample that is working with a selection of the text frame holding the chart.
The contents of the frame is: 76+6+18+B

Means, the chart is build with pies from values of 76%, 6% and 18% of a circle. B means make a "ring" form. This is not showing here, because after doing text to path with the text the inner circle is stacked behind the pie segments. All paths are grouped.

I'm showing the converted paths only, because I have no Chartwell font at hand and doing all calculations from the formatted text contents provided.


The typography of the label and the look of the labels' text frames is dictated by an object style provided in the document:

"ChartwellLabels". The applied paragraph style for this style is named "ChartwellLabel". If the object style is not there the script will build a default style you can later change to your needs.

1. Before doing the script action

To run the script select a text frame holding the necessary information: 76+6+18 for calculating the angle of the label text frames. You cannot see the contents here for demonstration purposes. Usually it would be in color to dictate the fill color of the pie segements.

Note: To make the script action work, the selected text frame must be not overset.

BuildLabels-ChartwellChart-v-0.0.2-1.png

2. After running the script.
I assume, that the label text frames should be positioned to the half of the angle of every individual piece of pie:

BuildLabels-ChartwellChart-v-0.0.2-2.png

3. The label frames are formatted by the object style.
in my German InDesign you can see the Object Styles panel where object style "ChartwellLabels" is selected.

BuildLabels-ChartwellChart-v-0.0.2-3.png

4. The text is formatted with a paragraph style that comes with the object style: "ChartwellLabel".

BuildLabels-ChartwellChart-v-0.0.2-4.png

5. As you can see below, the text frames of the labels are all centered around the pie chart.
The center of each text frame is on an imaginary outer circle.

BuildLabels-ChartwellChart-v-0.0.2-5.png

6. After changing the text size the center of text frames is still on the outer circle.
This effect is possible because the object style is coming with a fit to center setting.

BuildLabels-ChartwellChart-v-0.0.2-6.png

But please do not hold your breath, the script is not ready yet.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 24, 2018 Apr 24, 2018

Copy link to clipboard

Copied

Hi Uwe.
The script you mentioned in comment was released? Or still in progress?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 24, 2018 Apr 24, 2018

Copy link to clipboard

Copied

Hi Ahmet,

you can contact me through private message regarding the script.

Just hover over my name, wait a second and then press the Message button.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 22, 2022 Jul 22, 2022

Copy link to clipboard

Copied

LATEST

Hi.. Is the script still available?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 04, 2017 Apr 04, 2017

Copy link to clipboard

Copied

Hi Vinny, I didn't have time to play yesterday, so I haven't tested the script with PNG output output yet. I will try to fit this into my schedule this week so I can report back on the results because I think this can be a workable solution if the printed output looks okay.

Hi Uwe, the screenshots are amazing and your assumptions are correct. The script calculates the position, in the outer circle, on-the-fly? Mind blown.

I apologize to you both for not being able to focus on this yet!

Jodi

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines