Building a report-builder (or my career as an inbred redneck)

New day, new requirement. Welcome to my life.

What my boss would like is a sort of halfway house between a set of pre-written, inflexible standard reports and a completely flexible (and thus complicated) report-building tool, such as Crystal Reports or Hummingbird BI. An additional requirement is that the solution I come up with should cost my company nothing but my time - and so going out and buying some third-party product is out of the question.

Time to get my MacGyver hat on.

My intention is to kinda reproduce (and improve upon) a form I previously created in Oracle Forms, in which I applied a thin layer of interpretation between the end user and the database, and then presented the user with a list of report columns, a list of report filters, and a number of report templates. The user can then select any combination of columns to appear in their report, and any combination of parameters to filter it by. And my job is to take this permutation (whatever it might be) and build the sql code needed to produce a report from it.

The way I achieved this in the earlier incarnation of my Oracle Forms flexible report builder was to back each report column and filter with a huge block of sql code and, depending on the columns and filters selected by the user, I outer-joined these blocks of code together to produce the report's sql code.

This system worked (and works) reasonably well, but has the disadvantage of producing humongously long sql scripts to create even the simplest of reports. Goodbye code efficiency and often, goodbye runtime speed.

And so I'm wondering if I can do things a little differently this time; perhaps by dynamically determining the relationship between tables on the basis of their primary and foreign key constraints. Sounds horribly complicated though (imagine having to write code that will automatically determine the connection between any set of 2 or 3 or 20 tables in a database of 1000 tables!) and I'm not as clever as I used to be - I've got too many pints of Foster's lager and too many hours watching American Idol with my girlfriend to thank for that!

And so I'm wondering if anyone else has had to create their own reasonably-flexible report builder who can give me any pointers? (All the developers in my office have been working there for far too many years and so our knowledge is now a little inbred - kinda like the Royal family or some redneck hicks from Kentucky - and it's sometimes difficult to generate new ideas in-house.) How do you generate the sql that you need? How do you ensure that your tables are correctly joined?

I know that none of the foregoing is exactly Apex-related (although I will be implementing my solution in Apex and probably be blogging about it here) but I do think the pl/sql involved might be quite interesting, so please do pitch in with your two cents' worth if you've got any ideas or suggestions.