Monthly Archives: September 2012

A quick introduction to crosstabs in Pentaho Reporting 4.0

Crosstabs have been on the horizon for several years now. They lived a happy, undisturbed life along with the unicorns and gnomes guarding the pot of gold at the end of the rainbow.

With an endless recession and central banks selling off their gold reserves, the unicorn has been sold to a meat factory and the gnomes now assemble luxury cell-phones in a chinese factory.

So the day had to come that crosstabs have to work for a living. That day is now.

The added crosstab capabilities are the head feature of Pentaho Reporting 4.0 (now to be released in April 2013). To make crosstabs possible, the reporting engine had to move away from the strict banded approach of top-down layouting. The engine now has a true table layout that ensures that even misbehaving crosstab definitions will look and behave good.

Some sort of crosstab abilities were in experimental mode for very much of the last 4 years. The good news: The current implementation fixes all the problems we had with that experimental stuff. The bad news: Your old crosstab definitions will most-likely not work in 4.0 – you will have to recreate them properly with the new version.

To spin off crosstabs quickly, the Pentaho Report Designer ships with a quick-and-dirty crosstab creation dialog. This dialog pops up whenever you add a crosstab group.

Until we update the report designer with a proper user interface, you will have to edit all crosstab properties and elements in the structure tree.

Be aware: The code is pretty much happy-path – stray too far away from it and the big bad wolf will eat you, your children and your data. Wolves are a protected species, so bear your share and help to feed them properly. Before you are fully digested – file bug reports and feature requests in our JIRA system.

Enough prose: Let me just list some of the notable current behaviours and general ramblings.

  • normalization works now, but it is not all-knowing. It will only normalize on existing data items. If you whole dataset has Q1, Q2 and Q4 but fails to mention Q3 at least once, you will not see a Q3 anywhere.
  • The engine wants its data raw and bloody. Denormalize your data! If you use OLAP datasources, use Denormalized version of these datasources.
  • Sort your data to match your group structure.
  • column-headers currently do not repeat on the subsequent pages.
  • Setting your cell-contents (or any other row-layout child) to a percentage width will yield a zero-width in the final layout. (PRD-3970)
  • the report wizard really does not know how to handle anything crosstab related. (PRD-3860)
  • you can add charts into a crosstab cell.
  • if there is more than one row of data for a single crosstab cell, you have the choice to print either the first item, the last item or all items in a large list.

Features that will come, but are not implemented yet:

  • butterfly headers: Move row-headers into the middle of the data. (PRD-4005)
  • Subreports on all crosstab cells (PRD-4006)
  • style- and attribute-expressions for all crosstab elements (PRD-4007)
  • The no-data-band on the crosstab-group has no effect. (PRD-4008)
  • Summary-rows and columns will be positioned either at the start or the end of the respective group. (PRD-4009)
  • Details header will be printed. (API is defined, but not used yet). Printing of them will be optional. (PRD-3949)
  • Header cells can either be spanned cells, or can repeat its content for each child. (PRD-3386)
  • There will be a switch to make row- and column-headers disappear. You can then use the crosstab purely as a layouting utility. (PRD-4010)
  • sorting of data. This will be optional and will blow up if you expect us to do big-data sorting. Get a real database with SQL support 😉

Now go and download a CI build of PRD-trunk, will you!?