Dynamic reports in Odoo 17 are flexible and interactive documents that provide a comprehensive overview of various aspects of a business's operations. Unlike static reports, which offer fixed views of data, dynamic reports can be customized in real time to showcase specific metrics, filter criteria, and visualizations. This versatility enables users to analyze data from different angles and gain actionable insights to drive decision-making.
Let's explore the process of crafting a dynamic report in Odoo 17.
To initiate this, begin by creating a new menu through an XML file in the views directory, specifically located at (views/views.xml).
<?xml version="1.0" encoding="UTF-8"?>
<odoo>
<!-- Purchase report action and menu -->
<record id="dynamic_purchase_report_action" model="ir.actions.client">
<field name="name">Purchase Report</field>
<field name="tag">purchase_report</field>
</record>
<menuitem id="dynamic_purchase_report_menu"
name=" Dynamic Purchase Report"
parent="purchase.purchase_report"
action="dynamic_purchase_report_action"/>
</odoo>
In this setup, the "Dynamic Purchase Report" menu is created within the reporting section of the purchase order.
Here, I've created a record named "Purchase Report" within the ir.actions.client. Additionally, I've assigned a tag "purchase_report" in the tag field, which corresponds to the widget's tag for loading or calling the action when the associated menu is clicked. Now, let's proceed to define the JavaScript file located at static/src/js.
/** @odoo-module */
const { Component } = owl;
import { registry } from "@web/core/registry";
import { download } from "@web/core/network/download";
import { useService } from "@web/core/utils/hooks";
import { useRef, useState } from "@odoo/owl";
import { BlockUI } from "@web/core/ui/block_ui";
const actionRegistry = registry.category("actions");
import { uiService } from "@web/core/ui/ui_service";
// Extending components for adding purchase report class
class PurchaseReport extends Component {
async setup() {
super.setup(...arguments);
this.uiService = useService('ui');
this.initial_render = true;
this.orm = useService('orm');
this.action = useService('action');
this.start_date = useRef('date_from');
this.end_date = useRef('date_to');
this.order_by = useRef('order_by');
this.state = useState({
order_line: [],
data: null,
order_by : 'report_by_order',
wizard_id : []
});
this.load_data();
}
async load_data(wizard_id = null) {
/**
* Loads the data for the purchase report.
*/
let move_lines = ''
try {
if(wizard_id == null){
this.state.wizard_id = await this.orm.create("dynamic.purchase.report",[{}]);
}
this.state.data = await this.orm.call("dynamic.purchase.report", "purchase_report", [this.state.wizard_id]);
$.each(this.state.data, function (index, value) {
move_lines = value
})
this.state.order_line = move_lines
}
catch (el) {
window.location.href
}
}
async applyFilter(ev) {
let filter_data = {}
this.state.order_by = this.order_by.el.value
filter_data.date_from = this.start_date.el.value
filter_data.date_to = this.end_date.el.value
filter_data.report_type = this.order_by.el.value
let data = await this.orm.write("dynamic.purchase.report",this.state.wizard_id, filter_data);
this.load_data(this.state.wizard_id)
}
viewPurchaseOrder(ev){
return this.action.doAction({
type: "ir.actions.act_window",
res_model: 'purchase.order',
res_id: parseInt(ev.target.id),
views: [[false, "form"]],
target: "current",
});
}
async print_xlsx() {
/**
* Generates and downloads an XLSX report for the purchase orders.
*/
var data = this.state.data
var action = {
'data': {
'model': 'dynamic.purchase.report',
'options': JSON.stringify(data['orders']),
'output_format': 'xlsx',
'report_data': JSON.stringify(data['report_lines']),
'report_name': 'Purchase Report',
'dfr_data': JSON.stringify(data),
},
};
this.uiService.block();
await download({
url: '/purchase_dynamic_xlsx_reports',
data: action.data,
complete: this.uiService.unblock(),
error: (error) => this.call('crash_manager', 'rpc_error', error),
});
}
async printPdf(ev) {
/**
* Generates and displays a PDF report for the purchase orders.
*
* @param {Event} ev - The event object triggered by the action.
* @returns {Promise} - A promise that resolves to the result of the action.
*/
ev.preventDefault();
var self = this;
var action_title = self.props.action.display_name;
return self.action.doAction({
'type': 'ir.actions.report',
'report_type': 'qweb-pdf',
'report_name': 'purchase_report_generator.purchase_order_report',
'report_file': 'purchase_report_generator.purchase_order_report',
'data': {
'report_data': this.state.data
},
'context': {
'active_model': 'purchase.report',
'landscape': 1,
'purchase_order_report': true
},
'display_name': 'Purchase Order',
});
}
}
PurchaseReport.template = 'PurchaseReport';
actionRegistry.add("purchase_report", PurchaseReport);
In this section, we're extending the Abstract Action class, which displays the model we've defined (PurchaseReport). The load_data function is invoked from the start function, utilizing an RPC function to fetch data from the Python function. The returned value is subsequently passed to the PurchaseReport model, which is then incorporated into the table_view_pr class of the primary model (PurchaseReport).
To complement the RPC call made via the Python function file, we also need to define the QWeb model. Initially, I've created the Python file at (models/filename.py).
from odoo import api, fields, models
import io
import json
try:
from odoo.tools.misc import xlsxwriter
except ImportError:
import xlsxwriter
class DynamicPurchaseReport(models.Model):
"""Model for getting dynamic purchase report """
_name = "dynamic.purchase.report"
_description = "Dynamic Purchase Report"
purchase_report = fields.Char(string="Purchase Report",
help='Name of the report')
date_from = fields.Datetime(string="Date From", help='Start date of report')
date_to = fields.Datetime(string="Date to", help='End date of report')
report_type = fields.Selection([
('report_by_order', 'Report By Order'),
('report_by_order_detail', 'Report By Order Detail')], default='report_by_order',
help='The order of the report')
@api.model
def purchase_report(self, option):
"""Function for getting datas for requests """
report_values = self.env['dynamic.purchase.report'].search(
[('id', '=', option[0])])
data = {
'report_type': report_values.report_type,
'model': self,
}
if report_values.date_from:
data.update({
'date_from': report_values.date_from,
})
if report_values.date_to:
data.update({
'date_to': report_values.date_to,
})
filters = self.get_filter(option)
lines = self._get_report_values(data).get('PURCHASE')
return {
'name': "Purchase Orders",
'type': 'ir.actions.client',
'tag': 's_r',
'orders': data,
'filters': filters,
'report_lines': lines,
}
def get_filter(self, option):
"""Function for get data according to order_by filter """
data = self.get_filter_data(option)
filters = {}
if data.get('report_type') == 'report_by_order':
filters['report_type'] = 'Report By Order'
else:
filters['report_type'] = 'Report By Order Detail'
return filters
def get_filter_data(self, option):
""" Function for get filter data in report """
record = self.env['dynamic.purchase.report'].search([('id', '=', option[0])])
default_filters = {}
filter_dict = {
'report_type': record.report_type,
}
filter_dict.update(default_filters)
return filter_dict
Once the main Python function is defined, you can proceed to define additional functions that execute simple queries. Each row retrieved from these queries is then appended to a list. Finally, this list is returned as the final output.
def _get_report_sub_lines(self, data):
""" Function for get report value using sql query """
report_sub_lines = []
if data.get('report_type') == 'report_by_order':
query = """ select l.name,l.date_order, l.partner_id,l.amount_total,
l.notes,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
where 1=1 """
if data.get('date_from'):
query += """and l.date_order >= '%s' """ % data.get('date_from')
if data.get('date_to'):
query += """ and 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)
else :
query = """ select l.name,l.date_order,l.partner_id,l.amount_total,
l.notes, l.user_id,res_partner.name as partner,res_users.partner_id
as user_partner,sum(purchase_order_line.product_qty),
purchase_order_line.name as product, purchase_order_line.price_unit,
purchase_order_line.price_subtotal,l.amount_total,
purchase_order_line.product_id,product_product.default_code,
(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
left join product_product on purchase_order_line.product_id = product_product.id
where 1=1 """
if data.get('date_from'):
query += """ and l.date_order >= '%s' """ % data.get('date_from')
if data.get('date_to'):
query += """ and 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,
purchase_order_line.name,purchase_order_line.price_unit,
purchase_order_line.price_subtotal,l.amount_total,
purchase_order_line.product_id,product_product.default_code"""
self._cr.execute(query)
report_by_order_details = self._cr.dictfetchall()
report_sub_lines.append(report_by_order_details)
return report_sub_lines
def _get_report_values(self, data):
""" Get report values based on the provided data. """
docs = data['model']
if data.get('report_type'):
report_res = \
self._get_report_sub_lines(data)[0]
else:
report_res = self._get_report_sub_lines(data)
return {
'doc_ids': self.ids,
'docs': docs,
'PURCHASE': report_res,
}
As a result, the RPC call will encapsulate this list and transmit the data to the QWeb model. Now, let's proceed to define the templates, which will be located in static/src/xml.
<?xml version="1.0" encoding="UTF-8" ?>
<templates>
<t t-name="PurchaseReport" owl="1">
<!-- Section contains a structure for the purchase report, including a filter
view and a table view. It has div elements for the filter view and table view,
with respective classes for styling.-->
<div class="">
<div>
<center>
<h1 style="margin: 20px;">Purchase Report</h1>
</center>
</div>
</div>
<div class="print-btns">
<div class="sub_container_left"
style="width: 285px; margin-left: 36px;">
<div class="report_print">
<button type="button" class="btn btn-primary" id="pdf"
style="float: left; margin-right: 9px;"
t-on-click="printPdf">
Print (PDF)
</button>
<button type="button" class="btn btn-primary" id="xlsx"
t-on-click="print_xlsx">
Export (XLSX)
</button>
</div>
</div>
<br/>
<div class="sub_container_right">
<div class="dropdown">
<button class="btn btn-secondary dropdown-toggle time_range_pr"
type="button" id="date_chose"
data-bs-toggle="dropdown" aria-expanded="false">
<span class="fa fa-calendar" title="Dates" role="img"
aria-label="Dates"/>
Date Range
</button>
<div class="dropdown-menu my_custom_dropdown" role="menu"
aria-labelledby="date_chose">
<div class="form-group">
<label class="" for="date_from">Start Date :</label>
<div class="input-group date" id="date_from"
data-target-input="nearest">
<input type="date" name="date_from"
t-ref="date_from"
class="form-control datetimepicker-input"
data-target="#date_from"
t-att-name="prefix"/>
<div class="input-group-append"
data-target="#date_from"
data-toggle="datetimepicker"
style="pointer-events: none;">
</div>
</div>
<label class="" for="date_to">End Date :</label>
<div class="input-group date" id="date_to"
data-target-input="nearest">
<input type="date" name="date_to"
t-ref="date_to"
class="form-control datetimepicker-input"
data-target="#date_to"
t-att-name="prefix"/>
<div class="input-group-append"
data-target="#date_to"
data-toggle="datetimepicker"
style="pointer-events: none;">
</div>
</div>
</div>
</div>
</div>
<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"
t-ref="order_by"
name="states[]">
<div role="separator" class="dropdown-divider"/>
<option value="report_by_order" selected="">Report
By
Order
</option>
<option value="report_by_order_detail">Report By
Order
Detail
</option>
</select>
<span id="report_res" t-out="state.order_by"/>
</div>
</div>
<div class="apply_filter">
<button type="button" id="apply_filter"
class="btn btn-primary" t-on-click="applyFilter">
Apply
</button>
</div>
</div>
</div>
<div class="overflow-auto" style="height: 70vh; padding:10px">
<div t-if="state.order_by == 'report_by_order'">
<div class="table_main_view">
<table cellspacing="0" width="100%">
<thead>
<tr class="table_pr_head">
<th>Order</th>
<th class="mon_fld">Date Order</th>
<th class="mon_fld">Customer</th>
<th class="mon_fld">Purchase Representative</th>
<th class="mon_fld">Total Qty</th>
<th class="mon_fld">Amount Total</th>
<th class="mon_fld">Note</th>
</tr>
</thead>
<tbody>
<t t-foreach="state.order_line"
t-as="dynamic_purchase_report"
t-key="dynamic_purchase_report_index">
<tr style="border: 1.5px solid black;"
class="pr-line"
t-att-data-account-id="dynamic_purchase_report['id']"
t-attf-data-target=".a{{dynamic_purchase_report['id']}}">
<td>
<t t-if="dynamic_purchase_report['id']">
<div class="dropdown dropdown-toggle">
<a data-toggle="dropdown"
href="#"
id="table_toggle_btn"
data-bs-toggle="dropdown"
aria-expanded="false">
<span class="caret"/>
<span>
<t t-esc="dynamic_purchase_report['name']"/>
</span>
</a>
<ul class="dropdown-menu"
role="menu"
aria-labelledby="table_toggle_btn">
<li>
<a class="view_purchase_order"
tabindex="-1"
href="#"
t-att-id="dynamic_purchase_report['id']"
t-on-click="viewPurchaseOrder">
View Purchase Order
</a>
</li>
</ul>
</div>
</t>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['date_order']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['partner']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['salesman']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['sum']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['amount_total']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['notes']"/>
</span>
</td>
</tr>
</t>
</tbody>
</table>
</div>
</div>
<!--Report for order detail-->
<div t-if="state.order_by == 'report_by_order_detail'">
<div class="table_main_view">
<table cellspacing="0" width="100%">
<thead>
<tr class="table_pr_head">
<th>Order</th>
<th class="mon_fld">Date Order</th>
<th class="mon_fld">Customer</th>
<th class="mon_fld">Purchase Representative</th>
<th class="mon_fld">Product Code</th>
<th class="mon_fld">Product Name</th>
<th class="mon_fld">Price unit</th>
<th class="mon_fld">Qty</th>
<th class="mon_fld">Price Subtotal</th>
</tr>
</thead>
<tbody>
<t t-log="state.order_line"/>
<t t-foreach="state.order_line"
t-as="dynamic_purchase_report"
t-key="dynamic_purchase_report_index">
<tr style="border: 1.5px solid black;"
class="pr-line"
t-att-data-account-id="dynamic_purchase_report['id']"
t-attf-data-target=".a{{dynamic_purchase_report['id']}}">
<td>
<div class="dropdown dropdown-toggle">
<a data-toggle="dropdown" href="#"
id="table_toggle_btn"
data-bs-toggle="dropdown"
aria-expanded="false">
<span class="caret"/>
<span>
<t t-esc="dynamic_purchase_report['name']"/>
</span>
</a>
<ul class="dropdown-menu"
role="menu"
aria-labelledby="table_toggle_btn">
<li>
<a class="view_purchase_order"
tabindex="-1" href="#"
t-att-id="dynamic_purchase_report['id']">
View Purchase Order
</a>
</li>
</ul>
</div>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['date_order']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['partner']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['salesman']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['default_code']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['product']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['price_unit']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['sum']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['amount_total']"/>
</span>
</td>
</tr>
</t>
</tbody>
</table>
</div>
</div>
</div>
</t>
</templates>
In this overview of the purchase dynamic report, we encounter several filter options.
In this view, we can observe the products along with their filtered details. Additionally, there's an option available to filter the data based on date ranges.
Dynamic reporting in Odoo 17 empowers businesses to unlock the full potential of their data by providing flexible and customizable reporting solutions. By leveraging dynamic report templates, custom report design tools, and interactive elements, users can gain deeper insights into their business operations and make informed decisions to drive success. Embrace the power of dynamic reporting in Odoo 17, and take your data analysis capabilities to new heights.