How to Create Dynamic Reports in Odoo 18

Static reports are fine when you just need a snapshot of data. But when managers want to slice and dice information—filter by date, group by salesperson, compare categories—you need dynamic reports. In Odoo 18, building these interactive reports means combining OWL JavaScript components with Python backend logic and QWeb templates.

This tutorial walks through building a complete dynamic sales report. Users can filter by date range, choose different report views (by order, product, or salesperson), and export to PDF or Excel. The same pattern works for any Odoo model—inventory, accounting, HR, whatever data you need to analyze.

What You'll Build

Interactive client action with OWL component
Date range and report type filters
Dynamic data tables that update in real-time
PDF report generation
Excel (XLSX) export functionality

Module Architecture

sales_dynamic_report/

__init__.py

__manifest__.py

controllers/

__init__.py

main.py

models/

__init__.py

sales_report.py

static/src/

js/

sales_report.js

xml/

sales_report_template.xml

views/

menu_views.xml

report/

sales_report_pdf.xml

Step 1: Module Setup and Menu

First, let's create the module manifest and register the menu item that triggers our dynamic report.

__manifest__.py

{
    'name': 'Dynamic Sales Report',
    'version': '18.0.1.0.0',
    'category': 'Sales/Reporting',
    'summary': 'Interactive dynamic sales reports with filters and export',
    'description': 'Create dynamic, filterable sales reports with PDF and Excel export',
    'author': 'Your Company',
    'depends': ['sale', 'web'],
    'data': [
        'security/ir.model.access.csv',
        'views/menu_views.xml',
        'report/sales_report_pdf.xml',
    ],
    'assets': {
        'web.assets_backend': [
            'sales_dynamic_report/static/src/js/sales_report.js',
            'sales_dynamic_report/static/src/xml/sales_report_template.xml',
        ],
    },
    'installable': True,
    'application': False,
    'license': 'LGPL-3',
}

views/menu_views.xml

<?xml version="1.0" encoding="UTF-8"?>
<odoo>
    <!-- Client Action for Dynamic Report -->
    <record id="action_dynamic_sales_report" model="ir.actions.client">
        <field name="name">Sales Analytics</field>
        <field name="tag">sales_dynamic_report</field>
    </record>
    
    <!-- Menu Item -->
    <menuitem id="menu_dynamic_sales_report"
              name="Dynamic Sales Report"
              parent="sale.sale_order_menu"
              action="action_dynamic_sales_report"
              sequence="99"/>
</odoo>

Key Points

ir.actions.client - Creates a client-side action instead of a window action
tag field - Must match the tag used when registering the OWL component
assets section - Registers JavaScript and XML templates with the backend assets

Step 2: The OWL JavaScript Component

This is the heart of the dynamic report—an OWL component that manages state, handles user interactions, and communicates with the Python backend.

static/src/js/sales_report.js

/** @odoo-module */

import { Component, useState, useRef } from "@odoo/owl";
import { registry } from "@web/core/registry";
import { useService } from "@web/core/utils/hooks";
import { download } from "@web/core/network/download";

class SalesDynamicReport extends Component {
    
    setup() {
        // Initialize services
        this.orm = useService('orm');
        this.action = useService('action');
        this.uiService = useService('ui');
        
        // Create refs for filter inputs
        this.dateFromRef = useRef('date_from');
        this.dateToRef = useRef('date_to');
        this.reportTypeRef = useRef('report_type');
        
        // Component state
        this.state = useState({
            reportLines: [],
            reportType: 'by_order',
            isLoading: true,
            wizardId: null,
            summary: {
                totalOrders: 0,
                totalAmount: 0,
                totalQty: 0
            }
        });
        
        // Load initial data
        this.loadReportData();
    }
    
    async loadReportData(wizardId = null) {
        /**
         * Fetches report data from Python backend
         */
        this.state.isLoading = true;
        
        try {
            // Create wizard record if not exists
            if (!wizardId) {
                this.state.wizardId = await this.orm.create(
                    "dynamic.sales.report", 
                    [{}]
                );
            }
            
            // Fetch report data
            const result = await this.orm.call(
                "dynamic.sales.report",
                "get_sales_report",
                [this.state.wizardId]
            );
            
            this.state.reportLines = result.report_lines || [];
            this.state.summary = result.summary || {};
            this.state.reportType = result.report_type || 'by_order';
            
        } catch (error) {
            console.error('Failed to load report data:', error);
        } finally {
            this.state.isLoading = false;
        }
    }
    
