1 2 Previous Next 23 Replies Latest reply on Mar 7, 2013 2:04 PM by fkoger

    How can I get my pie chart slice colors to be consistent?

    fkoger

      I have attached a file with screen shots of pie charts for 6 months. There are 4 ranges and when range is not present the pie slice colors change but I would like them to be consistent each month. Is there a way to set the range colors so they are the same for each month whether there is data or not?

      Thanks.

      Faith

        • 1. Re: How can I get my pie chart slice colors to be consistent?
          Mike_Mitchell

          Hello, Faith.

           

          FileMaker will assign the colors based on the position of the data in the data range series and which pallette you're using. Series 1 gets the first color, series 2 gets the second color, etc. If you allow the graph to assign the series dynamically, then the colors will shift as you're seeing.

           

          To get them to "stick" to a particular series, you'll need to assign the series manually. You can do that by creating the series and assigning the data using the "delimited data" option on the chart. ExecuteSQL and the List ( ) function are very handy for doing this.

           

          If you give us a little more detail on how you're generating your chart, we might can help you further.

           

          HTH

           

          Mike

          • 2. Re: How can I get my pie chart slice colors to be consistent?
            LabsRock

            Hi Faith,

            I've run up against this in a FileMaker 11 system but haven't had "the opportunity" to try it out in 12 (not sure which version your in).  This quickly went to the top of my list of weaknesses in FileMaker charting - not being much help yet, am I.

             

            Mike is correct, you can get the colors to come up consistently using delimited data.  For null values you can insert an extra return and keep things balanced out.  However, the pie chart will insert a label for those null values, and that isn't desirable in most instances.

             

            I wouldn't recommend my solution for this unless you are really pressed, and are blessed with an abundance of patience.  In as few words as possible, I created a separate table to store "color swatches" in container fields matching the colors I knew would come up in the 11 values I was reporting.  I then created a small table on the layout showing the colors and displaying the associated values for the pie chart next to the pie chart.  In the pie chart I made the value labels as small font as possible and changed the font color to white (paper).

             

            Obviously there are a few more nuances in the setup and scripting (including the ability to add two "user defined categories), but those are more than I want to try to put forth here.  If you really wanted to go this way I could provide more details, but the solution is not very flexible and only could be applied if you don't have many changes in the data you're presenting.  In my case, the data was for participation in events by individuals belonging to a fixed set of job categories.  Those job categories didn't change through time, just the numbers in each.

             

            Craig

            • 3. Re: How can I get my pie chart slice colors to be consistent?
              fkoger

              Thank you for your feedback.  I am using FileMaker Pro Advanced 12.  When I change the Data Source to delimited data, I only get 1 range instead of all 4 when I use current data.   The pie does not have series, it only has slice data. 

               

              I read in the new Using Charts in FileMaker Pro Technical Brief on page 34 that I could use a ValueCount() or scripting but I could not follow the example easily or see how to apply either method to my data. 

               

              These are some of the fields I am using:  I use the first column as the labels for the pie and the 3rd column for the slice data.  Showing values and percentages.  The data source is Current Found Set, sort order by the label and summarized groups of records.      

               

              Any further assistance would be greatly appreciated. 

              Thanks.

              Faith

               

              It appears my screen shot was not included so I am attaching it now.  Message was edited by: fkoger

              • 4. Re: How can I get my pie chart slice colors to be consistent?
                Mike_Mitchell

                In order to use delimited data, you'll need to create the individual data series manually, one for each "slice". You can do this using a variety of calculation methods. My personal favorite since the advent of version 12 is ExecuteSQL; it very nicely returns a value for exactly what you want and is context-independent (i.e., you can pick up the graph and move it anyplace you want and the calculation will still work).

                 

                In your case, you can have a fixed, return delimited list of your range labels, like this:

                 

                Range X

                Range 1-3

                Range 4-6

                Range 7-10

                 

                I would put that into a variable, say $$ranges. (You can call it whatever you want.) Then you can have each data series defined by a calculation that pulls its totals from the relevant table, like this:

                 

                ExecuteSQL ( "SELECT COUNT (*) FROM WHERE {name of range field} = " & GetValue ( $$ranges ; 1 ) ; "" ; "" )

                 

                That gets you the total of Range X (because it's the first one in the list). You put that in the data section of data series 1. Then, you use the same thing for each other range; just change the "1" to a 2, 3, 4 for every data series after that.

                 

                Hope that makes sense.

                 

                Mike

                • 5. Re: How can I get my pie chart slice colors to be consistent?
                  Stephen Huston

                  One way you may be able to force the colors to stay consistent is to use a calculate result which substitutes some tiny value (0.01percent) for any value which is missing or zero, so all items will remain in the chart but the "missing" values would just be too small to take up any visible space.

                   

                  This would require calculated data results rather than simple delimited data.

                   

                  (Your image is still not coming through.)

                  • 7. Re: How can I get my pie chart slice colors to be consistent?
                    LabsRock

                    Hi Faith,

                    Hopefully I wasn't being to short or negative in my pervious post.  The more I read about what you are trying to accomplish, the more it seems like you're walking the same path I went down.  I don't think my solution was that difficult to implement, just sorting things out the first time was difficult.  I do use delimited data which is pulled from a set of 12 global fields set with the appropriate values.  However, any method you use to get your data into a single field of delimited data would work.  You just need to also display that data on your layout - see below.  I'm attaching (or attempting to attach) a graphc showing the final report I end up with.  In your case you would need to come up with small color swatches matching the colors in your final chart - which I found was easier than I thought it would be.  This system as been installed at about a dozen sites now and seems to be working flawlessly (knock on wood).

                     

                    So long as you are charting the same number of variables each time, the order of the colors will be consistent.  That is where putting the zeros into your list is important.  Basically the order of the data needs to be the same everytime you run the report and then, in your case, the legend (showing the data) could be hardwired into your layout.  By using global fields to store my data in I was able to simply put those fields on the layout as part of the legend to show the values.  If you think this will work for you but would like more details (I'm a little pressed for time right now), I'll be glad to provide them.

                     

                    SampleReport.jpg

                    • 8. Re: How can I get my pie chart slice colors to be consistent?
                      fkoger

                      Sorry I am still not able to apply any of these suggestions to my solution.  I am not sure how to return the list of range labels as you showed.  In my label field not all the ranges appear if there are no values fitting in this range.  Would I need to add to my script or my actual pie slice as a calculation.  I am still confused. 

                      Thanks.

                      Faith

                      • 9. Re: How can I get my pie chart slice colors to be consistent?
                        Mike_Mitchell

                        Faith -

                         

                        Create a script that has this script step in it:

                         

                             Set Variable [ $$range ; "Range X<cr>Range 1-3<cr>Range 4-6<cr>Range 7-10" ]

                         

                        where "<cr>" is the pilcrow (carriage return) character.

                         

                        Then, in your chart object, set your X axis like this:

                         

                        view.png

                         

                        That should do what you want.

                         

                        Mike

                        • 10. Re: How can I get my pie chart slice colors to be consistent?
                          fkoger

                          Mike -

                          I added the script and updated the chart slice data.  Now the % are broke out evenly and not accurately.  My screen looks a little different also.  When running a pie where some of the ranges have no values, all the ranges are stil not in the label now.  I will try to add some screen prints to explain hopefully they will come through.  In my last example, when I switch the $$range to the label and the slice data back to the original field now the % are correct but the labels do not look correct.  I think I am getting closer.  Thanks. Faith

                          • 11. Re: How can I get my pie chart slice colors to be consistent?
                            Mike_Mitchell

                            Faith -

                             

                            It might be helpful if you posted a copy of your file.

                             

                            Mike

                            • 12. Re: How can I get my pie chart slice colors to be consistent?
                              fkoger

                              Mike -

                              I have attached the copy to my last post. 

                              Faith

                              • 13. Re: How can I get my pie chart slice colors to be consistent?
                                Mike_Mitchell

                                How about a copy with the passwords removed?    

                                • 14. Re: How can I get my pie chart slice colors to be consistent?
                                  fkoger

                                  I reposted an updated file. Thanks.

                                  1 2 Previous Next