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
• Includes folder containing the required Javascript and CSS files

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.

You should know that our Demo Files load several Javascript and CSS include files from our website to make portability easier. We highly suggest that you use host these files on your own website which found in the 'Includes' folder. Just upload the folder to your website and change the url for each Javascript and CSS file to your own website links. The links can be found in the Xojo App PivotTable Class HTMLTemplate Constant and in the FileMaker Database PivotTableHTML Custom Function.

The PivotTableData is fomatted as a Javascript Array as shown below. Just format your data to match so that numbers are not quoted and strings are quoted. If you have data with double quotes you should escape them by adding a backslash before all double quotes. Escaping the double quotes turns them in into a simple string character rather than a defining the beginning or end of a string. Besides setting the PivotTable Title, all you need to do is set the Default Values, Default Columns, and Default Rows which defines how the Pivot table will appear on load.

["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"]

Questions or Comments

Post on this Xojo Forum Thread.