    async applyFilters() {
        /**
         * Applies date and report type filters
         */
        const filterData = {
            date_from: this.dateFromRef.el?.value || false,
            date_to: this.dateToRef.el?.value || false,
            report_type: this.reportTypeRef.el?.value || 'by_order'
        };
        
        // Update wizard record with filter values
        await this.orm.write(
            "dynamic.sales.report",
            [this.state.wizardId],
            filterData
        );
        
        // Reload data with new filters
        await this.loadReportData(this.state.wizardId);
    }
    
    viewSalesOrder(ev) {
        /**
         * Opens the sales order form view
         */
        const orderId = parseInt(ev.target.dataset.orderId);
        
        return this.action.doAction({
            type: "ir.actions.act_window",
            res_model: 'sale.order',
            res_id: orderId,
            views: [[false, "form"]],
            target: "current",
        });
    }
    
    async printPdf() {
        /**
         * Generates and downloads PDF report
         */
        return this.action.doAction({
            type: 'ir.actions.report',
            report_type: 'qweb-pdf',
            report_name: 'sales_dynamic_report.sales_report_template',
            report_file: 'sales_dynamic_report.sales_report_template',
            data: {
                report_lines: this.state.reportLines,
                summary: this.state.summary
            },
            context: { active_model: 'dynamic.sales.report' },
            display_name: 'Sales Report',
        });
    }
    
    async exportXlsx() {
        /**
         * Generates and downloads Excel report
         */
        this.uiService.block();
        
        try {
            await download({
                url: '/sales_dynamic_report/export_xlsx',
                data: {
                    wizard_id: this.state.wizardId,
                    report_data: JSON.stringify(this.state.reportLines),
                    summary: JSON.stringify(this.state.summary),
                },
            });
        } finally {
            this.uiService.unblock();
        }
    }
}

SalesDynamicReport.template = 'SalesDynamicReportTemplate';

// Register the component as an action
registry.category("actions").add("sales_dynamic_report", SalesDynamicReport);

Component Breakdown

useState

Reactive state management—when state values change, the template automatically re-renders.

useRef

Creates references to DOM elements, allowing direct access to input values.

useService('orm')

ORM service for CRUD operations—create, read, write, and call methods on models.

registry.add()

Registers the component as an action handler for the tag defined in menu_views.xml.

Step 3: The QWeb Template

The OWL template defines the UI structure—filters, buttons, and data tables.

static/src/xml/sales_report_template.xml

<?xml version="1.0" encoding="UTF-8"?>
<templates xml:space="preserve">
    <t t-name="SalesDynamicReportTemplate" owl="1">
        <div class="o_sales_dynamic_report">
            
            <!-- Report Header -->
            <div class="report-header">
                <h1>Sales Analytics Report</h1>
            </div>
            
            <!-- Filter Section -->
            <div class="filter-section">
                <div class="filter-row">
                    <!-- Export Buttons -->
                    <div class="export-buttons">
                        <button class="btn btn-primary" 
                                t-on-click="printPdf">
                            <i class="fa fa-file-pdf-o"/> Export PDF
                        </button>
                        <button class="btn btn-success" 
                                t-on-click="exportXlsx">
                            <i class="fa fa-file-excel-o"/> Export Excel
                        </button>
                    </div>
                    
                    <!-- Date Filters -->
                    <div class="date-filters">
                        <div class="filter-group">
                            <label>From:</label>
                            <input type="date" t-ref="date_from" 
                                   class="form-control"/>
                        </div>
                        <div class="filter-group">
                            <label>To:</label>
                            <input type="date" t-ref="date_to" 
                                   class="form-control"/>
                        </div>
                    </div>
                    
                    <!-- Report Type Selector -->
                    <div class="report-type-selector">
                        <label>Report Type:</label>
                        <select t-ref="report_type" class="form-control">
                            <option value="by_order">By Order</option>
                            <option value="by_product">By Product</option>
                            <option value="by_salesperson">By Salesperson</option>
                            <option value="by_customer">By Customer</option>
                        </select>
                    </div>
                    
                    <button class="btn btn-primary apply-btn" 
                            t-on-click="applyFilters">
                        Apply Filters
                    </button>
                </div>
            </div>
            
            <!-- Summary Cards -->
            <div class="summary-cards">
                <div class="summary-card">
                    <span class="card-label">Total Orders</span>
                    <span class="card-value" t-esc="state.summary.totalOrders"/>
                </div>
                <div class="summary-card">
                    <span class="card-label">Total Quantity</span>
                    <span class="card-value" t-esc="state.summary.totalQty"/>
                </div>
                <div class="summary-card">
                    <span class="card-label">Total Revenue</span>
                    <span class="card-value">
                        $<t t-esc="state.summary.totalAmount"/>
                    </span>
                </div>
            </div>
            
            <!-- Loading State -->
            <div t-if="state.isLoading" class="loading-overlay">
                <div class="spinner"/>
                <p>Loading report data...</p>
            </div>
            
            <!-- Report Table: By Order -->
            <div t-if="!state.isLoading &amp;&amp; state.reportType === 'by_order'" 
                 class="report-table">
                <table class="table table-striped">
                    <thead>
                        <tr>
                            <th>Order</th>
                            <th>Date</th>
                            <th>Customer</th>
                            <th>Salesperson</th>
                            <th>Quantity</th>
                            <th>Amount</th>
                        </tr>
                    </thead>
                    <tbody>
                        <t t-foreach="state.reportLines" t-as="line" 
                           t-key="line.id">
                            <tr>
                                <td>
                                    <a href="#" t-att-data-order-id="line.id"
                                       t-on-click="viewSalesOrder">
                                        <t t-esc="line.name"/>
                                    </a>
                                </td>
                                <td t-esc="line.date_order"/>
                                <td t-esc="line.partner_name"/>
                                <td t-esc="line.salesperson"/>
                                <td t-esc="line.total_qty"/>
                                <td>$<t t-esc="line.amount_total"/></td>
                            </tr>
                        </t>
                    </tbody>
                </table>
            </div>
            
        </div>
    </t>
