Pivot tables or SUMIFS in reporting

Like many people I both love and hate pivot tables with a passion. They are so useful in analysing data on a one off basis, but when the requirement is to get a series of complex reports out every month with basically the same structure from one set of data, a number of pivot tables that each require updating for every change is both slow and if each one is not updated, can result in incorrect information.

I was recently working with an NGO in Cambodia. They had six projects in different parts of the country and each project had five different activities. These were set up in Xero as separate tracking items. There was a requirement to provide each project with the ability to analyse the spend for each activity, within their project month and year to date, actual against budget.

The data is easily downloaded from Xero. The reports can be produced in Xero but this would not give the managers the ability to get to the individual transactions and is very slow.

I saw two basic options. Firstly a series of pivot tables that would need updating each time data was changed. These would give the user the ability to look into the detailed transactions, but it would be difficult to make the pivot tables look good in a report.

The second option was to produce a single report using the SUMIFS on a standard template. These reports looked good and were easy to manipulate, but suffered from two problems. Firstly it was not easy for the user to interrogate the database to see the individual transaction and secondly if a new account code was entered and the template was not changed, the report would be incorrect.

I used the SUMIIFS to produce the reports and checked the results against a pivot table. The word ERROR appeared if the two did not agree and this could be checked prior to sending out the file to the individual users.

I also provided a separate tab with a pivot table that could be used to analyse the results.

This resulted in a easy to set up, good looking report where the project and activities could be changed with the report updating automatically, as well as providing the benefits of pivot tables to the user.