Paradise by the Dashboard Light – Bouncing around in FileMaker, building Charts


FileMaker Pro 11’s new Charting ability, is a great addition to its feature set. It can however, be a little confusing how best to grab the numbers to use in the label and data (X and Y axis) series of a graph. I set out to build a ‘management dashboard’ layout for one of my customers. The idea was to give the owner of the company a single layout overview of some crucial metrics of his company’s performance. I started to try to gather the data from different tables within the database, and then I was reminded of a useful technique demonstrated at the FileMaker DevCon 2010 in San Diego by Bob Bowers of Soliant Consulting.

Bob did a great job of explaining how this technique worked, but I had forgotten the details. I dug out his PowerPoint slides from the Conference and worked my way through his example. I thought it might be useful to ‘show my work’ as they used to say in school, to others struggling with this issue.

Getting Summary Data into a Chart – The Easy Way

wpid891-media_1287885438534.png

Have you ever tried to chart a set of summary data? Everything works wonderfully if your chart is on the same layout as the underlying data. You simply click the popup and select ‘Use Data from Current Found Set’ and then click the ‘Show data points from group of records when sorted’ option.

The Problem: But what if you want to gather data from different layouts and table occurrences. You need a technique to capture the data from multiple sorted layouts and bring them back to a specific charting layout. The ‘easy way’ technique above won’t work unless it is employed on the specific layout or table occurrence that the data exists on.

What follows is a description of Bob Bowers’ ‘tricky way’ to get around this problem.

Getting Summary Data into a Chart – The Tricky Way

#Generate Chart Info for SALES TRENDS

 

1) We start by opening a new window and going to the layout for the sub-summary report we wish to capture the data from.

New Window [ Name: "Sales Trends" ] #Clear out the two global variables 

2) Clear out the Global Variables that are ultimately going to be used to feed the data into the Chart Object.

Set Variable [ $$Data_31_Day_Sales_Report; Value:"" ] 
Set Variable [ $$Label_31_Day_Sales_Report; Value:"" ] 
# Go to the Sales Summary Layout and run a search for the 
# last 31 days of sales. Sort by date. 

3) Next we run a search, in this case a date range search, specifically the last thirty-one days of sales, then we sort the data, using the criteria that the sub summary report is based on. In this case, it was simply a Sort by the Invoice Date.

Go to Layout [ “Reports - Sales - Summary” (Receipts Master) ] 
 Set Error Capture [ On ] 
 Enter Find Mode [ ] 
 Set Field [ Receipts Master::Invoice Date; 
    Reports Master::Search Date-31 
    & "..."&Reports Master::Search Date ]
 Perform Find [ ] 

4) This is an important error capture step. If there are no records found, be sure to close the window, otherwise you may end up with unpredictable results.

If [ Get(LastError)>0 ]
 Close Window [ Name: "Sales Trends"; Current file ]
 Exit Script [ ] 
 End If

5) Sort the records. In this case our report is based on Date, so we are sorting on the Invoice Date. Your sort will vary depending what you want to show up in your Label data in your graph.

Sort Records [ Specified Sort Order: Receipts Master::Invoice Date; 
 ascending ] [ Restore; No dialog ]
 #Set the local variable counter field to '1'

6) Here comes the secret sauce – The Loop. To make this work, we need three things:

a) a summary count field defined
b) the field that the summary is based upon. – this will usually be your label or X-Axis when you get to the graph.
c) the total field that you want to capture for your data field (or Y-Axis) in the graph.

We start by naming a variable called $record_number and setting it to ‘1’.

Set Variable [ $record_number; Value:1 ] 

Finally, we are ready for our Loop that will move us through the records to grab the totals for each category.

# Loop through the summary records, collecting the date 
# for the labels and 
# the totals for the data row of the chart (as derived by
 GetSummary function)
 Loop 
 Go to Record/Request/Page [ $record_number ] [ No dialog ]

• Go to Record step calculated on the variable $record_number

 Set Variable [ $$Label_31_Day_Sales_Report; Value:
  $$Label_31_Day_Sales_Report
  & Receipts Master::Invoice Date&"¶" ]

• Set the Label data to a variable called $Date (or whatever your category field is). You set it to itself and the current value of the field and a carriage return.

 Set Variable [ $$Data_31_Day_Sales_Report; 
  Value:$$Data_31_Day_Sales_Report & 
  GetSummary(Receipts Master::Total of Receipts; 
  Receipts Master::Invoice Date)&"¶" ]

