Update: Download for the Dynamic Charting demo has been added at the end of the post.
FileMaker 13’s charting functionality is a powerful toolset in which to create visual summaries of the data. It has come a long way in the past two versions, and by applying well-known techniques within FileMaker, one can create a better graphing experience. Today’s post will explore making charting a dynamic experience.
Charts are great visual tools, but they can quickly get messy and difficult to read. A chart that compares sales of different products throughout the year would get very unreadable if all the products were listed for all the months of the year.
Instead, we can dynamically add or subtract data series from the graph to make a better comparison. For example, I could compare apple sales to orange sales, or apple sales to banana sales, or even lime sales to strawberry sales. Or I could compare four of the products to each other. Using ExecuteSQL and a global field, a graph can be dynamic and interactive.
To start, I have a summary table which summarizes the total number of each kind of product sold in each month. I’ve also got a month name and a month number field. All of this is filled in using a script, and that script records something for all the products for every month, regardless of the total. If I sold zero limes in May, the total sales that is recorded is zero. This allows every product to have the exact same number of data points when it comes time to graph.
Using a summary table is a piece of optimization for this dynamic graphing example. The graph has up to 12 ExecuteSQL statements in it, and all of those could be running at same time. If each function has to do find the total number of sales on the fly, there could be a performance hit. Instead, the ExecuteSQL functions are returning lists rather than lists of on-the-fly sum calcuations.
On the layout, I have a global field named g_Products next to the graph. This is a checkbox-formatted field with the list of products in the data to be graphed. The idea is that when a user clicks on a product, that product’s data will be displayed on the graph.
The chart that I will use in this case is a line graph, but any chart could be used in the same manner. It simply depends on the type of data upon which you need to report. Remember when you’re building a chart using ExecuteSQL, you would need to select the “Current Record (delimited data) option as the data source. This prepares the graph to receive the result from the ExecuteSQL statement.
The x-axis will be a simple ExecuteSQL Statement that shows the distinct months in the summary table, and puts them in order of month number:
ExecuteSQL(" SELECT DISTINCT "Month" FROM Charting ORDER BY MonthNumber";"";"")
I am choosing to pull the month names from the summary table so that only the months that have data will be generated. If it is March, I may not want to see the month “Apr” on the graph with no data.
Defining the data series is next. Each data series contains a label and the data. By definition, a data series is one kind of data. It can be the number of bananas, or the heights of players, or the average grades. A data series is simply a list of the data that I want to show.
Since I have seven products, I’m going to need seven data series. Let’s build those. First, the label.
The data-series label comes from a simple GetValue() statement:
Let ([_Row = 1 ]; GetValue (Charting::g_Products ; _Row ) )
Notice that I am not using a lot of whitespace in this calculation to make it look good. Instead, I have the variable declaration on the first row of the Let statement. This allows me to edit the _Row value without having to open up the calculation dialog box for the label.
For the Data in the series, I first define the row, and then declare another variable with the name of the product in that row of the global field. Finally I use that product name in the ExecuteSQL statement:
Let ([_Row = 1; _Item = GetValue (Charting::g_Products ; _Row )]; If ( NOT IsEmpty (_Item); ExecuteSQL(" SELECT TotalSales FROM Charting WHERE Product = ? ORDER BY MonthNumber";"";"";_Item )) )
In this statement, I’m asking for the TotalSales data for any records that have the Product name that matches the value in the _Item variable. Notice the IF statement surrounding the ExecuteSQL statement; I do not want this function to run if the _Item variable is empty.
The Series Name and Data statements are then copied to all the other data series that I will need, while changing the value of the _Row variable in each copy. That is it. The ExecuteSQL statement in each data series will find the data from the summary table that matches the product in the correct row of the global field. The fifth data series will show the list of total sales for the fifth item checked in the global field. If there is no fifth item in the list, the nothing will return in the ExecuteSQL statement, and nothing will be drawn on the graph.
See the Post-Script below for a quick discussion of the data-series colors used.
A Further Exploration
Once we’ve gotten used to this, global fields open up many other new ways to dynamically show the data. Here is an example using a start and end month range:
This was accomplished by asking the ExecuteSQL statement to limit its search for months that fall with in the range. The data table contains month names as well as month numbers. This ExecuteSQL statement is limiting its pull to records that fall within the range, in this case month 1 to month 4. In this case the x-axis label is using the same ExecuteSQL statement to grab the month names.
Finally, one can program buttons to draw or erase data on the graph. In this example, the button on the left will let the user hide or show the average sales for each month in the given range. This is accomplished simply through setting a global field to a 1 or 0. The ExecuteSQL statement will only run if the global field is set to 1.
Here is the statement:
If (Charting::g_ShowAverage = 1; ExecuteSQL(" SELECT AverageSale FROM AverageSales WHERE MonthNumber >=? AND MonthNumber <= ?";"";"";Charting::g_MonthStart; Charting::g_MonthEnd );"")
Combining global fields, ExecuteSQL and the charting functionality gives developers a powerful tool to create context-free dynamic charts that can be used to read all kinds of data. Rather than building a separate chart for each possible comparison, one can use one chart to deliver all the data needed in a strongly interactive scenario.
A field that is formatted in a checkbox will have its contents generated in the order in which the boxes are checked. If I check “Apples”, “Oranges”, and “Limes”, the contents of the field will be a return-delimited list in that order. If I unclick “Oranges” and then click it again, the order will change: Apples, Limes, Oranges (in a return-delimited list).
THUS, the first item in a list will always have the first color of the chart’s color-scheme. In the first list, Apples has the orange line. If I unclick Apples, then the color shifts for each series and “Oranges” gets the orange line.
There are ways to prevent this. This can be explored in another post. But the color shift is something to consider. If it is not confusing to the users, don’t worry about it.