Pivot Tables in Xojo and FileMaker
Feb 07, 2019 10:41 AM
Pivot Tables will change how you view your data. Recently, I was working with someone who opened an Excel document and created a Pivot Table with the data. I was immediately in shock how fast and easy it was to visualize the data from different perspectives.
As you can see in the video below, you can easily visualize your data just by dragging the columns to change how the data is summarized.
This data is from our annual Boy Scout Mulch Sale which occurs for a few weeks in January and February, but I duplicated the data several times and randomized the dates so the sales would appear throughout the entire year. The video and the example files have sample data with 10,000 records.
When you add and remove columns and rows to the Pivot Table you can see it summarize the data with blinding speed. I've only seen a delay when adding unique data like an Invoice Date or Invoice Number which is unique unlike a Year, Month, or Product Name. That makes sense because the entire point of Pivot Tables is to show your raw data in different summarized views.
The Demo Files are designed to be easy to move into an existing Xojo App or FileMaker Database:
- Xojo Web App Demo
- Xojo Desktop App Demo
- FileMaker Database Demo
Download the Demo Files
To get the Pivot Table into your Xojo App, first copy the PivotTable Class to your App. Then as shown on the PivotTablePage for Web Apps and PivotTableWindow for Desktop Apps, use the code on the PivotTableButton to apply your data to an HTMLViewer.
To get the Pivot Table into your FileMaker Database, first copy the PivotTableHTML Custom Function to your database. Then as shown on the FileMaker Layout, use the code in the WebViewer calculation to apply your data to a WebViewer.
The magic all happens within PivotTable.js. I'd also like to thank Jeremy Brown for his Blog Post where I first learned about PivotTable.js. Jeremy's download breaks down all the parts into separate fields and suggests a nice method to format the data.
["Year","Month","Product","Quantity","Price","Total","Bag Count","Spread Service","Referral"],
[2014,"01 - January","Pine Mulch",1,47,47,9,"No","Friend"],
[2014,"01 - January","Pine Mulch",3,47,141,27,"No","Website"],
[2014,"01 - January","Pine Mulch",7,47,329,63,"Yes","Facebook"],
[2014,"01 - January","Cypress Mulch",7,47,329,63,"No","Facebook"],
[2014,"01 - January","Pine Mulch",4,47,188,36,"Yes","Website"],
[2014,"01 - January","Pine Mulch",2,47,94,18,"No","Facebook"]