</templates>

Step 4: Python Backend Model

The Python model stores filter parameters and executes the SQL queries that power the report.

models/sales_report.py

from odoo import models, fields, api


class DynamicSalesReport(models.TransientModel):
    _name = 'dynamic.sales.report'
    _description = 'Dynamic Sales Report Wizard'
    
    date_from = fields.Date(string='Start Date')
    date_to = fields.Date(string='End Date')
    report_type = fields.Selection([
        ('by_order', 'By Order'),
        ('by_product', 'By Product'),
        ('by_salesperson', 'By Salesperson'),
        ('by_customer', 'By Customer'),
    ], default='by_order', string='Report Type')
    
    def get_sales_report(self):
        """
        Main method called by JavaScript to fetch report data.
        Returns report lines and summary based on selected filters.
        """
        self.ensure_one()
        
        report_lines = self._get_report_lines()
        summary = self._calculate_summary(report_lines)
        
        return {
            'report_lines': report_lines,
            'summary': summary,
            'report_type': self.report_type,
        }
    
    def _get_report_lines(self):
        """
        Fetches report data based on report type.
        Uses raw SQL for performance on large datasets.
        """
        if self.report_type == 'by_order':
            return self._get_by_order()
        elif self.report_type == 'by_product':
            return self._get_by_product()
        elif self.report_type == 'by_salesperson':
            return self._get_by_salesperson()
        elif self.report_type == 'by_customer':
            return self._get_by_customer()
        return []
    
    def _get_by_order(self):
        """Report grouped by sales orders."""
        query = """
            SELECT 
                so.id,
                so.name,
                so.date_order::date as date_order,
                rp.name as partner_name,
                u.login as salesperson,
                SUM(sol.product_uom_qty) as total_qty,
                so.amount_total
            FROM sale_order so
            LEFT JOIN res_partner rp ON so.partner_id = rp.id
            LEFT JOIN res_users u ON so.user_id = u.id
            LEFT JOIN sale_order_line sol ON so.id = sol.order_id
            WHERE so.state IN ('sale', 'done')
        """
        
        params = []
        if self.date_from:
            query += " AND so.date_order >= %s"
            params.append(self.date_from)
        if self.date_to:
            query += " AND so.date_order <= %s"
            params.append(self.date_to)
            
        query += """
            GROUP BY so.id, so.name, so.date_order, rp.name, 
                     u.login, so.amount_total
            ORDER BY so.date_order DESC
        """
        
        self.env.cr.execute(query, tuple(params))
        return self.env.cr.dictfetchall()
    
    def _get_by_product(self):
        """Report grouped by products."""
        query = """
            SELECT 
                pt.name as product_name,
                pp.default_code as product_code,
                pc.name as category,
                SUM(sol.product_uom_qty) as total_qty,
                SUM(sol.price_subtotal) as total_amount
            FROM sale_order_line sol
            LEFT JOIN sale_order so ON sol.order_id = so.id
            LEFT JOIN product_product pp ON sol.product_id = pp.id
            LEFT JOIN product_template pt ON pp.product_tmpl_id = pt.id
            LEFT JOIN product_category pc ON pt.categ_id = pc.id
            WHERE so.state IN ('sale', 'done')
        """
        
        params = []
        if self.date_from:
            query += " AND so.date_order >= %s"
            params.append(self.date_from)
        if self.date_to:
            query += " AND so.date_order <= %s"
            params.append(self.date_to)
            
        query += """
            GROUP BY pt.name, pp.default_code, pc.name
            ORDER BY total_amount DESC
        """
        
        self.env.cr.execute(query, tuple(params))
        return self.env.cr.dictfetchall()
    
    def _get_by_salesperson(self):
        """Report grouped by salesperson."""
        query = """
            SELECT 
                rp.name as salesperson,
                COUNT(DISTINCT so.id) as order_count,
                SUM(sol.product_uom_qty) as total_qty,
                SUM(so.amount_total) as total_amount
            FROM sale_order so
            LEFT JOIN res_users u ON so.user_id = u.id
            LEFT JOIN res_partner rp ON u.partner_id = rp.id
            LEFT JOIN sale_order_line sol ON so.id = sol.order_id
            WHERE so.state IN ('sale', 'done')
        """
        
        params = []
        if self.date_from:
            query += " AND so.date_order >= %s"
            params.append(self.date_from)
        if self.date_to:
            query += " AND so.date_order <= %s"
            params.append(self.date_to)
            
        query += """
            GROUP BY rp.name
            ORDER BY total_amount DESC
        """
        
        self.env.cr.execute(query, tuple(params))
        return self.env.cr.dictfetchall()
    
    def _get_by_customer(self):
        """Report grouped by customer."""
        query = """
            SELECT 
                rp.name as customer,
                COUNT(DISTINCT so.id) as order_count,
                SUM(sol.product_uom_qty) as total_qty,
                SUM(so.amount_total) as total_amount
            FROM sale_order so
            LEFT JOIN res_partner rp ON so.partner_id = rp.id
            LEFT JOIN sale_order_line sol ON so.id = sol.order_id
            WHERE so.state IN ('sale', 'done')
        """
        
        params = []
        if self.date_from:
            query += " AND so.date_order >= %s"
            params.append(self.date_from)
        if self.date_to:
            query += " AND so.date_order <= %s"
            params.append(self.date_to)
            
        query += """
            GROUP BY rp.name
            ORDER BY total_amount DESC
        """
        
        self.env.cr.execute(query, tuple(params))
        return self.env.cr.dictfetchall()
    
    def _calculate_summary(self, report_lines):
        """Calculate summary statistics from report lines."""
        total_orders = len(report_lines)
        total_qty = sum(line.get('total_qty', 0) or 0 for line in report_lines)
        total_amount = sum(
            line.get('amount_total', 0) or line.get('total_amount', 0) or 0 
            for line in report_lines
        )
        
        return {
            'totalOrders': total_orders,
            'totalQty': round(total_qty, 2),
            'totalAmount': round(total_amount, 2),
        }

