Generic Reporting Tool

Short version:

It would be rad if Drupal had some basic business intelligence tools which could be used to easily build reporting for web applications written in Drupal. Some basic reporting exists for overall site metrics, but not to easily have custom reporting (unless I've missed it in my searching around - one of the motivations for this post). Looking for feedback on the concept.

Long version:

Reporting tools such as JasperReports and BERT (and OpenReports, which is a facade for both of these) as well as Pentaho, provide a platform for building custom reporting tools. Often with large sites one finds oneself asking questions like "just how many webforms of people who picked X as the 'I am interested in' field did we have last week", or "it would be great to see a daily graph of..." - these tools make it easy to provide reporting that answers these questions and the ability to allow other users in such a reporting system access to them, proving their own input parameters to drive the report (starting date, category, etc. which provide the bounces of the report or chart).

However, using such tools with Drupal has a few decided disadvantages:

• It's not Drupal, i.e. you have set up a totally different environment which has an entirely different set of dependencies than your Drupal site and for many people this is not an option or at least not a viable option because it means maintaining a completely different hosting environment just for the reporting.
• Certain things are duplicated like login system, user accounts, permissions and database connection info. Drupal already has very workable facilities for these things and it would be good to not have to duplicate them for the reporting setup. This actually applies also down to things like the theming system in Drupal, widgets, etc. - there's a lot of this stuff which Drupal already has.
• Most of these tools are in Java and it takes a different skillset work with that than what you already have working on your Drupal website.

In terms of what modules exist already that seem to have anything to do with this, what I've seen is:
http://drupal.org/handbook/modules/statistics - good for specific site stats, doesn't appear to provide much ability to easily customize or to report off of data other than browsing stats
http://drupal.org/project/report - kind of an extension of statistics module above, still very oriented toward browsing stats
http://drupal.org/project/chart - very cool way of making charts, might be great to use for chart output of any custom reporting tool

However none of these provides a generic reporting tool.

As such, I am looking at the possibility of creating such a tool.

Major requirements would be:

• Would leverage existing Drupal framework features such as login, users, permissions, charts (as mentioned above),
• Would allow Views, SQL or PHP code to be used as the data source.
• Would provide table output as well as major types of charts such as pie charts, bar charts, line graphs, etc.
• Would allow reports to be created by a report administrator and the permissions system of Drupal used to decide who can have access to which reports.
• Would allow custom theming of report output using Drupal's existing theming mechanism.
• Users using a report would need to be able to provide input parameters (perhaps webform could be used for this...)
• Other modules should be easily able to implement a hook to define their own default reports, which can then be customized by an administrator - in much the same style as is done with Views, Imagecache and many others.
• Would have to eventually allow for PDF output and other such formats, although this would likely not be in any initial release unless someone has a bright idea on how to do this easily.
• Maybe not in the first version, but would need to have a way of working on aggregated data for very large data sets. That's likely a separate module in itself, but often large datasets need to be "cooked" (denormalized) in order to be querable at a fast speed. This is also often done on a different system than the live production database. This aspect definitely needs some thought... and I'm not sure this is a job for PHP.

Am looking for feedback in terms of the overall approach - and anything that might lead to a solution to the overall problem more rapidly than building what's laid out above. Unfortunately, from what I can see at this point that would be the "right" way of approaching it (said with reservation - there's always multiple ways to approach a problem, each with pros and cons) - but that's probably a lot of work.