Odoo Pivot Tables and Graph Views: Analytical Reporting Guide

Pivot View Overview

The pivot view is Odoo's built-in tool for multidimensional data analysis. It generates an interactive pivot table from any model with stored fields, supporting row and column grouping, multiple measures, and drill-down exploration.

<pivot string="Sales Analysis">
    <field name="user_id" type="row"/>
    <field name="date_order" type="col" interval="month"/>
    <field name="amount_total" type="measure"/>
    <field name="product_uom_qty" type="measure"/>
</pivot>

Pivot Field Types

Users can interactively add, remove, and reorder row/column groupings in the UI. The XML definition sets the default configuration.

Date Intervals

Date fields support interval grouping:

<field name="date_order" type="col" interval="month"/>
<!-- Available: day, week, month, quarter, year -->

Graph View Overview

The graph view visualizes data as bar charts, line charts, or pie charts. It uses the same field definitions as pivot:

<graph string="Sales Chart" type="bar">
    <field name="user_id"/>
    <field name="amount_total" type="measure"/>
</graph>

Graph Types

Stacked Bar Charts

<graph string="Revenue by Product" type="bar" stacked="True">
    <field name="categ_id"/>
    <field name="user_id"/>
    <field name="amount_total" type="measure"/>
</graph>

Building a Complete Report View

A typical analytical report combines pivot, graph, and search views:

<!-- Pivot View -->
<record id="view_sale_report_pivot" model="ir.ui.view">
    <field name="name">sale.report.pivot</field>
    <field name="model">sale.report</field>
    <field name="arch" type="xml">
        <pivot string="Sales Analysis" sample="1">
            <field name="date" type="col" interval="month"/>
            <field name="categ_id" type="row"/>
            <field name="price_total" type="measure"/>
            <field name="product_uom_qty" type="measure"/>
        </pivot>
    </field>
</record>

<!-- Graph View -->
<record id="view_sale_report_graph" model="ir.ui.view">
    <field name="name">sale.report.graph</field>
    <field name="model">sale.report</field>
    <field name="arch" type="xml">
        <graph string="Sales Chart" type="line">
            <field name="date" interval="month"/>
            <field name="price_total" type="measure"/>
        </graph>
    </field>
</record>

<!-- Search View with Filters -->
<record id="view_sale_report_search" model="ir.ui.view">
    <field name="name">sale.report.search</field>
    <field name="model">sale.report</field>
    <field name="arch" type="xml">
        <search>
            <filter name="this_month" string="This Month"
                    domain="[('date', '>=', (context_today()).strftime('%Y-%m-01'))]" />
            <filter name="this_year" string="This Year"
                    domain="[('date', '>=', (context_today()).strftime('%Y-01-01'))]" />
            <separator/>
            <filter name="group_user" string="Salesperson" context="{'group_by': 'user_id'}"/>
            <filter name="group_month" string="Month" context="{'group_by': 'date:month'}"/>
            <filter name="group_product" string="Product" context="{'group_by': 'product_id'}"/>
        </search>
    </field>
</record>

<!-- Action -->
<record id="action_sale_report" model="ir.actions.act_window">
    <field name="name">Sales Analysis</field>
    <field name="res_model">sale.report</field>
    <field name="view_mode">graph,pivot,tree</field>
    <field name="search_view_id" ref="view_sale_report_search"/>
    <field name="context">{'search_default_this_month': 1}</field>
</record>

Custom Report Model (SQL View)

For complex reporting, create a SQL view model:

from odoo import fields, models, tools

class CustomSaleReport(models.Model):
    _name = 'custom.sale.report'
    _description = 'Custom Sales Report'
    _auto = False
    _rec_name = 'date'
    _order = 'date desc'

    date = fields.Date('Date', readonly=True)
    partner_id = fields.Many2one('res.partner', 'Customer', readonly=True)
    user_id = fields.Many2one('res.users', 'Salesperson', readonly=True)
    categ_id = fields.Many2one('product.category', 'Category', readonly=True)
    amount = fields.Float('Revenue', readonly=True)
    quantity = fields.Float('Quantity', readonly=True)
    margin = fields.Float('Margin', readonly=True)
    order_count = fields.Integer('Orders', readonly=True)

    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute(f"""
            CREATE OR REPLACE VIEW {self._table} AS (
                SELECT
                    MIN(sol.id) AS id,
                    so.date_order::date AS date,
                    so.partner_id,
                    so.user_id,
                    pt.categ_id,
                    SUM(sol.price_subtotal) AS amount,
                    SUM(sol.product_uom_qty) AS quantity,
                    SUM(sol.margin) AS margin,
                    COUNT(DISTINCT so.id) AS order_count
                FROM sale_order_line sol
                JOIN sale_order so ON sol.order_id = so.id
                JOIN product_product pp ON sol.product_id = pp.id
                JOIN product_template pt ON pp.product_tmpl_id = pt.id
                WHERE so.state = 'sale'
                GROUP BY so.date_order::date, so.partner_id, so.user_id, pt.categ_id
            )
        """)

Pivot View Options

<pivot string="Report" sample="1" disable_linking="True">
    <!-- sample="1": show sample data when empty -->
    <!-- disable_linking="True": disable click-to-drill-down -->
    <field name="categ_id" type="row"/>
    <field name="amount" type="measure" widget="monetary"/>
</pivot>

Measure Widgets

Measures can use widgets for formatting:

<field name="amount" type="measure" widget="monetary"/>
<field name="margin_percent" type="measure" widget="percentage"/>
<field name="duration" type="measure" widget="float_time"/>

Tips

• Only stored fields can be used as measures — computed non-stored fields will not work
• Use SQL view models for reports needing joins across models
• The sample="1" attribute shows demo data when the view is empty — good for demos
• Pivot views support Excel export — users can download data with the Download button
• Combine with search filters for interactive date ranges and groupings
• Use context in the action to set default filters