In my DevCon 2017 session on Web Viewer Integrations, I very briefly showed an extension of the concept presented in the form of a pivot table. I had roughly 20 seconds to show it, and so I provided a quick overview of some of its features. In the waning seconds of the hour, I promised some follow up, and here it is.
Pivot table reporting is somewhat of the holy grail in FileMaker development. A quick search in the FileMaker Community discussion board revealed over 20 posts about this very topic. Unfortunately, this kind of reporting is difficult to do in native FileMaker. It requires some combination of portals or repeating fields, ExecuteSQL, and maybe some calc fields. It’s a tough report to build, which is why many of my clients ask for exports. Then they use the data within Excel to build a pivot table externally.
That’s too many steps just to see your data. We want you to stay in FileMaker, so we need to provide for them a great way to use a pivot table.
Today I’m sharing how to create a pivot table through a PivotTable.js integration and how to leverage its best features.
Figure 1. Pivot table created using a PivotTable.js integration
Let’s start by showing the capabilities of this integration. The above pivot table takes data from a sales table and summarizes it from many different perspectives. More than 17,500 records show a date, unit price, quantity sold, and total price of products. Once you load the data into the integration via a script, you can view the data viewed from many different angles and view it very quickly. There’s no reloading of the data from FileMaker. The library does all the work on its own.
Library features include the following:
- 13 different renders of the data, including the basic summary table you see above, heat maps, C3 charts, and even an export view that allows the user to (if they must) export the data to Excel using comma-separated values. See Figure 2 below.
- 22 ways to summarize the data: Sum, maximum, minimum, average, Sum as Fraction Rows, etc. See Figure 3 below.
- The ability to drag and drop an attribute to either the x or y axis. Multiple attributes can be used, as you see in the first picture.
- Filtering functionality for each attribute. For example, I can click on “Type” and remove one or more products from the summarization. See Figure 4 below.
- Sorting capabilities. You can sort both columns and rows in default order or by total descending or ascending.
Figure 2. Pivot table types
Figure 3. 22 ways to summarize the data
Figure 4. Filtering functionality for each attribute
Overall the setup is not complex in any way. I’ve worked out what is needed and have tweaked the function to make it fit a normal need in FileMaker. Here are your requirements:
1. A summary table from which to gather the data
Figure 5. Summary table
2. The HTML and CSS code
Figure 6. HTML and CSS code
3. The data gathered in a certain format and a script that gathers the data in this format
Figure 7. Data gathered in a certain format
4. The other required libraries
Figure 8. Required CSS and JS libraries
As with my Web Viewer Integrations library, you can easily import these fields into your own custom app.
You must gather and set the data for the table to properly summarize it. Follow these steps for a successful setup:
- Gather all data as individual arrays, with each array separated by a comma.
- Make the first row the header, i.e. the name of the attributes displayed in the chart and moved around.
- Of course, the order of the data gathered matters. Gather your data in the same order as the header. In the example above, the “Sales” header is first, and the sales data comes first in each array.
Figure 9. Sales data comes first in each array
- Using FileMaker 16’s new JSONSetElement() function, you can set a field to gather the data.
Figure 10. JSONSetElement() function
- Then use the ListOf summary field type to gather all the data during a script. Notice this script first sets the header row and then adds to that variable the LIstOf data.
Of course, you can gather the data gathered in a multitude of ways. It all depends on the circumstances you have for the given custom app.
As with other Web Viewer Integrations, you can edit the style of the pivot table, but everything else is ready for you to use. All you have to do is provide the data, and you have a complete, multi-function pivot table.
I’ve made some other tweaks to this to make it easier to use and more useful for you.
- I added another field to the pivot table to ensure you could easily add any field. In this demo, you’ll see Customers. You would need to collect the data again to use this. In this case, the data is collected with a new script called “Gather Data_Loop” and it does that, setting each array needed using JSONSetElement(). The script gathers data from the sales table and from the related table of Customers.
- I set up some defaults to the chart. My chart, in the function, is set to render the table showing the sum of Type in the column and Year and Month in the rows, as shown in Figure 11.
Figure 11. Chart parameters set in the function
How to Set This Up in Your Custom App
It is very simple to push this to your custom app. Just follow these four steps:
- Import these fields into your custom app into a new table. You’ll be bringing over my data, so you may want to erase that data once you have the fields imported.
- Copy the HTML_Calc calculation field from this demo to a new field in this table.
- Write a script to gather the data. See mine as a template. Set the data into the field.
- Accept or change the defaults that are in this library.
That’s it. You now have a fully-functioning, multi-use pivot table!
PivotTable.js in FileMaker Demo
Good luck! Please feel free to ask questions in the comment fields.