Reports based on custom SQL

The report we built was based on a regular recordset. However, in some cases we need to transform or aggregate data in ways that are not easy or desirable to process in a QWeb template.

One approach for this is to write a SQL query to build the dataset we need, expose those results through a special model, and have our report work based on a recordset.

To showcase this, we will create a reports/todo_task_report.py file with the following code:

from odoo import models, fields 
 
class TodoReport(models.Model):
    _name = 'todo.task.report' 
    _description = 'To-do Report'
    _auto = False

    name = fields.Char('Description') 
    is_done = fields.Boolean('Done?') 
    active = fields.Boolean('Active?') 
    user_id = fields.Many2one('res.users', 'Responsible') 
    date_deadline = fields.Date('Deadline') 

    def init(self):
        self.env.cr.execute("""
           CREATE OR REPLACE VIEW todo_task_report AS
           (SELECT *
           FROM todo_task
...