Our company loves reports that calculate obscure metrics--metrics that cannot be calculated with ActiveRecord's finders (except find_by_sql) and where ruport's ruby-based capabilities are just too slow.
Is there a plugin or gem or db adapter out there that will do large calculations in the database layer? What's your solution to creating intricate reports?
Thoughtbot's Squirrel plugin adds a lot of Ruby-ish functionality to ActiveRecord's find method, with multi-layered conditionals, ranges, and nested model associations:
www.thoughtbot.com/projects/squirrel/
Is there anything inherent about your reports that prevents the use of an SQL view or stored procedure?
In one particular project, a technique I often find useful is to create your SQL query (that may be quite complex) as a named view in the database, and then use
YourModel.connection.select_all(query)
to pull back the data. It's not an optimal approach; I'm keen to explore improvements to it.
Unfortunately, as you suggested, the support for doing computing complex database-based reports within rails seems fairly limited.
Although not database agnostic, our solution is plpgsql functions where it becomes really slow to use Ruby and ActiveRecord.
You might want to look at using DataMapper or Sequel for your ORM if you're finding that ActiveRecord lacks the expressiveness you need for complex queries. Switching away from ActiveRecord wouldn't be a decision to take likely, but it might be worth investigating at least.
It sounds as if your tables could be normalized. At one place I worked, the amount of normalization we did was impacting our reporting needs, so we created some shadow tables that contained a bunch of the aggregate data, and did reporting against that.
I agree with Neil N's comment that the question is a little vague, but perhaps this gets you moving in the right direction?