Forum OpenACS Development: Re: Advanced OpenACS Reporting - Pivot Table HowTo
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.
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.