Step 5: Excel Export Controller

The controller handles the Excel file generation and download.

controllers/main.py

import json
import io
from odoo import http
from odoo.http import request, content_disposition

try:
    import xlsxwriter
except ImportError:
    xlsxwriter = None


class SalesReportController(http.Controller):
    
    @http.route('/sales_dynamic_report/export_xlsx', type='http', 
                auth='user', methods=['POST'], csrf=False)
    def export_xlsx(self, wizard_id, report_data, summary, **kw):
        """
        Generates and returns an Excel file with sales report data.
        """
        if not xlsxwriter:
            return request.make_response(
                'xlsxwriter library not installed',
                headers=[('Content-Type', 'text/plain')]
            )
        
        # Parse JSON data
        report_lines = json.loads(report_data)
        summary_data = json.loads(summary)
        
        # Create Excel file in memory
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        worksheet = workbook.add_worksheet('Sales Report')
        
        # Define formats
        header_format = workbook.add_format({
            'bold': True,
            'bg_color': '#cb0000',
            'font_color': 'white',
            'border': 1,
            'align': 'center'
        })
        cell_format = workbook.add_format({
            'border': 1,
            'align': 'left'
        })
        number_format = workbook.add_format({
            'border': 1,
            'align': 'right',
            'num_format': '#,##0.00'
        })
        
        # Write headers
        headers = ['Order', 'Date', 'Customer', 'Salesperson', 'Qty', 'Amount']
        for col, header in enumerate(headers):
            worksheet.write(0, col, header, header_format)
        
        # Write data rows
        for row, line in enumerate(report_lines, start=1):
            worksheet.write(row, 0, line.get('name', ''), cell_format)
            worksheet.write(row, 1, str(line.get('date_order', '')), cell_format)
            worksheet.write(row, 2, line.get('partner_name', ''), cell_format)
            worksheet.write(row, 3, line.get('salesperson', ''), cell_format)
            worksheet.write(row, 4, line.get('total_qty', 0), number_format)
            worksheet.write(row, 5, line.get('amount_total', 0), number_format)
        
        # Add summary row
        summary_row = len(report_lines) + 2
        worksheet.write(summary_row, 0, 'TOTAL', header_format)
        worksheet.write(summary_row, 4, summary_data.get('totalQty', 0), number_format)
        worksheet.write(summary_row, 5, summary_data.get('totalAmount', 0), number_format)
        
        # Adjust column widths
        worksheet.set_column(0, 0, 15)
        worksheet.set_column(1, 1, 12)
        worksheet.set_column(2, 3, 20)
        worksheet.set_column(4, 5, 12)
        
        workbook.close()
        output.seek(0)
        
        return request.make_response(
            output.read(),
            headers=[
                ('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'),
                ('Content-Disposition', content_disposition('Sales_Report.xlsx'))
            ]
        )

