6 Replies Latest reply on Feb 23, 2013 9:20 AM by ahcho

    Charts and ExecuteSQL



      Charts and ExecuteSQL



           I've been trying to use the Charting functions in FMPRO 12 but am running into a few hiccups. It seems a little backwards to me how the charting functions work when compared to regular tabular reporting. I've attached a screenshot below of the TO's that I am working with.

           Ultimately, I want to create a bar chart with the X-axis being the names of the different categories (from the table Search_Category) and the counts of each category within Search_OFATR.

           Now I can pull in the Category names from the TO "Search_Category" but then I don't have a way to calculate the sums that correspond to each category (it will give me the counts for all values within the table). Now I have a feeling that I will need to use a count-unique self-join relationship.

           So I thought that I could use the ExecuteSQL function to do this but since I'm querying the Search_OFATR, I will recieve the values from that table and (from my understanding) I cannot pull the related category names from the TO "Search_Category". If I do this then my X-axis labels become cryptic foreign keys.

           Now in all the FM tech briefs and examples, the charts come from rather simple tables so my next idea was to create another table that would summarize the chart data that I want to use but I have no idea how to do this.

           How would I create another table that would automatically update from another table? If this isn't the best way to do this what would be? 



        • 1. Re: Charts and ExecuteSQL

               You should be able to chart your values directly from a layout based on either Search_Category or Search_OFATR.

               If you base it on Search Category, you can use Count ( Search_OFATR::_kf_CategoryID ) as the calculation for your Y-Axis.

               If you base it on Search_OFATR, the Y-axis can be specified as a "count" summary field and you can specify "found set, groups of values when sorted" in your data source settings. The records must then be sorted by Search_OFATR::_kf_CategoryID to get one bar for each category.

               Both options work for what you describe, but the second option allows you to perform finds to further control exactly what records get counted in each category.

          • 2. Re: Charts and ExecuteSQL

                 I've gotten the first one to workout (Layout based on Search_Category).

                 The screenshot shows my attempt at the second. I cannot get the graph to sum up similar records within Search_OFATR.

                 Would I be able to create this graph with a layout based on Search_Overwatch?



            • 3. Re: Charts and ExecuteSQL

                   Your y-axis data series must be changed to refer to a summary field defined in Search_OFATR.

              • 4. Re: Charts and ExecuteSQL

                     I must be missing something. The field is a summary field based on "count" but the graph doesn't change.

                • 5. Re: Charts and ExecuteSQL

                       Are the records in your found set sorted or unsorted? They should be sorted by category.

                  • 6. Re: Charts and ExecuteSQL

                         That was it. Thanks PhilModJunk.