Reporting on Surcharges with Pivot Tables

How to create a report that shows the surcharges on shipments as columns which is a common reporting requirement.

A common report request is to have a breakdown of surcharges over a table of shipments. Surcharges are properties of a shipment price, separate to the base freight charge.

It may be useful, for example, to have a report identifying the most expensive surcharges over a period of time. However without knowing what those surcharges are in advance we cannot build a filter to extract them.

Reporting Breakdown Columns

In the reporting system, the base freight is a reporting column. In addition, the reporting system is able to turn surcharges into additional columns. Columns are provided (currently) for up to the first five surcharges in a breakdown (as more than five surcharges on a shipment are incredibly rare and computationally expensive).

Each surcharge is defined by an amount, a description and an internal categorization code. The code is a means to categorize charges when they represent the same fee but have varying descriptions. However the descriptions are often clearer as they are generally taken straight from the carrier billing data.

The Amount properties are provided as metric columns and the Description and Code properties are provided as dimension columns.

Creating the Report

Let's begin by creating a base report showing what we can initially extract. Choose a Shipment Report as we want the unit of data to be a Shipment not a Transaction or some other unit, which will throw off the aggregators by counting a shipment potentially more than once.

We start by adding all the Description or Code columns we require as Dimensions. These are the columns that will identify the surcharge. (These could be customer, cost or reseller depending on the purpose of the report.)

Next we add the Amount columns as Metrics. Remember to set the aggregator to Sum.

And of course make sure you filter the data as per your reporting requirements. A generally useful filter is to limit the range by reconciliation date (which is a non-moving date that implicitly filters shipments by those that are billed) and the customer name.

You can use the Variables feature to make the above filter interactive.

When you run this report (and produce an Excel sheet) it will look something like this:

Here you have one line per shipment. You will see that the first breakdown column is usually occupied while the fifth breakdown column is usually empty. This report is hard to work with if you want to inspect the surcharges.

Building the Pivot

To resolve this problem we build a Pivot table. You can read more about Pivot Tables here.

Find the report and click on Edit Features. In the Pivot section, create a pivot for each identifying dimension (in this case the Customer Breakdown Description columns) and match it to the corresponding metric column (in this case the Customer Breakdown Amount columns).

With these pivots saved, when you run the report the output will now look similar to this:

Now each distinct surcharge (based on the identifying column) has its own column in the report. This makes it much easier to order by amounts and sum the individual surcharges.