18 Replies Latest reply on Apr 4, 2017 8:57 AM by jleigh987

    Pie graph with labels using data merge

    jleigh987

      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?

        • 1. Re: Pie graph with labels using data merge
          Laubender Adobe Community Professional & MVP

          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

          • 2. Re: Pie graph with labels using data merge
            jleigh987 Level 1

            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

            • 3. Re: Pie graph with labels using data merge
              Eternal Warrior Adobe Community Professional

              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

              • 4. Re: Pie graph with labels using data merge
                Eternal Warrior Adobe Community Professional

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

                • 5. Re: Pie graph with labels using data merge
                  vinny38 Level 4

                  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...

                  4 people found this helpful
                  • 6. Re: Pie graph with labels using data merge
                    jleigh987 Level 1

                    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.

                    • 7. Re: Pie graph with labels using data merge
                      Laubender Adobe Community Professional & MVP

                      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

                      1 person found this helpful
                      • 8. Re: Pie graph with labels using data merge
                        jleigh987 Level 1

                        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.

                        • 9. Re: Pie graph with labels using data merge
                          Laubender Adobe Community Professional & MVP

                          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

                          1 person found this helpful
                          • 10. Re: Pie graph with labels using data merge
                            jleigh987 Level 1

                            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

                            • 11. Re: Pie graph with labels using data merge
                              Laubender Adobe Community Professional & MVP

                              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

                              • 12. Re: Pie graph with labels using data merge
                                vinny38 Level 4

                                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

                                1 person found this helpful
                                • 13. Re: Pie graph with labels using data merge
                                  Laubender Adobe Community Professional & MVP

                                  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

                                  • 14. Re: Pie graph with labels using data merge
                                    vinny38 Level 4

                                    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!

                                    • 15. Re: Pie graph with labels using data merge
                                      jleigh987 Level 1

                                      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.

                                      • 16. Re: Pie graph with labels using data merge
                                        vinny38 Level 4

                                        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

                                         

                                        • 17. Re: Pie graph with labels using data merge
                                          Laubender Adobe Community Professional & MVP

                                          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

                                          2 people found this helpful
                                          • 18. Re: Pie graph with labels using data merge
                                            jleigh987 Level 1

                                            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