Aggregation via report_table_by_sql

Say you want to get a report of the top sellers from the last quarter, involving a two or more tables ('orders' and 'products', for instance).

If you're using ActiveRecord, you can do all sorts of things with the find syntax. It may be easier to write some simple sql, though.

Ruport doesn't have built-in aggregation, but it does let you roll your own sql. So, you can build a simple report like so:

report = Order.report_table_by_sql %Q{ 
  select product_id, product_name, count(*) as count from orders
  left outer join products on orders.product_id = products.id
  where order_date <= '#{ start_date }' and order_date >= '#{end_date}'
  group by product_id
  order by count DESC
}

You can then manipulate the report using all the handy ruport methods and renderers.

For instance,

report.remove_column('product_id')
report.rename_columns(['product_name', 'count'], ['Product', 'Sales'])
report.to_csv