2 Replies Latest reply on Jan 21, 2014 1:31 AM by prtd

    Filter option for chart function?

    prtd

      I would like to present data in chart form but according to a filter. Whilst it's been easy to present the data from the found set as a graph, I need to filter this according to a global variable. The filter option is offered for displaying data in a portal. Does anyone know of the equivalent option that could be used for the chart function? I am using FM Pro 13 Advanced. Suggestions as to how to solve this would be welcome. Peter

        • 1. Re: Filter option for chart function?
          jbrown

          Good morning. I've set up, in my solutions, a global variable that allows the user to filter what information is shown on the graph. To do that, I use an ExecuteSQL statement in graph's series calculation boxes. The chart is set to show, in the data-source menu in the chart setup, "Current Found Set (delimited data)". The ExecuteSQL statement will return a delimited list of results which is then put on the chart.

           

          The ExecuteSQL statement is a pretty standard one: ExecuteSQL("Select yourField from yourTable WHERE FilterField = ?";"";"";$$MyVar).  Again, based on what is in the global variable, the graph will show that data.

           

          EDIT: For example: I need to show the number of detentions a student has had over the weeks of the semester. I would ask the graph to show me the dates of the semester 1 and the value in the field called Detentions for a student". The executeSQL statement is in both the x-axis calc box and the y-axis. For the x-axis I'm going to ask the SQL statement to select the dates. In the y-axis I want the Detention count. That will ensure that the data matches up. Using the same SQL calc with different SELECT fields will keep things matched up.

           

          This works very well for me. I'm able to get some good dynamic reporting to happen.

          1 of 1 people found this helpful
          • 2. Re: Filter option for chart function?
            prtd

            Dear Jeremy - Many thanks for reponding so quickly to my query. I will try, as you suggest. Thank you too fo the example - very helpful. Kind regards, Peter