Reports based on custom SQL

The report we built was based on a regular recordset. But in some cases we need to transform or aggregate data in ways that are not easy when processing data on the fly, such as while rendering the report.

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.

For this, we will create a reports/todo_task_report.py file with this code:

# -*- coding: utf-8 -*- 
from odoo import models, fields 
 
class TodoReport(models.Model): 
    _name = 'todo.task.report' 
    _description = 'To-do Report'
    _sql = """
           CREATE OR REPLACE VIEW todo_task_report AS
           SELECT *
           FROM todo_task
           WHERE active = True
           """ 
   ...