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
"""
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')
For this file to be loaded we need to add a from . import reports line to the top __init__.py file, and from . import todo_task_report to the reports/__init__.py file.
The sql attribute is used to override the database table automatic creation, providing an SQL for that. We want it to create a database view to provide the data needed for the report. Our SQL query is quite simple, but the point is that we could use any valid SQL query for our view.
We also mapped the fields we need with ORM field types, so that they are available on recordsets generated on this model.
Next we can add a new report based on this model, reports/todo_model_report.xml:
<odoo>
<report id="action_todo_model_report"
string="To-do Special Report"
model="todo.task"
report_type="qweb-html"
name="todo_report.report_todo_task_special"
/>
<template id="report_todo_task_special">
<t t-call="report.html_container">
<t t-call="report.external_layout">
<div class="page">
<!-- Report page content -->
<table class="table table-striped">
<tr>
<th>Title</th>
<th>Owner</th>
<th>Deadline</th>
</tr>
<t t-foreach="docs" t-as="o">
<tr>
<td class="col-xs-6">
<span t-field="o.name" />
</td>
<td class="col-xs-3">
<span t-field="o.user_id" />
</td>
<td class="col-xs-3">
<span t-field="o.date_deadline" />
</td>
</tr>
</t>
</table>
</div>
</t>
</t>
</template>
</odoo>
For even more complex cases, we can use a different solution: a wizard. For this we should create a transient model with related lines, where the header includes report parameters, introduced by the user, and the lines will have the generated data to be used by the report. These lines are generated by a model method that can contain whatever logic we may need. It is strongly recommended to get inspiration from an existing similar report.