Jeevachaithanyan SivanandanIn Odoo development, there are instances where it becomes necessary to introduce custom fields into the 'Group By' or "Filter" options within reports. While the conventional approach involves modifying extended views for reports, this may not universally apply. For instance, when augmenting the Sales Analysis report with custom fields, simply adding the fields to the View file proves ineffective. Although the fields may appear in the view, clicking on them triggers an error, as illustrated below:
return call_kw(request.env[model], method, args, kwargs)
File "C:\Work\SmartOdoo\odoo\odoo\api.py", line 462, in call_kw
result = _call_kw_model(method, model, args, kwargs)
File "C:\Work\SmartOdoo\odoo\odoo\api.py", line 435, in _call_kw_model
result = method(recs, *args, **kwargs)
File "C:\Work\SmartOdoo\odoo\addons\web\models\models.py", line 237, in web_read_group
groups = self._web_read_group(domain, fields, groupby, limit, offset, orderby, lazy)
File "C:\Work\SmartOdoo\odoo\addons\web\models\models.py", line 262, in _web_read_group
groups = self.read_group(domain, fields, groupby, offset=offset, limit=limit,
File "C:\Work\SmartOdoo\odoo\odoo\models.py", line 2717, in read_group
rows = self._read_group(domain, annoted_groupby.values(), annoted_aggregates.values(), offset=offset, limit=limit, order=orderby)
File "C:\Work\SmartOdoo\odoo\odoo\models.py", line 1906, in _read_group
self.env.cr.execute(SQL("\n").join(query_parts))
File "C:\Work\SmartOdoo\odoo\odoo\sql_db.py", line 332, in execute
res = self._obj.execute(query, params)
psycopg2.errors.UndefinedColumn: column sale_report.treatment_type does not exist
LINE 1: SELECT "sale_report"."treatment_type", "sale_report"."length...
Upon initial inspection, it may seem that the model 'sale.report' lacks the specified field. However, the actual issue lies in the absence of the field in the database query. This discrepancy arises because the report in question doesn't retrieve data from views but rather directly queries the database. To verify this, examination of the parent 'sale.report' model reveals the underlying code:
# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.
from odoo import api, fields, models, tools
from odoo.addons.sale.models.sale_order import SALE_ORDER_STATE
class SaleReport(models.Model):
_name = "sale.report"
_description = "Sales Analysis Report"
_auto = False
_rec_name = 'date'
_order = 'date desc'
@api.model
def _get_done_states(self):
return ['sale']
# sale.order fields
name = fields.Char(string="Order Reference", readonly=True)
date = fields.Datetime(string="Order Date", readonly=True)
partner_id = fields.Many2one(comodel_name='res.partner', string="Customer", readonly=True)
company_id = fields.Many2one(comodel_name='res.company', readonly=True)
pricelist_id = fields.Many2one(comodel_name='product.pricelist', readonly=True)
team_id = fields.Many2one(comodel_name='crm.team', string="Sales Team", readonly=True)
user_id = fields.Many2one(comodel_name='res.users', string="Salesperson", readonly=True)
state = fields.Selection(selection=SALE_ORDER_STATE, string="Status", readonly=True)
analytic_account_id = fields.Many2one(
comodel_name='account.analytic.account', string="Analytic Account", readonly=True)
invoice_status = fields.Selection(
selection=[
('upselling', "Upselling Opportunity"),
('invoiced', "Fully Invoiced"),
('to invoice', "To Invoice"),
('no', "Nothing to Invoice"),
], string="Invoice Status", readonly=True)
campaign_id = fields.Many2one(comodel_name='utm.campaign', string="Campaign", readonly=True)
medium_id = fields.Many2one(comodel_name='utm.medium', string="Medium", readonly=True)
source_id = fields.Many2one(comodel_name='utm.source', string="Source", readonly=True)
# res.partner fields
commercial_partner_id = fields.Many2one(
comodel_name='res.partner', string="Customer Entity", readonly=True)
country_id = fields.Many2one(
comodel_name='res.country', string="Customer Country", readonly=True)
industry_id = fields.Many2one(
comodel_name='res.partner.industry', string="Customer Industry", readonly=True)
partner_zip = fields.Char(string="Customer ZIP", readonly=True)
state_id = fields.Many2one(comodel_name='res.country.state', string="Customer State", readonly=True)
# sale.order.line fields
order_reference = fields.Reference(string='Related Order', selection=[('sale.order', 'Sales Order')], group_operator="count_distinct")
categ_id = fields.Many2one(
comodel_name='product.category', string="Product Category", readonly=True)
product_id = fields.Many2one(
comodel_name='product.product', string="Product Variant", readonly=True)
product_tmpl_id = fields.Many2one(
comodel_name='product.template', string="Product", readonly=True)
product_uom = fields.Many2one(comodel_name='uom.uom', string="Unit of Measure", readonly=True)
product_uom_qty = fields.Float(string="Qty Ordered", readonly=True)
qty_to_deliver = fields.Float(string="Qty To Deliver", readonly=True)
qty_delivered = fields.Float(string="Qty Delivered", readonly=True)
qty_to_invoice = fields.Float(string="Qty To Invoice", readonly=True)
qty_invoiced = fields.Float(string="Qty Invoiced", readonly=True)
price_subtotal = fields.Monetary(string="Untaxed Total", readonly=True)
price_total = fields.Monetary(string="Total", readonly=True)
untaxed_amount_to_invoice = fields.Monetary(string="Untaxed Amount To Invoice", readonly=True)
untaxed_amount_invoiced = fields.Monetary(string="Untaxed Amount Invoiced", readonly=True)
weight = fields.Float(string="Gross Weight", readonly=True)
volume = fields.Float(string="Volume", readonly=True)
discount = fields.Float(string="Discount %", readonly=True)
discount_amount = fields.Monetary(string="Discount Amount", readonly=True)
# aggregates or computed fields
nbr = fields.Integer(string="# of Lines", readonly=True)
currency_id = fields.Many2one(comodel_name='res.currency', compute='_compute_currency_id')
@api.depends_context('allowed_company_ids')
def _compute_currency_id(self):
self.currency_id = self.env.company.currency_id
def _with_sale(self):
return ""
def _select_sale(self):
select_ = f"""
MIN(l.id) AS id,
l.product_id AS product_id,
t.uom_id AS product_uom,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.product_uom_qty / u.factor * u2.factor) ELSE 0 END AS product_uom_qty,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.qty_delivered / u.factor * u2.factor) ELSE 0 END AS qty_delivered,
CASE WHEN l.product_id IS NOT NULL THEN SUM((l.product_uom_qty - l.qty_delivered) / u.factor * u2.factor) ELSE 0 END AS qty_to_deliver,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.qty_invoiced / u.factor * u2.factor) ELSE 0 END AS qty_invoiced,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.qty_to_invoice / u.factor * u2.factor) ELSE 0 END AS qty_to_invoice,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.price_total
/ {self._case_value_or_one('s.currency_rate')}
* {self._case_value_or_one('currency_table.rate')}
) ELSE 0
END AS price_total,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.price_subtotal
/ {self._case_value_or_one('s.currency_rate')}
* {self._case_value_or_one('currency_table.rate')}
) ELSE 0
END AS price_subtotal,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.untaxed_amount_to_invoice
/ {self._case_value_or_one('s.currency_rate')}
* {self._case_value_or_one('currency_table.rate')}
) ELSE 0
END AS untaxed_amount_to_invoice,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.untaxed_amount_invoiced
/ {self._case_value_or_one('s.currency_rate')}
* {self._case_value_or_one('currency_table.rate')}
) ELSE 0
END AS untaxed_amount_invoiced,
COUNT(*) AS nbr,
s.name AS name,
s.date_order AS date,
s.state AS state,
s.invoice_status as invoice_status,
s.partner_id AS partner_id,
s.user_id AS user_id,
s.company_id AS company_id,
s.campaign_id AS campaign_id,
s.medium_id AS medium_id,
s.source_id AS source_id,
t.categ_id AS categ_id,
s.pricelist_id AS pricelist_id,
s.analytic_account_id AS analytic_account_id,
s.team_id AS team_id,
p.product_tmpl_id,
partner.commercial_partner_id AS commercial_partner_id,
partner.country_id AS country_id,
partner.industry_id AS industry_id,
partner.state_id AS state_id,
partner.zip AS partner_zip,
CASE WHEN l.product_id IS NOT NULL THEN SUM(p.weight * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END AS weight,
CASE WHEN l.product_id IS NOT NULL THEN SUM(p.volume * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END AS volume,
l.discount AS discount,
CASE WHEN l.product_id IS NOT NULL THEN SUM(l.price_unit * l.product_uom_qty * l.discount / 100.0
/ {self._case_value_or_one('s.currency_rate')}
* {self._case_value_or_one('currency_table.rate')}
) ELSE 0
END AS discount_amount,
concat('sale.order', ',', s.id) AS order_reference"""
additional_fields_info = self._select_additional_fields()
template = """,
%s AS %s"""
for fname, query_info in additional_fields_info.items():
select_ += template % (query_info, fname)
return select_
def _case_value_or_one(self, value):
return f"""CASE COALESCE({value}, 0) WHEN 0 THEN 1.0 ELSE {value} END"""
def _select_additional_fields(self):
"""Hook to return additional fields SQL specification for select part of the table query.
:returns: mapping field -> SQL computation of field, will be converted to '_ AS _field' in the final table definition
:rtype: dict
"""
return {}
def _from_sale(self):
return """
sale_order_line l
LEFT JOIN sale_order s ON s.id=l.order_id
JOIN res_partner partner ON s.partner_id = partner.id
LEFT JOIN product_product p ON l.product_id=p.id
LEFT JOIN product_template t ON p.product_tmpl_id=t.id
LEFT JOIN uom_uom u ON u.id=l.product_uom
LEFT JOIN uom_uom u2 ON u2.id=t.uom_id
JOIN {currency_table} ON currency_table.company_id = s.company_id
""".format(
currency_table=self.env['res.currency']._get_query_currency_table(self.env.companies.ids, fields.Date.today())
)
def _where_sale(self):
return """
l.display_type IS NULL"""
def _group_by_sale(self):
return """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
s.partner_id,
s.user_id,
s.state,
s.invoice_status,
s.company_id,
s.campaign_id,
s.medium_id,
s.source_id,
s.pricelist_id,
s.analytic_account_id,
s.team_id,
p.product_tmpl_id,
partner.commercial_partner_id,
partner.country_id,
partner.industry_id,
partner.state_id,
partner.zip,
l.discount,
s.id,
currency_table.rate"""
def _query(self):
with_ = self._with_sale()
return f"""
{"WITH" + with_ + "(" if with_ else ""}
SELECT {self._select_sale()}
FROM {self._from_sale()}
WHERE {self._where_sale()}
GROUP BY {self._group_by_sale()}
{")" if with_ else ""}
"""
@property
def _table_query(self):
return self._query()
Consequently, the solution involves adding the new field to the database query. This can be achieved by extending the methods in the inherited 'sale.report' model, as exemplified below:
from odoo import api, fields, models
class PurchaseReport(models.Model):
_inherit = "purchase.report"
treatment_type = fields.Char(string='Treatment Type', readonly=True)
length = fields.Char(string='Length', readonly=True)
def _select(self):
return super(PurchaseReport, self)._select() + ", t.treatment_type as treatment_type, "
def _group_by(self):
return super(PurchaseReport, self)._group_by() + ", t.id"
Following the reboot of Odoo and the module upgrade, this modification becomes part of the database query during report generation.
Determining when to employ this approach can be intricate, given that Odoo generates different reports in distinct ways. The recommended method involves inspecting the parent report model to discern the appropriate course of action.
By following this methodical approach, Odoo developers can seamlessly integrate custom fields into reports, ensuring accurate data representation and a streamlined user experience.
*Now, you dont have to add the fields into the filter as in the dropdown menu list but just check if your new field is inside once you ' Add Custom Filter', if yes then the work is done ! *
