Pivot table

The StatisticsCalculator transformer has the ability to generate statistics for groups of features rather than all features together. This effectively allows us to create pivot tables in FME similar to the pivot tables in Excel.

Source Table and Excel Pivot Table

In Excel, I generated some fictitious source data and exported it to a CSV file (see attached) for use in Workbench. I also generated a simple pivot table in Excel to show what we want to produce from FME; basically we want to summarize observed values based on region and potential.

Above: source table and example pivot table in Excel.

FME Pivot Table

The workspace (see attached) shown below uses the updated StatisticsCalculator transformer to create statistics for the observed attribute by first grouping features by region and potential. Then the new statistics features are sorted by region and potential and output to a CSV file. The resulting CSV file has all of the same attributes/fields as the Excel pivot table.

Above: the StatisticsCalculator transformer generates a pivot table.


Above: the table written by FME (viewed in Excel) resembles the Excel pivot table.

FME Chart from Pivot Table

We can take the pivot table a bit further and generate a fancy chart using the WebCharter transformer. This provides us with the basis for some neat summarization and reporting tools.

Above: The WebCharter transformer creates a chart of the pivot table.


Above: The chart created from the FME generated pivot table (Note: data is fictitious).