1 of 1 people found this helpful
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.
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