Dependency Note

The xlsxwriter library is required for Excel export. Install it with: pip install xlsxwriter

What You've Built

You now have a complete dynamic reporting system: an OWL component handling the UI and interactions, Python backend processing data with raw SQL for performance, and export functionality for both PDF and Excel. This same architecture extends to any Odoo model—just modify the queries and template columns to match your data requirements.

Frequently Asked Questions

What is a dynamic report in Odoo 18?

A dynamic report in Odoo 18 is an interactive report that allows users to filter, sort, and customize data in real-time without page reloads. Unlike static PDF reports that show fixed data, dynamic reports use OWL JavaScript components to update the display based on user selections like date ranges, grouping options, and filter criteria. They combine a frontend OWL component with Python backend queries.

How do I register an OWL component as a client action in Odoo 18?

First, create your OWL component class extending Component from @odoo/owl. Then use registry.category('actions').add('your_tag', YourComponent) to register it. In your XML, create an ir.actions.client record with a tag field matching your_tag. When a user clicks a menu item linked to this action, Odoo loads and renders your component.

Why use raw SQL instead of ORM methods for reports?

Raw SQL queries offer significant performance advantages for reports that aggregate large datasets. ORM methods like search_read load full records into memory, which is inefficient when you only need aggregated values. SQL with GROUP BY, SUM, and COUNT executes directly on the database server and returns only the computed results, making reports much faster especially with thousands of records.

How do I add Excel export to my Odoo dynamic report?

Create an HTTP controller with a route that accepts report data as JSON. Use the xlsxwriter library to create an Excel workbook in memory, write your headers and data rows, then return the file with appropriate Content-Type and Content-Disposition headers. In your OWL component, use the download utility from @web/core/network/download to trigger the file download.

What is the difference between ir.actions.client and ir.actions.act_window?

ir.actions.act_window opens a standard Odoo view (form, list, kanban) for a model. ir.actions.client loads a custom JavaScript component identified by its tag. Use act_window for standard CRUD views and client for custom interfaces like dashboards, reports, or specialized UIs that don't follow the standard view structure.

AI Solutions

Cloud & AWS

Shopify

Odoo & ERP

AI Solutions

AI Support Agent

AI Inventory Agent

AI Finance Agent

Free AI Audit

AI Chatbot

AI Agent Development

AI Development

MCP Server

Blog

Case Studies

Dead Stock Calculator

Guides & Playbooks

Tutorials

How to Create Dynamic Reports in Odoo 18

Step 1: Module Setup and Menu

Step 2: The OWL JavaScript Component

Component Breakdown

Step 3: The QWeb Template

Step 4: Python Backend Model

Step 5: Excel Export Controller

Frequently Asked Questions