Do-it-yourself reporting with Excel

Posted on by

When you are responsible for setting up or extending an automized reporting of data stored in a warehouse with Excel, I would always vote for using PowerPivot for fetching the data and displaying it in pivot charts and tables. But there are use cases and situations where PowerPivot is not an option. Maybe you just cannot use PowerPivot for whatever reason. But first and foremost I met / suffered from the case where you already have an Excel reporting using cell references to other files and macros to fill the cells. If this sort of reporting reaches a certain extent and importance you cannot just set it up new again. Now if you need to extend such a report f.x. with a new column and don’t want to use cell references to other files – or worse VBA – because this is very fragile and error prone then I have a solution for you.

And this is how it works

structure

  1. A program (f.x. written in PHP using PHPExcel) creates an Excel file filling it with the data in long formatted table.
  2. This Excel file keeping the data can downloaded and saved locally in a specified folder.
  3. Now the actual Excel report file has a separate sheet with a pivot table that is using this data file as a remote source.
  4. Within the dashboard values are referenced from the pivot table.

Isn’t this unnecessarily complicated?

At first sight this sequence might seem unnecessarily complicated. But actually each step serves a specific purpose and/or solves a problem I experienced. In a perfect world I would just create immediately the report. Unfortunately pivot charts and tables f.x. cannot be handled reliably with PHPExcel so far. This is why I create the spreadsheet in step 1 which keeps just the data in long format. The reason why I do not save this data more simply in a CSV or TSV is because then the user of the report would have to refresh the intermediate pivot table by repeating the whole importing process again. The automatic refresh of a pivot table with a remote data source via click on ‘refresh data’ apparently only works with Excel files as the source.

Of course it is no option to skip step 4 and just reference the data in the data file directly by composing crazy formula applying ROW(), INDIRECT() and their siblings. This would be like walking through the Bronx with a golden Rolex and blazing abroad how much money you have. But referencing a value in a pivot table using GETPIVOTDATA() is very safe and straighforward.

The concept in action!

schema

GETPIVOTDATA() takes at lest four arguments. The first one is the name of the column keeping the value you want (“sum of sales”). The second one identifies the pivot table you are referencing by naming a cell within the pivot table. The rest are key value pairs to identify the exact value you are interested in (“sum of sales” for Germany “de” in June 2012).

Mind treacherous macros

In the end there is only one adjustment to the original report necessary which is to add a new sheet for the pivot table. If you make sure that this sheet is the last one then you are not taking a risk that some macro stops working because it references sheets with absolute indices.

If this article was helpful to you or you just enjoyed it – then don’t hesitate to leave a comment or share it using the links below this box (that would be uber-awesome)! If not – then you’re welcome to tell me what could be improved? Maybe you even have a suggestion for a new article?
– Thanks, Raffael