How to write custom reports in Drupal

Question

What's the "right" way in Drupal to create reports? I was hoping to use a view but am not having much luck. My goal is to create a table of rows containing three fields: user name, location, SUM of volunteer hours. Once I have this part working, I plan to expose filters for Location and Date.

Views Calc only allows you to group by one field. I know Crystal Reports and MSSQL Reporting Services and I was hoping to find a similar kind of thing for Drupal. Is there a framework, examples, or a module to help with this, or do I need to write a custom module implementing the views_alter_SQL hook to get the desired data for each report?

---

EDIT: I ended up getting it to work with BIRT reports, which gave a lot more power than Views could allow. Code is on my blog: http://nicholaiburton.com/blog/2010/creating-custom-reports-for-drupal

Answer 1

in any way, for Sum of Volunteer hours you need to build custom module, so building custom query more simpler, than attaching custom field to Views via it's hooks...

Answer 2

You could implement a views_query_alter but you might be better off implementing a custom views field handler, because I assume that your user.uid joins to hours.uid. This will probably be your best long term solution.

All you'd need to do is just tell views how your tables join and define the handler for the total hours. You can find help/docs -> http://views-help.doc.logrus.com/help/views/api