Results #4: Pivot Tables

Click to Print

Conceptual

A pivot table is a data summarisation tool that can automatically sort, provide totals or give the average of the data stored. For a more detailed explanation of pivot tables in general visit http://en.wikipedia.org/wiki/Pivot_table

Pivot Tables in Spry are essentially no different to a regular Pivot table, but it’s still useful to discuss the basic concepts regarding Spry specifically.

  • Summation Values: Called the Data Area in Spry. Can use any numeric Variable including Value Type Custom Fields including Volumes, Distances, Times, Rates etc.
  • Row and Column Labels: Called the Row and Column Area in Spry. Can use any text or date Variable Filter Type Custom Fields including Leaf Positions, Process, Equipment, Reporting Periods etc.

You can also use multiple types of filters to show or remove anything in each individual Pivot Table (options shown below in the Practical section).

Empty Row and Column Labels

One important feature to be aware of in Pivot Tables is that there are times when your row and column labels will be empty. Let’s say you created a monthly Reporting Period, but only covered the first year and used it as your Row Label. Any summation value that falls outside the Reporting Period appears as a blank like the screenshot below.

Blank

In the example above it’s a consequence of a Reporting Period setting (which can be resolved by recreating or amending the Reporting Period to include more periods) but an example where this feature can be particularly useful is during destination scheduling where you can use it to check your dump balance.

Finally, if you haven’t used Pivot Tables before it’s quite possible to create nonsensical reports with too many columns and/or rows. Don’t worry! With a little patience you’ll get the hang of it.

Practical

Pivot Tables are drag-and-drop. Drag your Row and Column Labels and Summation Values straight into the position you want and your Pivot Table will be populated immediately. Custom Fields will appear by default, as well as Reporting Periods which appear as “Period YourPeriodName”

By default, you don’t see all of the Labels and Values that you have available. To see the full list, right click the header that has the various drag-and-drop items and choose Show Field List.

results4pivot2

Export your Pivot Table as either XLS(X), PDF or CSV using the contextual Import/Export menu at the top of the Spry window.

results4pivot3

Pivot Table Charts

To view your Pivot Table with a Chart, click the small arrow at the bottom of your Chart to bring up the interface. From here click one or more of the Rows to populate it as illustrated below.
Charts
To use Column Data instead of Row Data, or for other options right-click the Pivot Table Chart.
Chart Options