4 Replies Latest reply on Aug 11, 2014 7:24 AM by johncampbell

    Cant find a way to get counts on pie chart

    johncampbell

      Title

      Cant find a way to get counts on pie chart

      Post

           I have a project management DB where there is a table of projects related one-to-many with a tasks table. Each project has a project manager (=user) and a set of tasks. Each task has a status (closed, open etc).

           The requirement is that when a user logs in, they are taken immediately to a Project Manager Dashboard which lists their active projects (no problem), shows a bar chart of the revenue for each of those projects (no problem), and shows a pie chart of the counts of the Task Statuses across all those projects (problem).

           The charter is only showing the Task Status for the currently selected record, and even then shows each task staus separately in the pie (i.e. I get a slice for each task). What I want is a total for a given task status, for all the tasks, across all the projects in the found set. So the pie would show for example a total of 10 tasks in status Open, 30 tasks in Status closed etc etc.

           I can't see a way to tell it to use counts.

            

           The charts are showing in a Footer part.

           Chart data = related records

           Related table = Tasks

           Category Label = Tasks::Status

           Slice Data = Tasks::Status

           Thanks for you help!...John

        • 1. Re: Cant find a way to get counts on pie chart
          philmodjunk

               The main problem is with your data source options for you chart. The chart you describe is typically created on a layout based on the table storing the data to be charted, tasks in your case. The y series (wedge size) would specify a "count of" summary field and you would sort your records by task status to group your records by status and thus get one wedge for each status instead of one wedge for each task. You data source options need to be the ones for "found set" and "summarized data".

          • 2. Re: Cant find a way to get counts on pie chart
            johncampbell

                 In fact the dashboard is based on the project table, in order to list the active projects, and show the project revenue chart. In this scenario it seems when I choose the suggested "found set" and "summarized data" options, I lose the sorting associated with "related records" (I lose the option). Do I have to create a summary field for each task status at project level to get this to work, surely not?

                 Mistakenly said I was on FM Pro 12, but it's 13, sorry for that.

            • 3. Re: Cant find a way to get counts on pie chart
              philmodjunk

                   That's one of the key points that I was trying to draw your attention to. In my last post, I stated: "The chart you describe is typically created on a layout based on the table storing the data to be charted, tasks in your case."

                   If your layout is based on projects, the above method doesn't work and there is no simple way to get the wedges to represent summarized data.

                   It can be done, but it's not simple.

                   One option is to use a script to generate the chart on the tasks layout and then copy the chart image into a container field in Projects. You can use GetLayoutObjectAttribute to access a chart's image.

                   Another is to use the delimited data option and use Execute SQL to create some return delimitted subtotals--one for each task status.

                   A third is to set up a related table where you have one record for each task status. These records then link to your task records by ProjectID and TaskStatus. A calculation field using count can count the records in tasks with the matching project ID and status and you now have one related record for each wedge of your pie chart where your wedge size will refer to this count calculation in the related table.

              • 4. Re: Cant find a way to get counts on pie chart
                johncampbell

                     Thanks.

                     As you say, it's not simple. This is clearly not something FM is currently set up to do, so I will seek a different approach.