How To Customize Existing Dynamic Reports In Odoo 16

In Odoo 16, Dynamic Reports are customizable reports that allow users to modify and filter data at runtime, providing a flexible and interactive experience. Dynamic reports can be created using Odoo's built-in report engine, which allows users to design and format reports using a drag-and-drop interface. Users can create dynamic reports by selecting the fields and data sources they want to include and setting filtering and sorting options. 

In this blog, we will discuss how to customize or extend the existing dynamic reports in Odoo 16.

The screenshot mentioned above depicts our current purchase order report. Here, we can see some customizations, like adding new columns and adding various filter options. So first, we need to extend the existing purchase report.

First, we can see how to add a new column to an existing report. Here we can extend the existing template first. So first, create an XML file in static.

static/src/xml.

<?xml version="1.0" encoding="UTF-8" ?>
<odoo>
   <t t-name="dynamicReportView" t-inherit="purchase_report_generator.PurchaseOrderTable"
      t-inherit-mode="extension">
       <xpath expr="//div[hasclass('table_main_view')]/table/thead/tr" position="inside">
           <th>Vendor Reference</th>
       </xpath>
   </t>
</odoo>

Here I'm adding a new column vendor reference in the existing report.

Then we can see how to add the data to the newly added column. So create a new .py file and inherit the existing model.

from odoo import models, fields, api
class AccountReportPartnerLedger(models.AbstractModel):
   _inherit = 'dynamic.purchase.report'

Then add the data on existing functions.

def _get_report_sub_lines(self, data, report, date_from, date_to):
   report_sub_lines = []
new_filter = None
   if data.get('report_type') == 'report_by_order':
       query = '''
                select l.name,l.date_order,l.partner_id,l.amount_total,l.notes,l.partner_ref,l.user_id,res_partner.name as partner,
                         res_users.partner_id as user_partner,sum(purchase_order_line.product_qty),l.id as id,
                        (SELECT res_partner.name as salesman FROM res_partner WHERE res_partner.id = res_users.partner_id)
                        from purchase_order as l
                        left join res_partner on l.partner_id = res_partner.id
                        left join res_users on l.user_id = res_users.id   
                        left join purchase_order_line on l.id = purchase_order_line.order_id
                         '''
       term = 'Where '
       if data.get('date_from'):
           query += "Where l.date_order >= '%s' " % data.get('date_from')
           term = 'AND '
       if data.get('date_to'):
           query += term + "l.date_order <= '%s' " % data.get('date_to')
       query += "group by l.user_id,res_users.partner_id,res_partner.name,l.partner_id,l.date_order,l.name,l.amount_total,l.notes,l.id"
       self._cr.execute(query)
       report_by_order = self._cr.dictfetchall()
       report_sub_lines.append(report_by_order)
       return report_sub_lines

Next, add the data fetching code on .xml file.

<?xml version="1.0" encoding="UTF-8" ?>
<odoo>
   <t t-name="dynamicReportView" t-inherit="purchase_report_generator.PurchaseOrderTable"
      t-inherit-mode="extension">
       <xpath expr="//div[hasclass('table_main_view')]/table/thead/tr" position="inside">        
 <th>Vendor Reference</th>
       </xpath>
       <xpath expr="//div[hasclass('table_main_view')]/table/tbody/t/tr" position="inside">
           <td style="text-align:center;">
               <span>
                   <t t-esc="dynamic_purchase_report['partner_ref']"/>
               </span>
           </td>
       </xpath>
   </t>
</odoo>

Here we can see the data fetched from the new added column.

This is the field vendor reference on the purchase order form.

This data is added to the purchase report, the same way we can customize the existing report with our needs , in this blog we can see we can extend the template of the existing report and added a column and then we can extend the python function of the report and then we added the data on the xml file , here you can have to add column and various type filters and etc. 

Then we can see how we can add a new filter in the existing filter report type.

We can see report types are filtered by report by order. Next, we can add other report types as report by product and report by order detail.

<div class="search-Result-Selection">
   <div class="dropdown">
       <a class="btn btn-secondary dropdown-togglereport-type"
          href="#" role="button" id="dropdownMenuLink"
          data-bs-toggle="dropdown" aria-expanded="false">
           <span class="fa fa-book"/>
           <span class="low_case dropdown-toggle">Report Type :</span>
       </a>
       <select id="selection" class="dropdown-menu report_type"
               aria-labelledby="dropdownMenuLink"
               name="states[]">
           <div role="separator" class="dropdown-divider"/>
           <option value="report_by_order" selected="">Report
               By
               Order
           </option>
           <option value="report_by_product">Report By
               Product
           </option>
       </select>
       <span id="report_res"/>
   </div>
</div>

Here we can see that we can add a “report type is report by product” in existing code.

We can then add the table when the "Report by Product" report type is clicked.

<div t-if="order.report_type == 'report_by_product'">
   <div class="table_main_view">
       <table cellspacing="0" width="100%">
           <thead>
               <tr class="table_pr_head">
                   <th>Category</th>
                   <th class="mon_fld">Product Code</th>
                   <th class="mon_fld">Product Name</th>
                   <th class="mon_fld">Qty</th>
                   <th class="mon_fld">Amount Total</th>
               </tr>
           </thead>

Now we can see the report type product type is added.

And added the query to get data on the existing query.

elif data.get('report_type') == 'report_by_product':
 query = '''
   select l.amount_total,sum(purchase_order_line.product_qty) as qty, purchase_order_line.name as product, purchase_order_line.price_unit,product_product.default_code,product_category.name
            from purchase_order as l
            left join purchase_order_line on l.id = purchase_order_line.order_id
            left join product_product on purchase_order_line.product_id = product_product.id
            left join product_template on purchase_order_line.product_id = product_template.id
            left join product_category on product_category.id = product_template.categ_id
                      '''
   term = 'Where '
   if data.get('date_from'):
       query += "Where l.date_order >= '%s' " % data.get('date_from')
       term = 'AND '
   if data.get('date_to'):
       query += term + "l.date_order <= '%s' " % data.get('date_to')
   query += "group by l.amount_total,purchase_order_line.name,purchase_order_line.price_unit,purchase_order_line.product_id,product_product.default_code,product_template.categ_id,product_category.name"
   self._cr.execute(query)
   report_by_product = self._cr.dictfetchall()
   report_sub_lines.append(report_by_product)

Here, we can see the report type is filtered by product details.

In this blog, you can see how we can extend the existing functions of the dynamic reports for customization. How to add various filters in the dynamic report is defined as the blog https://www.cybrosys.com/blog/how-to-create-a-dynamic-report-in-odoo-16

 You can refer to the link below for more about dynamic report creation on Odoo 16.