How to create Excel 2007 Pivot Reports

This memo shows how to use Excel 2007 to analyse data from FBO One. As an example, we'll create the following overview:

  1. revenue per aircraft type
  2. revenue per debtor & form of payment
  3. number of orders per debtor

The data used in this example will be a set of totally random orders. All the pivot tables are based on the 'orders' report in FBO One, but of course data generate by the services or movements reports can be analysed in a similar way.

 

Start by downloading all orders in the period of interest (say May 1 to Aug 1) from the Report section in FBO One:

 

The result will be a table in Excel. Now a very important step: Excel won't allow to create pivot tables on 'temporary' files, so we'll have to save the report first, by choosing save (not open!) in the dialog.

If you didn't save the file and then opened it in Excel, you'll have to save the file first, by using the Save As command of Excel. Otherwise, creating a pivot table will not work!

 

Next step after saving the file is to create the actual pivot table. In Excel 2007 this function is in the insert menu.

 

The wizzard wil automatically find the entire table (indicated by the blinking dashed line) so all we have to do is click OK.

 

The result is a pivot table on a new worksheet. The screen should look somewhat like this:

 

Now the pivot table magic starts. For the first report, revenue per aircraft type, find the AircraftTypeShortname field in the 'Choose fields to add to reports' list at the right side of the screen, and drag it to the 'Drop Row Fields Here' column. Then, find the 'NET Amount' field, and drag it to the big 'Drop Data Items Here' area. Thats all there is to it. The pivot table now shows 'Sum of NET amount' in the top left corner:

 

For the second example, revenue per debtor & form of payment, find the 'FOP' field in the list and drag it to the cell B3 right above the Total column. Then, drag the AircraftTypeShortName field from the pivot table to the field list. The mouse cursor will show a red cross to indicate you are removing a field from the pivot table. Finally, get the DebtorShortName field and drag it to the now blank cell A4 below the 'Sum of NET amount':

Interesting in the table above is the FOP is blank for quite a few orders totalling EUR 6147,-. This is caused by the fact not all orders are closed yet, so we see a mix of invoiced, working and requested orders here.

 

See also

How to use Excel 2007 Autofilter

 

Now, by clicking on the little arrow next to the DebtorShortName in A4 and selecting 'Value Filers' -> 'Top 10' and then 'More sort options...' -> 'Descending (Z to A) by: Sum of NET amount' you can get a list of the top 10 debtors:

 

For the final report, the number of orders per debtor, we have to remove the FOP field again, by dragging it rom the pivot table to the field list. Then we remove the 'NET amount' field too by dragging 'Sum of NET amount' from cell A3 back to the field list. This removes the sort order and top 10 filter autmatically.Then, to get an ordercount, drag the 'HandlingOfficeShortName' field to the 'Drop Data Items Here' area. Trick is, the HandlingOfficeShortname is not an number, so it can not be summed. Excel will do the best it can and just count it. So the resulting pivot shows the number of orders for each debtor.

If we drag the 'OrderState' field to the 'Drop Page Fields Here' area in cell A1 we can easily filter just the 'registered' orders, so we now see what we can invoice:

 

Using pivot tables in templates

Pivot tables store the data that is used. This can cause that the file size of the template will be very large. To reduce the file size right click on the pivot table and select "Pivot table options" and disable the option "Save source data with file".

 

See also

Create a template

Contact fields

Order fields