Forum OpenACS Development: Advanced OpenACS Reporting - Pivot Table HowTo


We have "accidentally" found an easy way to extract a "Pivot Table" from the database, or atleast something the comes pretty close to it. The result looks like and behaves effectively like a data-warehouse "Cube".

Example Output:

Source Code:

The default cube configuration shows you the evolution of customer invoices over time, grouped by customer type and customer. But playing around with the report "dimensions" you can check very different things as well.

About the code: The core idea of the algorithm is to represent report cells using a hash array. The key of each cell is composed by the dimension values. Entries from the "fact table" (i.e. Invoices) with the same keys will just sum up as a cell. The cell is written to HTML simply using a plain double foreach loop generating TRs and TDs. Subtotals are included automatically as cells with a lacking dimension.

The header of the source page says "all rights reserved", but I hereby release it also under GPL V2.0.

'Hope you like it, it saved us weeks of coding...

Posted by Frank Bergmann on
I forgot to say, to login to the rest of the application please go to main page and click on the "Login as Ben Bigboss" link.
Posted by Malte Sussdorff on
This looks interesting but I'm not entirely sure if it is not (at least partially) superseeded by the work dave has done on listbuilder, which let's you dynamically extend the displayed columns.

Obviously your example is considerably more complex and it would be nice to have a generalization out of it e.g by declaring "this is my date_filter variable" and then use that to group according to the years you are displaying.

But again, this is just an idea and probably you are better off taking this existing code and adopting it then to make a generalization out of it.

Posted by Frank Bergmann on
Hi Malte,

"this is my date_filter variable"

This is how it works. Did you glance the code? It's completely generic. You could even "drag" the time dimension to the left, or mix or whatever. The funny thing is: It's all contained in some 100 lines of code...

I don't believe that this structure could be superseeded by any list-oriented library, it's something completely different. For example, you can ONLY draw (summable) numeric values. You can't have text values nor can you have multiple columns like in a normal list.

In exchange, you get automagically all of these subtotals and the free floating dimensions.


Posted by Andrew Piskorski on
Is this for PostgreSQL or Oracle, or both?

Oracle SQL does support pivot table queries directly via its SQL-2003 OLAP (aka, windowing) functions. Last time I asked, PostgreSQL didn't have that yet.

Posted by Frank Bergmann on
Hi Andrew,

It seems my initial explanation wasn't very good. This "Report" is a Pivot _client_, a GUI for displaying the results of Pivot queries and for drill-down. It displays the result of a normal SQL query in a multidimensional way. So it works for both PostgreSQL and Oracle.

It's basicly a "Data-Warehouse in a Box" (in a single TCL page). All the steps of a DW creation (extracting dimensions, extracting the fact table measures, building a cube and providing the cube elements to a Pivot-client) are included in a single TCL page. It replaces an entire "Business Intelligence" suite, such as Business Objects or the Microsoft "Analysis Services" + Excel Pivot Table combination.

Well, most of it, there are obviously things it doesn't do. And performance will suffer as soon as you get into the gigabytes of data for a single fact-table. But that's not the case in an OpenACS-based environment. Also, our client isn't (yet?) as generic as a real Data-Warehouse system, so you have to code a bit more as a tradeoff. But that's not very much, and the TCL coding replaces the otherwise necessary configuration of the DW cubes.

The fun thing is that we've found a very short code (less then 300 lines of code) to express/implement this.

Happy Holidays!

Posted by Claudio Pasolini on
Hi Frank,

I have to beef up my own mini DW and before proceeding I'd like to look at your code, but the download link is broken.

I searched the more recent ]project-open[ code without finding it: could you kindly repair the link or send me a copy?