Add New Field to Sale Report in Odoo Using _query Method

Adding a field to sale report model in Odoo aids custom reporting. It is tailored to your business demands. Process involves inheriting model adding the essential fields. It involves update of the SQL query. This reflects the new data. Below is a methodological guide for it. The guide uses the _query method in Odoo.

Question is why add fields to sale report?

Sale report model is a source of rich insights into sales data. Yet in some situations you may require more fields. This is to match particular business reporting needs. Addition of a custom field to the model becomes essential. It allows for tracking and reporting operations. These operations are based on data not part of default setup.

Step 1: Inherit Sale Report Model

For addition of new field to sale.report, begin with inheriting model. Define new field. For example, a custom_field. This is to store extra information related to every sales report.

from odoo import models, fields

class SaleReport(models.Model):
    _inherit = 'sale.report'

    custom_field = fields.Char(string="Custom Field")

Step 2: Override the _query Method

Sale.report model uses SQL view. Merely adding a field does not reflect in database. We must override _query method. This action will adjust SQL statement. It populates report.

Next let's override the _query to incorporate a field. Let's call this field custom_field. Let's assume custom_field data originates from note field. The source now is sale.order model.

from odoo import api, models

class SaleReport(models.Model):
    _inherit = 'sale.report'

    custom_field = fields.Char(string="Custom Field")

    @api.model
    def _query(self, with_clause='', fields={}, group_by='', from_clause=''):
        fields['custom_field'] = ", s.note as custom_field"
        group_by += ', s.note'
        return super(SaleReport, self)._query(with_clause, fields, group_by, from_clause)

In this example:

• ▹ fields['custom_field'] appends our new field to select clause of SQL query. Source of this appendage is s.note.
• ▹ group_by is updated. This update includes s.note. We do this to ensure that our field is correctly grouped in the report.

Step 3: Update the Database

After incorporating these changes, restart the Odoo server. Update module. This is how you can apply the modifications. In terminal run this command. This command is to upgrade module.

   odoo -u your_module_name

There is a need to recreate the SQL view. This view has new field now.

Step 4: Add Field to Report View

Move onto including custom_field in Sale Report's tree or pivot view. This way it's visible. It's now in Odoo user interface.

<record id="view_sale_report_tree_inherit" model="ir.ui.view">
    <field name="name">sale.report.tree.inherit</field>
    <field name="model">sale.report</field>
    <field name="inherit_id" ref="sale.view_order_report_tree"/>
    <field name="arch" type="xml">
        <xpath expr="//field[@name='price_total']" position="after">
            <field name="custom_field"/>
        </xpath>
    </field>
</record>

XML sample places custom_field in tree view. It is placed right after price_total field.

Conclusion

Your completion of these steps extends the sale report model. A custom field has been added. The _query method was used for this.

The _query method allows you to smoothly integrate more data. The data goes into sales reports. This makes Odoo more flexible. It adjusts to your reporting needs.