How to Create a Pivot Report using Query in Odoo 19

Odoo 19 is a powerful, flexible open-source ERP platform that helps businesses manage and optimize operations across various areas, including Accounting, Sales, Inventory, and HR. The latest release introduces a refreshed user interface, faster performance, and more automation tools, making it suitable for organizations of any size. A key advantage of Odoo 19 is its ability to deliver real-time reports and analytics, giving decision-makers reliable insights based on up-to-date data.

One of the highlights of this version is the upgraded Pivot View, which allows users to analyze and summarize large datasets with ease. Pivot reports make it simple to group, filter, and aggregate information, transforming raw records into actionable insights. While Odoo provides ready-to-use pivot reports for many standard modules, advanced users can design custom reports using SQL queries. This is particularly valuable for more complex reporting needs, such as financial overviews, sales performance tracking, or vendor bill monitoring. By combining Odoo’s ORM with PostgreSQL views, businesses can create flexible and dynamic reports that remain directly accessible inside the system.

As an illustration, consider building a Vendor Bill Report that provides financial analysis of supplier invoices. The report can include critical details such as: Invoice Name ,Bill Month, Vendor Name, Purchase Order Reference, Number of Bills, Total Billed Amount, Paid Amount, Outstanding Amount, Company, Responsible Salesperson, Journal, and Invoice Date.

Defining the Model

Because this report is based on an SQL view rather than a standard Odoo model with its own database table, we need to declare the model so that it doesn’t try to automatically generate a table. In Odoo 19, this is done by setting the attribute _auto = False in the model class. This tells Odoo to map the model directly to the SQL view instead of creating a new table during initialization.

Python:

from odoo import models, fields

class VendorBillReport(models.Model):
   _name = "vendor.bill.report"
   _description = "Vendor Bill Report"
   _auto = False  # Use SQL view instead of creating a table
   invoice_name = fields.Char(string="Invoice Name", readonly=True)
   bill_month = fields.Char(string="Bill Month", readonly=True)
   vendor_name = fields.Char(string="Vendor Name", readonly=True)
   purchase_order_name = fields.Char(string="Purchase Order", readonly=True)
   total_bills = fields.Integer(string="Total Bills", readonly=True)
   total_billed = fields.Float(string="Total Billed Amount", readonly=True)
   total_paid = fields.Float(string="Total Paid", readonly=True)
   total_unpaid = fields.Float(string="Total Unpaid", readonly=True)
   company_id = fields.Many2one("res.company", string="Company", readonly=True)
   sale_person_id = fields.Many2one("res.users", string="Salesperson", readonly=True)
   journal_id = fields.Many2one("account.journal", string="Journal", readonly=True)
   invoice_date = fields.Date(string="Invoice Date", readonly=True)

Building the SQL View

In Odoo 19, you can link your model to a database view by overriding the init() method. Inside this method, raw SQL is executed to create or refresh the view. The view will serve as the data source for the model defined with _auto = False, ensuring that Odoo reads directly from the SQL query results instead of a physical table.

    def init(self):
   """
   Creates or replaces the SQL view for the Vendor Bill Report Pivot table.
   This view aggregates data from account moves to provide a summary of
   vendor bills based on bill month, vendor, purchase orders, and payment status.
   """
   self.env.cr.execute("""
        CREATE OR REPLACE VIEW vendor_bill_report AS (
            SELECT
                row_number() OVER() AS id,
                am.name AS invoice_name,
                TO_CHAR(am.invoice_date, 'YYYY-MM') AS bill_month,
                rp.name AS vendor_name,
                COALESCE(po.name, 'No Purchase Order') AS purchase_order_name,
                COUNT(DISTINCT am.id) AS total_bills,
                SUM(am.amount_total) AS total_billed,
                SUM(CASE WHEN am.payment_state = 'paid' THEN am.amount_total ELSE 0 END) AS total_paid,
                SUM(CASE WHEN am.payment_state = 'not_paid' THEN am.amount_total ELSE 0 END) AS total_unpaid,
                am.company_id,
                am.invoice_user_id AS sale_person_id,
                am.journal_id,
                am.invoice_date
            FROM
                account_move am
            JOIN
                res_partner rp ON am.partner_id = rp.id
            LEFT JOIN
                account_move_line aml ON aml.move_id = am.id
            LEFT JOIN
                purchase_order_line pol ON aml.purchase_line_id = pol.id
            LEFT JOIN
                purchase_order po ON pol.order_id = po.id
            WHERE
                am.move_type = 'in_invoice'  -- Only vendor bills
                AND am.state = 'posted'      -- Only posted invoices
            GROUP BY
                am.name, bill_month, rp.name, po.name,
                am.company_id, am.invoice_user_id, am.journal_id, am.invoice_date
            ORDER BY
                bill_month DESC, total_billed DESC
        );
    """)

Defining the Pivot View in XML

To make the Vendor Bill Report available in the Odoo interface, we create a pivot view in an XML file. This view specifies how the report data should be structured, giving users the ability to explore and analyze it across multiple dimensions such as vendor, bill month, and payment status.

Xml:

<?xml version="1.0" encoding="utf-8"?>
<odoo>
   <record id="vendor_bill_report_pivot_view" model="ir.ui.view">
       <field name="name">vendor.bill.report.pivot</field>
       <field name="model">vendor.bill.report</field>
       <field name="arch" type="xml">
           <pivot string="Vendor Bills Analysis">
               <field name="invoice_name" type="row"/>
               <field name="vendor_name" type="col"/>
               <field name="total_billed" type="measure"/>
               <field name="total_paid" type="measure"/>
               <field name="total_unpaid" type="measure"/>
           </pivot>
       </field>
   </record>
   <record id="vendor_bill_report_action" model="ir.actions.act_window">
       <field name="name">Vendor Bills Report</field>
       <field name="res_model">vendor.bill.report</field>
       <field name="view_mode">pivot</field>
   </record>
   <menuitem id="menu_vendor_bill_report"
             name="Vendor Bills Report"
             parent="account.account_reports_management_menu"
             action="vendor_bill_report_action"/>
</odoo>

• type="row"

<field name="invoice_name" type="row"/>

This defines the rows of the pivot table, where each row corresponds to a specific invoice.

• type="col"

<field name="vendor_name" type="col"/>

This defines the columns of the pivot table, where each column corresponds to a different vendor.

• type="measure"

<field name="total_billed" type="measure"/>

<field name="total_paid" type="measure"/>

<field name="total_unpaid" type="measure"/>

These fields are the metrics being calculated and displayed, showing totals for billed, paid, and unpaid amounts.

These fields represent the aggregated values in the pivot table and are displayed as measures.

Once the XML and Python files are added to your custom module and the module is installed, a new menu option will be available under Invoicing > Reporting > Vendor Bills Report. Selecting this option will open the pivot view, where users can explore vendor bills with dynamic filters, grouping options, and aggregation features.

To read more about How to Create Pivot Report Using Query in Odoo 18, refer to our blog How to Create Pivot Report Using Query in Odoo 18.