3 Replies Latest reply on Mar 12, 2013 3:16 PM by philmodjunk

    Creating summary chart dashboard report

    bcf06877

      Title

      Creating summary chart dashboard report

      Post

           Hi,

           I have a detail report containing several fields that I would like to summarize and chart. As an example my detail report contains "Customer", "Country", and "Product" in the report body. I would like to include a footer or trailing summary part that contains a row of three pie charts summarizing the count of each of these values (e.g. "Sales by Customer", "Sales by Country" and "Sales by Product"). I would like these charts to dynamically update based on the found set of records, and not be dependent on any particular sort order.

           I've spent considerable time researching this and I'm stuck. I can only get one chart to display correctly at a time, and only by sorting the field the chart is summarizing. But if I do that the other two charts go kablooey. I'm using FileMaker Pro 12. Can anyone offer some advice?

           Thanks,

           Brad

            

        • 1. Re: Creating summary chart dashboard report
          philmodjunk

               I would like these charts to dynamically update based on the found set of records, and not be dependent on any particular sort order.

               That, in a nutshell, is the problem. The sort order needed to group records for one chart is not the sort order that you need for the other two charts.

               Option 1:

               Put global container fields in your trailing grand summary. Have your script go to a different layout, sort the records, capture an image of the chart and then set one of the container fields to the image of that chart. GetlayoutObjectAttribute can be used for this purpose.

               Option 2:

               Use Calculation fields with ExecuteSQL to generate a delimitted data series for each "wedge" for each chart. (One Calc field for each chart.) The challenge here is that you have feed data to the SQL WHERE clause that produces a set of values that is in synch with your found set of records used for your report. If you use a script to collect the user specified criteria and that then performs the needed find to pull together the report, you may already have the needed data in global fields so that they can be included as value parameters in the ExecuteSQL function call.

          • 2. Re: Creating summary chart dashboard report
            bcf06877

                 You are a brilliant guy PhilModJunk. Option 1 seems like it would do the job. Option 2 seems like the more elegant solution but my SQL skills are very limited. Could you send me a code sample?

                 Thanks Phil. I think you are the same guy that helped me with my "diminishing list" problem the last time. Your solution worked perfectly! I thank you 2x.

                 Brad

            • 3. Re: Creating summary chart dashboard report
              philmodjunk

                   Take a look at the SQL query used in this thread: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                   The "summary recap" it produces could be the return separated list of values you'd use for a chart if you used the delimitted values option.

                   I find the free SQL Explorer tool from SeedCode helpful when working with the ExecuteSQL function.