Hi, my name is Gabriel, and I'm a data junkie (everyone claps in imaginary support group). I like to measure processes by analyzing data logs, and check for trends. I do this as part of my job, but also do it with my personal data, such as my email inbox. The feature that I use the most in Microsoft Excel is Pivot Tables. So when I started using my iPad I could not believe there were no good apps to do pivot tables on the iPad, or even the iPhone. All office-like apps, don't include Pivot Tables support. That´s one of the reasons we decided to build ReportPlus, to allow data junkies like me to be able to do all sort of data analysis on the go, not just when they have access to a desktop computer. In this article we'll show how easy it is to build pivot tables in ReportPlus to summarize data. 

In case you are wondering, Pivot Tables are data summarization tools commonly found in spreadsheets or business intelligence software. Probably the most popular software application that provides pivot tables support is Microsoft Excel.

Let's look at the following example, for instance if you have an excel spreadsheet with sales data, listing invoices, something like the following:

You may want to know the total number of sales, grouped by Country. How can you do this? You could start adding up each sales cell of the same country manually, which would be very tedious and error prone, or you could create a pivot table and get something like:

Creating the pivot table on the iPad

If you need to do this in an iOS device, such as an iPad, or iPhone, your best option is to get ReportPlus, import the Excel spreadsheet to Local Files, and get started. To do this you'll need to start a new dashboard, which you do by tapping the + button on the initial view, and selecting one of the blank templates. Then you navigate the Local Files folder (or whatever data source you may have configured to retrieve the data from), and drop it in the dashboard canvas.

Once you do this, the widget editor is displayed automatically. This view lists on the left hand side the columns of the data set, and in the central bottom part the pivot table editor.

By tapping and holding fields in the columns list they become available for dragging. This allows for two things, one reordering them in the list, two using them to build a pivot table. By dragging and dropping columns in the pivot editor placeholders (Rows, Columns, Values and Filters) we create the pivot table. 

Dropping fields in the Rows placeholder will create a table with the first row containing all the distinct values contained in the dropped column. For instance in the Sales example, dropping the Country column in Rows generates a table listing USA, UK, and Japan in different rows. Dropping a field in the Column placeholder has a similar effect but the values are structured as different columns of the table.

Dropping fields in the Values placeholder applies a summarization function on all the values of dropped column, grouped by the values in the Rows or Columns placeholders. For instance, by dropping the Sales column in Values will add the number of Sales grouped by the different countries. There's a list of available summarization functions to calculate minimum, maximum, average, sum, etc. This can be selected by tapping the field in the pivot table, and changing the function selected in the Aggregation option.

If the field dropped in Values is not numeric, then the function that will be applied is the COUNT function that simply counts all values on that column in the original data table. 

Applying Filters and Sorting Pivot Tables

By tapping on a field dropped in values, the fields settings editor is displayed. This dialog allows to configure several things:

• Label. Allows to change the label generated for the aggregated value, which by default is the name of the aggregation function plus the name of the field. This can be renamed to anything.
• Aggregation. Changing the aggregation functions as mentioned in the previous section.
• Sorting. This option allows to sort values in the pivot table. For instance it can be used to sort values in a descendent order.
• Formatting. This option allows for example changing the way numeric values are display, to render them as currency, remove decimal figures, show 1000 separator, etc.

For instance it allows to sort sales by amount, and filter the values to display only the top 10 Countries. Again, something very useful, very easy to achieve with ReportPlus, not so much with other tools out there.

Pivot Table and DateTime fields

One of the neatest features of ReportPlus pivot table is that it handles Date columns "intelligently". Whenever you drop a Date column in Rows ReportPlus automatically groups the values by Day, Month, or Year, depending on the time period contained in the data table. This is simpler that other pivot table implementations that require the creation of a calculated field, to extract for instance the month value of out the Date value in order to aggregate by Month.

Aggregating Date columns also allows drill down navigation. This can be tried by tapping in the first row containing the dates. Drilling down from years, displays, months, and drilling down months displays the values for days.

Finally

Once the data has been summarized with the pivot table we are able to choose among the many data visualization options available in ReportPlus to chart it, so that it becomes as easy as possible to read & digest.