How would you handle a practical scenario involving Stored Procedures in SQL Server?

A reporting endpoint needs a complex aggregation; explain when a stored procedure is preferable to ORM-generated SQL and what tradeoffs you would consider.