In this article, we’ll explore how to add a Tags Filter in the Sales Reporting feature of Odoo. The data in the Reporting tab is often retrieved through SQL queries based on selected filters. However, filtering by tags can lead to SQL query errors when the name field is used in both the sale.order and crm.tag tables.

To address this, we’ll create a new field (tag_name) in the crm.tag model and update it with the tag’s name. Then, we’ll extend the sale.report model to incorporate this new field for filtering.

Step 1: Create a New Field in the crm.tag Model

To ensure there are no SQL conflicts, we’ll add a computed and stored field, tag_name, in the crm.tag model. This field will duplicate the value of the name field.

Code:

python

from odoo import api, fields, models
class CrmTag(models.Model):
    _inherit = "crm.tag"
    tag_name = fields.Char(string=’Tag Name’, compute="_compute_tag_name", store=True)
    @api.depends(‘name’)
    def _compute_tag_name(self):
        for rec in self:
            rec.tag_name = rec.name

This ensures that every time a new tag is created or the name field is updated, the tag_name field will store the same value.

Update Existing Records

For existing records, update the tag_name field in the database using the following SQL query:

sql

UPDATE crm_tag SET tag_name = name;

Step 2: Inherit the sale.report Model

Next, extend the sale.report model to include the tag_name field in the reporting queries.

Code:

python

from odoo import models, fields

class SaleReport(models.Model):

    _inherit = "sale.report"

    tag_name = fields.Char(string=’Tags’, store=True, readonly=True)

    def _query(self, with_clause=”, fields={}, groupby=”, from_clause=”):

        fields[‘tag_name’] = ", c.tag_name as tag_name"

        return super(SaleReport, self)._query(with_clause, fields, groupby, from_clause)

    def _from_sale(self, from_clause=”):

        res = super(SaleReport, self)._from_sale()

        from_str = res + """

            LEFT JOIN sale_order_tag_rel r ON (s.id = r.order_id)

            LEFT JOIN crm_tag c ON (r.tag_id = c.id)

        """

        return from_str

    def _group_by_sale(self, groupby=”):

        return super(SaleReport, self)._group_by_sale() + ", c.tag_name"

    def _select_sale(self, fields={}):

        return super(SaleReport, self)._select_sale() + ", c.tag_name as tag_name"

Step 3: Explanation of Query Changes

1. Join Tables:
   • We added LEFT JOIN statements in the _from_sale method to link sale_order_tag_rel (many-to-many relationship table between sale.order and crm.tag) and crm_tag.
2. Fields Inclusion:
   • The tag_name field from crm_tag is included in the _select_sale method so it appears in the report’s data.
3. Grouping:
   • The tag_name field is added to the _group_by_sale method to ensure proper aggregation.
4. SQL Query Adjustment:
   • The _query method is updated to include tag_name as part of the selected fields.

Real-Life Use Case

Imagine a company using Odoo ERP for tracking sales performance. Tags such as “VIP Customer,” “High Priority,” or “Discount Eligible” are assigned to sales orders. When generating reports, the sales manager wants to filter the data by tags based on customer type or specific campaigns.

By adding a tag_name filter in the reporting tab, the manager can:

1. Easily filter and view sales orders associated with specific tags.
2. Gain insights into sales trends for various customer segments or promotions.

Benefits of This Approach

1. Error Prevention: Avoid SQL query errors caused by duplicate field names in sale.order and crm.tag tables.
2. Performance Optimization: Precomputed fields (tag_name) reduce runtime calculations, improving report generation speed.
3. Enhanced Reporting: Filtering by tags provides a deeper understanding of sales performance.

By implementing the above changes, you can extend Odoo’s sales reporting capabilities to include robust tag-based filtering. This solution ensures smooth SQL query execution while enhancing the usability of the reporting feature.