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. Once a report is created, users can run it and adjust filtering and sorting options in real time without regenerating the report. Dynamic reports in Odoo 16 are suitable for a wide range of applications, from generating ad-hoc reports for specific use cases to creating complex reports that can be used for data analysis and business intelligence. They provide a powerful and flexible reporting solution that can be customized to meet the unique needs of any business.
Odoo 16 Dynamic Reporting introduces several new features that improve user experience and provide greater flexibility in report creation.
* Customizable headers and footers: Odoo 16 allows users to customize the headers and footers of their dynamic reports, including the ability to add logos, images, and other branding elements.
* Conditional Formatting: The new conditional formatting feature allows users to define rules that change the format of specific cells or rows based on the data values they contain. This makes it easier to identify trends and patterns in large data sets.
* Improved filtering options: Odoo 16 offers more advanced filtering options for dynamic reports, including the ability to filter by date range, multiple values, etc.
* Customizable charts and graphs: Users can now create custom charts and graphs in their dynamic reports, choosing from a variety of chart types and customizing colors, labels, and other visual elements.
* Real-time data update: Dynamic report in Odoo 16 now supports real-time data update, and users can see the latest data without regenerating the report.
Then we can see how to create a dynamic report in Odoo 16.
Firstly you can create a new A menu in an XML file in the views directory
(views/views.xml).
<?xml version="1.0" encoding="UTF-8"?>
<odoo>
<record id="purchase_all_report_action" model="ir.actions.client">
<field name="name">Purchase Report</field>
<field name="tag">p_r</field>
</record>
<menuitem action="purchase_all_report_action" parent="purchase.purchase_report"
id="purchase_report_sub_menu"
name="Purchase Report"/>
</odoo>
Here we can see the menu purchase report is created under the reporting menu in the purchase order.
Here I created a record in the ir.actions.customer template called Purchase Report (we can name it whatever we want). Also, I entered the p_r tag in the tag field, which is the tag used in the widget to load the action or call the action defined in the JS file when the menu is clicked.
Now let's define the JS file (static/src/js).
odoo.define('purchase_report_generator.purchase_report', function(require) {
'use strict';
var AbstractAction = require("web.AbstractAction");
var core = require("web.core");
var rpc = require('web.rpc');
var _t = core._t;
var QWeb = core.qweb;
var datepicker = require("web.datepicker");
var time = require('web.time');
var framework = require('web.framework');
var session = require('web.session');
var PurchaseReport = AbstractAction.extend({
template: 'PurchaseReport',
events: {
'click #apply_filter': 'apply_filter',
'click #pdf': 'print_pdf',
'click .view_purchase_order': 'button_view_order',
'mousedown div.input-group.date[data-target-input="nearest"]': '_onCalendarIconClick',
},
init: function(parent, action) {
this._super(parent, action);
this.report_lines = action.report_lines;
this.wizard_id = action.context.wizard | null;
},
start: function() {
var self = this;
self.initial_render = true;
rpc.query({
model: 'dynamic.purchase.report',
method: 'create',
args: [{
}]
}).then(function(res) {
self.wizard_id = res;
console.log('res',res);
self.load_data(self.initial_render);
console.log('self',self.initial_render);
})
},
_onCalendarIconClick: function(ev) {
console.log('calendar icon clicked',ev);
var $calendarInputGroup = $(ev.currentTarget);
console.log('calendar icon clicked2',$calendarInputGroup);
var calendarOptions = {
minDate: moment({
y: 1000
}),
maxDate: moment().add(200, 'y'),
calendarWeeks: true,
defaultDate: moment().format(),
sideBySide: true,
buttons: {
showClear: true,
showClose: true,
showToday: true,
},
icons: {
date: 'fa fa-calendar',
},
locale: moment.locale(),
format: time.getLangDateFormat(),
widgetParent: 'body',
allowInputToggle: true,
};
console.log(calendarOptions,'sdsdsdsadn')
$calendarInputGroup.datetimepicker(calendarOptions);
},
load_data: function(initial_render = true) {
var self = this;
self._rpc({
model: 'dynamic.purchase.report',
method: 'purchase_report',
args: [
[this.wizard_id]
],
}).then(function(datas) {
if (initial_render) {
self.$('.filter_view_pr').html(QWeb.render('PurchaseFilterView', {
filter_data: datas['filters'],
}));
self.$el.find('.report_type').select2({
placeholder: ' Report Type...',
});
}
if (datas['orders'])
self.$('.table_view_pr').html(QWeb.render('PurchaseOrderTable', {
filter: datas['filters'],
order: datas['orders'],
report_lines: datas['report_lines'],
main_lines: datas['report_main_line']
}));
})
},
print_pdf: function(e) {
e.preventDefault();
var self = this;
var action_title = self._title;
self._rpc({
model: 'dynamic.purchase.report',
method: 'purchase_report',
args: [
[self.wizard_id]
],
}).then(function(data) {
var action = {
'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': data
},
'context': {
'active_model': 'purchase.report',
'landscape': 1,
'purchase_order_report': true
},
'display_name': 'Purchase Order',
};
return self.do_action(action);
});
},
button_view_order: function(event) {
event.preventDefault();
var self = this;
var context = {};
this.do_action({
name: _t("Purchase Order"),
type: 'ir.actions.act_window',
res_model: 'purchase.order',
view_type: 'form',
domain: [
['id', '=', $(event.target).closest('.view_purchase_order').attr('id')]
],
views: [
[false, 'list'],
[false, 'form']
],
target: 'current'
});
},
//
apply_filter: function() {
var self = this;
self.initial_render = false;
var filter_data_selected = {};
if (this.$el.find('.datetimepicker-input[name="date_from"]').val()) {
filter_data_selected.date_from = moment(this.$el.find('.datetimepicker-input[name="date_from"]').val(), time.getLangDateFormat()).locale('en').format('YYYY-MM-DD');
}
if (this.$el.find('.datetimepicker-input[name="date_to"]').val()) {
filter_data_selected.date_to = moment(this.$el.find('.datetimepicker-input[name="date_to"]').val(), time.getLangDateFormat()).locale('en').format('YYYY-MM-DD');
}
if ($(".report_type").length) {
console.log()
var report_res = document.getElementById("report_res")
filter_data_selected.report_type = $(".report_type")[1].value
report_res.value = $(".report_type")[1].value
report_res.innerHTML = report_res.value.replaceAll('_', ' ');
if ($(".report_type")[1].value == "") {
report_res.innerHTML = "report_by_order";
}
}
rpc.query({
model: 'dynamic.purchase.report',
method: 'write',
args: [
self.wizard_id, filter_data_selected
],
}).then(function(res) {
self.initial_render = false;
self.load_data(self.initial_render);
});
},
});
core.action_registry.add("pu_r", PurchaseReport);
return PurchaseReport;
});So here we are extending the Abstract Action class. When I do this, the model I defined (PurchaseReport) is displayed.
The load data function is called from the start function, which uses an RPC function to retrieve data from the python function. The return value is then passed to the model ("PurchaseReport"), which is then added to the table_view_pr class of the main model ("PurchaseReport").
Now that the RPC is called through the python function file, we also need to define the qweb model.
So first, I created the python file (models/filename.py).
from odoo import models, fields, api
import io
import json
try:
from odoo.tools.misc import xlsxwriter
except ImportError:
import xlsxwriter
class DynamicPurchaseReport(models.Model):
_name = "dynamic.purchase.report"
purchase_report = fields.Char(string="Purchase Report")
date_from = fields.Datetime(string="Date From")
date_to = fields.Datetime(string="Date to")
report_type = fields.Selection([
('report_by_order', 'Report By Order'),
('report_by_product', 'Report By Product')], default='report_by_order')
@api.model
def purchase_report(self, option):
orders = self.env['purchase.order'].search([])
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)
report = self._get_report_values(data)
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):
data = self.get_filter_data(option)
filters = {}
if data.get('report_type') == 'report_by_order':
filters['report_type'] = 'Report By Order'
return filters
def get_filter_data(self, option):
r = self.env['dynamic.purchase.report'].search([('id', '=', option[0])])
default_filters = {}
filter_dict = {
'report_type': r.report_type,
}
filter_dict.update(default_filters)
return filter_dict
After defining the main python function, you can define functions that perform simple queries and append each row to a list, which is the final list returned.
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.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)
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)
return report_sub_lines
def _get_report_values(self, data):
docs = data['model']
date_from = data.get('date_from')
date_to = data.get('date_to')
if data['report_type'] == 'report_by_order_detail':
report = ['Report By Order Detail']
elif data['report_type'] == 'report_by_product':
report = ['Report By Product']
elif data['report_type'] == 'report_by_categories':
report = ['Report By Categories']
elif data['report_type'] == 'report_by_purchase_representative':
report = ['Report By Purchase Representative']
elif data['report_type'] == 'report_by_state':
report = ['Report By State']
else:
report = ['Report By Order']
if data.get('report_type'):
report_res = \
self._get_report_sub_lines(data, report, date_from, date_to)[0]
else:
report_res = self._get_report_sub_lines(data, report, date_from,
date_to)
return {
'doc_ids': self.ids,
'docs': docs,
'PURCHASE': report_res,
}
Therefore, an RPC call will contain this list and pass the data to the qweb model.
Let's define the templates (in static/src/xml).
<templates>
<t t-name="PurchaseReport">
<div class="">
<div>
<center>
<h1 style="margin: 20px;">Purchase Report</h1>
</center>
</div>
<div>
<div class="filter_view_pr"/>
</div>
<div>
<div class="table_view_pr" style="width: 95%; margin: auto;"/>
</div>
</div>
</t>
<t t-name="PurchaseFilterView">
<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">
Print (PDF)
</button>
<button type="button" class="btn btn-primary" id="xlsx">
Export (XLSX)
</button>
</div>
</div>
<br></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="text" name="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;">
<span class="input-group-text">
<span class="fa fa-calendar" role="img"
aria-label="Calendar"/>
</span>
</div>
</div>
<label class="" for="date_to">End Date :</label>
<div class="input-group date" id="date_to"
data-target-input="nearest">
<input type="text" name="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;">
<span class="input-group-text">
<span class="fa fa-calendar" role="img"
aria-label="Calendar"/>
</span>
</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"
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>
<option value="report_by_product">Report By
Product
</option>
</select>
<span id="report_res"/>
</div>
</div>
<div class="apply_filter">
<button type="button" id="apply_filter"
class="btn btn-primary">
Apply
</button>
</div>
</div>
</div>
</t>
<t t-name="PurchaseOrderTable">
<div t-if="order.report_type == '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-if="order['report_type']='report_by_order'">-->
<t t-foreach="report_lines"
t-as="dynamic_purchase_report">
<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']">
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>
<!--Report for order detail-->
</tbody>
</table>
</div>
</div>
<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>
<tbody>
<!-- <t t-if="order['report_type']='report_by_order'">-->
<t t-foreach="report_lines"
t-as="dynamic_purchase_report">
<tr style="border: 1.5px solid black;"
class="pr-line"
data-toggle="collapse"
t-att-data-account-id="dynamic_purchase_report['id']"
t-attf-data-target=".a{{dynamic_purchase_report['id']}}">
<td style="border: 0px solid black;">
<i class="fa fa-caret-down" role="img"
aria-label="Unfolded" title="Unfolded"/>
<span>
<t t-esc="dynamic_purchase_report['name']"/>
</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['qty']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['amount_total']"/>
</span>
</td>
</tr>
</t>
</tbody>
</table>
</div>
</div>
</t>
</templates>
Here we can see the overview of the purchase dynamic report. Here we have various filter options.
Here we can see the products, and the product details are filtered. Then we have the option to filter date range-wise.
Here there is no data created on the mentioned date. Therefore nothing to display in the report.
Here we can see the data filtered by date range wise.
This is all about how to create a dynamic report and how to fetch data and various filter options in Odoo 16.