we have growing demands for more and more granular reporting of business activity taking place on our drupal site (e.g. counts of different types of cck objects created over time), along with charts and other management type reporting.
i have explored lots of options (most either crazy expensive, or overwhelming in hosting requirements) and have recently been exporting this type of data in xml feeds for consumption in google spreadsheets. its working very well. most of our queries are beyond the capabilities of views, and so we write straight sql, embed it in a php page in our root folder, and return the result as xml. google apps scripts have a strong ability to fetch, parse, and write the data to hidden sheets in a spreadsheet, which can then use it for summary tables/charts/etc.
as these php pages start to multiply, i'm designing a module to centralize administration/maintenance of these reporting type needs.
the basic structure of a "xml report" would be: title, path (e.g. reports/xml/usercountbyday), and query.
obviously, when i'm personally creating these queries and reports, i know not to export information that should not go out over an insecure connection. but the current design would expose privacy risks for others using it (someone creates and exposes a report of every users email address and someone else gets a hold of the report url).
any thoughts on how to approach this to secure it but not make it impossible/restrictive/slow?
