Charting Anywhere
One of the more valuable things you can add to your data is some sort of visualization. At its most basic level, this is primarily a chart or graph which speaks about the state of your data.
Visualizations have been a hot topic in recent years because it's become so easy to access larger data sets. Spending even just a small amount of time on http://visualizing.org or http://www.informationisbeautiful.net makes your everyday UI designer salivate at all the pretty pictures which can provide insight into solution data.
With powerful JavaScript libraries like D3, http://d3js.org, readily available within a web viewer and even FileMaker's own charting tool, it simply doesn't make sense to not understand how you can easily access the data for showing a useful chart.
In this comprehensive video, we take a look at some tricks to making charting quite easy within FileMaker. It requires an understanding of ExecuteSQL, which is fully covered, but once you have the basics you start to wonder why you've not added more visualizations to your own FileMaker solutions!
Comments
Tweaking the Example
This is fantastic! I have been playing with this in my solution and it works beautifully. Now I'd like to tweak it and am a bit stuck.
Instead of a pie chart I've changed it to a bar chart. Now, how about showing only groups with more than three members, for example? I imagine I need a field that counts the number of members in a group. Then I add criteria to ExecuteSQL statement, such as WHERE g.~totalGroupActive > 3. But how do I get the field that counts the total of active people in a group?
Referencing summary fields won't work.
The problem with referencing summary fields (which I'm assuming totalGroupActive is) doesn't work within ExecuteSQL - unless they are stored summary values. Summary fields, will only work within the context of what FileMaker understands about the graph.
When using ExecuteSQL you are making an explicit query. You'll need this logic to be within your SQL.
Such that it would read like this.
WHERE COUNT( g.~groupActive) > 3
Hope that helps out.
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Re: Referencing summary fields won't work.
Yes - that helps tremendously. Essentially, I should try to make the SQL do everything if possible. I actually figured it out by doing this:
Let ( [ ~sql = "
SELECT g.~groupname, COUNT(m.~mid) AS theCount
FROM ~members m
JOIN ~groups g
ON m.~id_Groups = g.~gid
JOIN ~people p
ON m.~id_People = p.~pid
GROUP BY g.~groupname
HAVING COUNT(m.~mid) >= ~filterCountMin AND COUNT(m.~mid) <= ~filterCountMax
ORDER BY theCount
";
So, I used COUNT after the select statement and HAVING COUNT to order to create a filter so I could specify a max and min number of members. I'll have to dig deeper into SQL, but this example is a wonderful technique can get a lot of use.
Good topic
I have been looking for Filemaker charting tutorials. It isn't well documented area. I have been dodging SQL , but here we go ...
Do you always use sql to gather your data for a chart Matt P? I assume you would not only because of being able to use it anywhere, but also because sql gets the summary data (group by , sum etc) as Matt B comments refer to above. isthmus that otherwise yo need to create scripts and more tables to get summary data into charts !?
Great question!
Where the data comes from for a chart is really dependent upon all the places you wish to show it.
In this video, I'm primarily addressing a situation where you want to show visualized data in an area of your solution which is not "wired up" in the Relationship graph given the current context (what the viewed layout is connected to).
In other words, if the only place I need to show visualized information about how much a customer has spent is on the Invoices layout for a customer - and I have access to that related data via a relationship - then I'll just access the data right there from the record using the option of Related Records. Likewise if I'm on a layout where the Current Found Set makes sense, I'll use that.
If I suspect, AT ALL, that I may want to show a chart in an unrelated area - meaning the chart would not have the context to derive the data - or - it would require additional connections on the Relationship graph which would only be there for the purpose of the chart, then using ExecuteSQL is a better way to go.
The tradeoff off of using ExecuteSQL is the learning curve it will require to become comfortable with it. Using the Chart options of pulling data right from the context of the current layout is easy, but not nearly as flexible in terms of where you can use that same chart solution wide.
I hope this makes sense.
-- Matt Petrowsky - ISO FileMaker Magazine Editor
I need help for Charting
I am new user in file Maker as well also new to this website. I need help for charting anywhere.
Tried to check in DataViewer its showing correct result as per video but in the graph it showing combine all the labels.
e.g. I am doing summary on Type of computers i.e. Desktop, Laptop and Mac
Below is real data
Desktop, 16
Laptop, 31
Mac, 16
I had selected pic chart and in the labels its showing Desktop Laptop Mac instead separate Desktop, laptop and Mac for each part. Its same thing for the value showing in one line 163116, as the three time equal value its showing percentage 33% for each part.
Please help me what I am doing wrong ?
Thanks