• Set the Data variable to $SalesTotal to your total field. The key here is to use FileMaker’s GetSummary function, in this instance based on the same field I have sorted on, the Date field.

Set Variable [ $record_number; Value:$record_number+ 
 GetSummary(Receipts Master::Total Number of Receipts; 
 Receipts Master::Invoice Date) ]

• Finally we increment the $record_number variable by the number of records in the next group of summarized records.

Exit Loop If [ $record_number>Get(FoundCount) ]

• There is an Exit Loop If statement if the $record_number exceeds the total number of records in the Found Set.

End Loop

7) We have accumulated in our loop through the sub-summary report the Data and Label content for our Graph axises so we can now close the Window.

Close Window [ Name: "Sales Trends"; Current file ]

FileMaker’s GetSummary Function

wpid886-media_1287527631892.png

Check FileMaker’s Help file for a reminder of how the GetSummary function works.

Tip: Displaying Global Results on the Layout

wpid889-media_1287545687557.png

The Sales data is captured in the $$Data_31_Day_Sales_Report global variable and the Label ends up in $$Label_Sales_Report global variable. In addition to seeing this information in the Data Viewer, it is possible to display this data on the layout by using merge field brackets to surround the global variable. <<$$Data_31_Day_Sales_Report>> will output the contents on the layout when viewed in Browse mode. This is another nice new feature of FileMaker 11. Now FileMaker Inc. would just add the ability to set global and local variables in a dialog box, the circle would be complete.

Setting up the Chart

wpid887-media_1287536694144.png

1) and 2) Feed the Global Variables we created in the Script into the X and Y axis fields.

3) Set the ‘Use Data From’ popup to ‘Current Record (delimited data)’.

You can then go into the Format Chart area to set up the specifics of how you want the chart to display.

The Chart Displayed

wpid888-media_1287545464122.png

Assuming all the steps have been performed correctly, you should see your data nicely displayed as a bar chart.

Part of the beauty of this technique, is that you don’t really need an underlying sub-summary report for this technique to work. I tried it on a simple list, and the results were the same as if I had built a complex layout complete with sub-summary parts etc. It would work even with a data view, no list layout required. The Count Records field, and a Sort based on your Label information are the crucial elements.

Management Dashboard

wpid890-media_1287862724435.png

The end result is that information is pulled from a number of different data tables to generate a graphic report. In this example we drew from Customers, Sales, Line Items, Notes and Activities databases, and displayed them all on a single layout based on the Reports table.

As you can see this technique frees you from generating reports on a layout tied to the data. You can now move the graphs to a dedicated graphing layout, perhaps tied to your reports table. If you set up your scripts using this technique, you can bounce all around your database gathering information from different layouts and table occurrences and display them all nicely on a single management dashboard layout. Your only constraint now, is how long each report takes to generate.

Related Articles:
Interactive Charts driven by Virtual Javascript files
Creating and Storing 2D Bar Codes in FileMaker
‘FileMaker Go for the iPhone’ – a First Look

Advertisements

9 Responses to “Paradise by the Dashboard Light – Bouncing around in FileMaker, building Charts”

  1. Very very useful tip thanks!

    Just started working with charts for a client project and this will come in very handy!

  2. I am seeing one problem working with this and was hoping somebody could help me trouble-shoot. All looks well except nothing is showing in my chart. When I say nothing, I mean nothing at all.

    The global variable fields are showing all of my data properly, I have double checked my chart settings.

    Thanks

  3. Is that possible for you to send an example of your dashboard file ?

  4. Is it possible for you to send me an example of your dashboard file ? I have difficulty to reproduce it, multiple chart on the same layout…

    Thanks in advance,

    Benoit

  5. In this following step of your script, can you tell more about the definition of the feild “Total Number of Receipts”. I not to sure what is the definition of that feild and how have you create that ?

    Thanks for your help, I’m almost there !

    Set Variable [ $record_number; Value:$record_number+ GetSummary(Receipts Master::Total Number of Receipts; Receipts Master::Invoice Date) ]
    • Finally we increment the $record_number variable by the number of records in the next group of summarized records.

    • That field is a Summary field that is a Count of the ID_Receipt field. Basically I want a count of the number of records. It could also be Get(FoundCount)

Trackbacks/Pingbacks

  1. Listening for Keystrokes | HomeBase Software - July 14, 2012

    […] Articles: Highlighting Rows in FileMaker lists or portals Paradise by the Dashboard Light – Bouncing around in FileMaker, building Charts […]

%d bloggers like this: