How to Custom Pivot & Graph View in Odoo

In the world of Odoo development, providing users with raw data is rarely enough. To truly empower a business, you need to transform that data into actionable insights. This is where Pivot and Graph views come into play. These views form the backbone of Odoo’s built-in Business Intelligence (BI) capabilities, allowing users to slice and dice data without needing external tools like Excel or Tableau.
 
1. The Pivot View: Multidimensional Data Analysis

The Pivot view is used to display data in a grid (pivot table). It allows users to aggregate information across different dimensions (rows and columns).

XML Structure

To create a pivot view, you define a <pivot> element inside the arch of an ir.ui.view record.

XML

<record id="view_sale_order_pivot_custom" model="ir.ui.view">
    <field name="name">sale.order.pivot</field>
    <field name="model">sale.order</field>
    <field name="arch" type="xml">
        <pivot string="Sales Analysis" sample="1" disable_linking="True">
            <field name="date_order" type="row" interval="month"/>
            <field name="team_id" type="col"/>
            <field name="amount_total" type="measure"/>
        </pivot>
    </field>
</record>

Key Attributes and Fields:

• type="row": Sets the field as a vertical grouping (X-axis).
• type="col": Sets the field as a horizontal grouping (Y-axis).
• type="measure": Defines the quantitative data (must be an Integer or Float) that Odoo will calculate (Sum, Average, etc.).
• interval: For date fields, you can specify day, week, month, quarter, or year.
• disable_linking="True": Prevents the user from clicking on a cell to drill down into the list view.

2. The Graph View: Visualizing Trends

The Graph view provides a visual representation of data. It is essential for spotting trends, outliers, and distributions at a glance.

Example Implementation:

XML

<record id="view_sale_order_graph_custom" model="ir.ui.view">
    <field name="name">sale.order.graph</field>
    <field name="model">sale.order</field>
    <field name="arch" type="xml">
        <graph string="Sales Statistics" type="bar" stacked="True">
            <field name="partner_id"/>
            <field name="amount_total" type="measure"/>
        </graph>
    </field>
</record>

Graph Types:

1. Bar (type="bar"): Best for comparing different categories. Use stacked="True" to see the composition of each bar.
2. Line (type="line"): Best for showing trends over time.
3. Pie (type="pie"): Best for showing the proportion of a whole.

3. Advanced Strategy: Using SQL Views for Complex Reports

Sometimes, the data you need isn't in a single table. In Odoo, the best practice for complex reporting is to create a custom Read-Only Model backed by a SQL View.

Step 1: Python Model

You define a model where _auto = False. This tells Odoo not to create a standard table in PostgreSQL.

Python

from odoo import models, fields, api, tools

class SalesReportView(models.Model):
    _name = 'custom.sales.report'
    _description = 'Custom Sales Analysis'
    _auto = False  # Critical: Tells Odoo this is a SQL view

    partner_id = fields.Many2one('res.partner', readonly=True)
    total_revenue = fields.Float(readonly=True)
    order_date = fields.Date(readonly=True)

    def init(self):
        # This function creates the actual SQL view in the database
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute(f"""
            CREATE OR REPLACE VIEW {self._table} AS (
                SELECT 
                    min(s.id) as id,
                    s.partner_id,
                    s.date_order as order_date,
                    SUM(s.amount_total) as total_revenue
                FROM sale_order s
                GROUP BY s.partner_id, s.date_order
            )
        """)

Step 2: Define Action and Menu

To make these views accessible, include pivot and graph in your view_mode.

XML

<record id="action_custom_sales_report" model="ir.actions.act_window">
    <field name="name">Executive Dashboard</field>
    <field name="res_model">custom.sales.report</field>
    <field name="view_mode">graph,pivot</field>
</record>

<menuitem id="menu_sales_report_custom" 
          name="Executive Insights" 
          parent="sale.menu_sale_report" 
          action="action_custom_sales_report"/>

Pro Tips for Better Views

• Sample Data: Adding sample="1" in the XML tag displays a beautiful placeholder UI when no data is found, which is much better for UX than a blank screen.
• Search View Integration: Pivot and Graph views respect the filters applied in the Search view. Always define custom <filter> tags to let users toggle between "This Year" vs. "Last Year."
• Stored Computes: If you are using computed fields as measures, they must have store=True, or Odoo will not be able to aggregate them in the database.

Contact us