In Odoo 19, dynamic reports offer a flexible and interactive way to analyze business data in depth. Unlike traditional static reports that display fixed information, dynamic reports enable users to customize and explore data in real time by applying filters, selecting specific metrics, and adjusting visual elements. This interactive approach allows users to view information from multiple angles and uncover insights that drive quicker and more informed decisions.
Module structure
purchase_dynamic_report/
+-- __init__.py
+-- __manifest__.py
+-- models/
¦ +-- __init__.py
¦ +-- purchase_report_wizard.py
+-- controllers/
¦ +-- __init__.py
¦ +-- report_controller.py
+-- views/
¦ +-- purchase_report_menu.xml
+-- static/
¦ +-- src/
¦ ¦ +-- js/
¦ ¦ ¦ +-- purchase_report.js
¦ ¦ +-- xml/
¦ ¦ +-- purchase_report_template.xml
+-- security/
+-- ir.model.access.csv
To begin creating a dynamic report in Odoo 19, start by adding a new menu item in an XML file, usually defined in the views directory as purchase_report_menu.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, a new menu item titled "Dynamic Purchase Report" is placed within the Reporting section of the Purchase module.
In this step, a record called "Purchase Report" is defined in ir.actions.client. The tag field is assigned the value "purchase_report", linking it to the widget tag that activates the action when the related menu item is selected. After that, create a JavaScript file inside the static/src/js directory to manage the client-side functionality and interactions of the dynamic report interface.
/** @odoo-module */
const { Component } = owl;
import { onWillStart } from "@odoo/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";
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.actionService = useService("action");
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.reportTypeLabels = {
report_by_order: 'Report By Order',
report_by_order_detail: 'Report By Order Detail',
report_by_product: 'Report By Product',
report_by_categories: 'Report By Categories',
report_by_purchase_representative: 'Report By Purchase Representative',
report_by_state: 'Report By State',
};
onWillStart(async () => {
this.load_data();
});
}
get reportLabel() {
return this.reportTypeLabels[this.state.order_by] || this.state.order_by;
}
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]);
if (Array.isArray(this.state.data)) {
this.state.order_line = this.state.data.map(item => {
return item;
});
} else if (typeof this.state.data === 'object' && this.state.data !== null) {
this.state.order_line = this.state.data.report_lines;
}
}
catch (el) {
window.location.href;
}
}
async onchangeFromDate() {
// Validation for start date
if (this.end_date.el.value && this.start_date.el.value > this.end_date.el.value) {
this.actionService.doAction({
type: 'ir.actions.client',
tag: 'display_notification',
params: {
message: 'End date should be greater than the start date.',
type: 'warning',
sticky: false,
}
});
this.start_date.el.value = ''
this.end_date.el.value = ''
}
}
async onchangeEndDate() {
// Validation for end date
if (this.start_date.el.value && this.start_date.el.value > this.end_date.el.value) {
this.actionService.doAction({
type: 'ir.actions.client',
tag: 'display_notification',
params: {
message: 'End date should be greater than the start date.',
type: 'warning',
sticky: false,
}
});
this.start_date.el.value = ''
this.end_date.el.value = ''
}
}
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
if (data.report_lines && data.report_lines.length > 0) {
const 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),
});
} else {
// Notify the user if there's no data
this.actionService.doAction({
type: 'ir.actions.client',
tag: 'display_notification',
params: {
message: 'No data available to print',
type: 'warning',
sticky: false,
}
});
}
}
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;
var data = this.state.data
if (data.report_lines && data.report_lines.length > 0) {
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': data
},
'context': {
'active_model': 'purchase.report',
'purchase_order_report': true
},
'display_name': 'Purchase Order',
})
} else {
// Notify the user if there's no data
this.actionService.doAction({
type: 'ir.actions.client',
tag: 'display_notification',
params: {
message: 'No data available to print',
type: 'warning',
sticky: false,
}
});
}
}
stripHtml(string) {
if (string)
// Strip tags, html entity and whitespaces
{
return string.replace(/<\/?[^>]+(>|$)/g, "").replace(/"/g, "").replace(/\s+/g, "");
}
else{
return ''
}
}
}
PurchaseReport.template = 'PurchaseReport';
actionRegistry.add("purchase_report", PurchaseReport);
The controller (TBXLSXReportController) handles incoming HTTP POST requests through the /purchase_dynamic_xlsx_reports route. When triggered, it retrieves the appropriate model and executes the get_purchase_xlsx_report function to generate the Excel report. The controller sets the necessary headers for Excel output, creates the response, and returns the generated file to the user for download.
import json
from odoo import http
from odoo.http import content_disposition, request
from odoo.tools import html_escape
class TBXLSXReportController(http.Controller):
""" This endpoint generates and provides an XLSX report in response to an
HTTP POST request. The function uses the provided data to create the
report and returns it as an XLSX file. """
@http.route('/purchase_dynamic_xlsx_reports', type='http', auth='user',
methods=['POST'], csrf=False)
def get_report_xlsx(self, model, options, output_format, report_data,
report_name, dfr_data, **kw):
""" Endpoint for getting xlsx report """
uid = request.session.uid
report_obj = request.env[model].with_user(uid)
dfr_data = dfr_data
options = options
token = 'dummy-because-api-expects-one'
try:
if output_format == 'xlsx':
response = request.make_response(
None,
headers=[
('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition',
content_disposition(report_name + '.xlsx'))
]
)
report_obj.get_purchase_xlsx_report(options, response,
report_data, dfr_data)
response.set_cookie('fileToken', token)
return response
except Exception as e:
se = 0
error = {
'code': 200,
'message': 'Odoo Server Error',
'data': se
}
return request.make_response(html_escape(json.dumps(error)))
The model (DynamicPurchaseReport) manages the logic for fetching and structuring data based on various report types such as By Order, By Product, By Category, By Purchase Representative, and By State. It uses SQL queries to pull data from the database according to filters like date range and report type. The same model also includes the get_purchase_xlsx_report method, which formats the report data into an XLSX file using xlsxwriter. It dynamically structures the Excel sheet with headings, columns, and values that match the selected report type, providing a clean, organized, and fully exportable report view.
import io
import json
import re
from datetime import datetime
from odoo import api, fields, models
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'),
('report_by_product', 'Report By Product'),
('report_by_categories', 'Report By Categories'),
('report_by_purchase_representative',
'Report By Purchase Representative'),
('report_by_state', 'Report By State')], 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'].browse(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'
elif data.get('report_type') == 'report_by_order_detail':
filters['report_type'] = 'Report By Order Detail'
elif data.get('report_type') == 'report_by_product':
filters['report_type'] = 'Report By Product'
elif data.get('report_type') == 'report_by_categories':
filters['report_type'] = 'Report By Categories'
elif data.get('report_type') == 'report_by_purchase_representative':
filters['report_type'] = 'Report By Purchase Representative'
elif data.get('report_type') == 'report_by_state':
filters['report_type'] = 'Report By State'
else:
filters['report_type'] = 'report_by_order'
return filters
def get_filter_data(self, option):
"""Function for get filter data in report"""
record = self.env['dynamic.purchase.report'].browse(option[0])
default_filters = {}
filter_dict = {
'report_type': record.report_type,
}
filter_dict.update(default_filters)
return filter_dict
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.note,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.note,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_order_detail':
query = """ select l.name,l.date_order,l.partner_id,l.amount_total,
l.note, 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)
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
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.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)
elif data.get('report_type') == 'report_by_categories':
query = """ select product_category.name,sum(l.product_qty) as qty,
sum(l.price_subtotal) as amount_total from purchase_order_line as l
left join product_template on l.product_id = product_template.id
left join product_category on product_category.id = product_template.categ_id
left join purchase_order on l.order_id = purchase_order.id
where 1=1 """
if data.get('date_from'):
query += """and pos_order.date_order >= '%s' """ % data.get(
'date_from')
if data.get('date_to'):
query += """ and pos_order.date_order <= '%s' """ % data.get(
'date_to')
query += "group by product_category.name"
self._cr.execute(query)
report_by_categories = self._cr.dictfetchall()
report_sub_lines.append(report_by_categories)
elif data.get('report_type') == 'report_by_purchase_representative':
query = """select res_partner.name,sum(purchase_order_line.product_qty)
as qty,sum(purchase_order_line.price_subtotal) as amount,count(DISTINCT l.id)
as order from purchase_order as l left join res_users on
l.user_id = res_users.id left join res_partner on
res_users.partner_id = res_partner.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 res_partner.name"
self._cr.execute(query)
report_by_purchase_representative = self._cr.dictfetchall()
report_sub_lines.append(report_by_purchase_representative)
elif data.get('report_type') == 'report_by_state':
query = """select l.state,sum(purchase_order_line.product_qty) as
qty,sum(purchase_order_line.price_subtotal) as amount,
count(DISTINCT l.id) as order from purchase_order as l
left join res_users on l.user_id = res_users.id left join
res_partner on res_users.partner_id = res_partner.id
left join purchase_order_line on l.id = purchase_order_line.order_id
where 1=1 """
if data.get('date_from'):
query += """and so.date_order >= '%s' """ % data.get('date_from')
if data.get('date_to'):
query += """ and so.date_order <= '%s' """ % data.get('date_to')
query += "group by l.state"
self._cr.execute(query)
report_by_state = self._cr.dictfetchall()
report_sub_lines.append(report_by_state)
return report_sub_lines
@staticmethod
def strip_html_tags(text):
if not text:
return ''
return re.sub(r'<[^>]*>', '', text)
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)
if isinstance(report_res, list):
for row in report_res:
if 'notes' in row:
row['notes'] = self.strip_html_tags(row['notes'])
elif isinstance(report_res, dict):
if 'notes' in report_res:
report_res['notes'] = self.strip_html_tags(report_res['notes'])
return {
'doc_ids': self.ids,
'docs': docs,
'PURCHASE': report_res,
}
def get_purchase_xlsx_report(self, data, response, report_data, dfr_data):
""" This function generates an XLSX report based on the provided data
and report type. It writes the report data to the response
object for download.
Args:
data (str): JSON data containing report filters.
response (object): The response object used to send the XLSX file.
report_data (str): JSON data containing the report data to be
included in the XLSX.
dfr_data: Data that doesn't appear to be used in this function."""
report_data_main = json.loads(report_data)
output = io.BytesIO()
filters = json.loads(data)
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
head = workbook.add_format({
'align': 'center', 'bold': True, 'font_size': '20px'})
heading = workbook.add_format({'align': 'center', 'bold': True,
'font_size': '10px', 'border': 1, 'border_color': 'black'})
txt_l = workbook.add_format({
'font_size': '10px', 'border': 1, 'bold': True})
txt_normal = workbook.add_format({
'font_size': '10px', 'border': 1})
sheet.merge_range('A2:H3', 'Purchase Report', head)
if filters.get('report_type') == 'report_by_order':
sheet.merge_range('B5:C5', 'Report Type: ' +
'Report By Order', txt_l)
if filters.get('date_from') and filters.get('date_to'):
formatted_date_from = datetime.strptime(
filters['date_from'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
formatted_date_to = datetime.strptime(
filters['date_to'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
sheet.write('B6', 'Start Date:', txt_l)
sheet.write('C6', formatted_date_from, txt_l)
sheet.write('D6', 'End Date:', txt_l)
sheet.write('E6', formatted_date_to, txt_l)
sheet.write('A7', 'Order', heading)
sheet.write('B7', 'Date Order', heading)
sheet.write('C7', 'Customer', heading)
sheet.write('D7', 'Purchase Representative', heading)
sheet.write('E7', 'Total Qty', heading)
sheet.write('F7', 'Amount Total', heading)
lst = []
for rec in report_data_main[0]:
lst.append(rec)
row = 6
col = 0
sheet.set_column(3, 0, 15)
sheet.set_column(4, 1, 15)
sheet.set_column(5, 2, 15)
sheet.set_column(6, 3, 15)
sheet.set_column(7, 4, 15)
sheet.set_column(8, 5, 15)
for rec_data in report_data_main:
row += 1
sheet.write(row, col, rec_data['name'], txt_normal)
sheet.write(row, col + 1, rec_data['date_order'], txt_normal)
sheet.write(row, col + 2, rec_data['partner'], txt_normal)
sheet.write(row, col + 3, rec_data['salesman'], txt_normal)
sheet.write(row, col + 4, rec_data['sum'], txt_normal)
sheet.write(row, col + 5, rec_data['amount_total'], txt_normal)
if filters.get('report_type') == 'report_by_order_detail':
sheet.merge_range('B5:C5', 'Report Type: ' +
'Report By Order Details', txt_l)
if filters.get('date_from') and filters.get('date_to'):
formatted_date_from = datetime.strptime(
filters['date_from'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
formatted_date_to = datetime.strptime(
filters['date_to'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
sheet.write('B6', 'Start Date:', txt_l)
sheet.write('C6', formatted_date_from, txt_l)
sheet.write('D6', 'End Date:', txt_l)
sheet.write('E6', formatted_date_to, txt_l)
sheet.write('A7', 'Order', heading)
sheet.write('B7', 'Date Order', heading)
sheet.write('C7', 'Customer', heading)
sheet.write('D7', 'Purchase Representative', heading)
sheet.write('E7', 'Product Code', heading)
sheet.write('F7', 'Product Name', heading)
sheet.write('G7', 'Price unit', heading)
sheet.write('H7', 'Qty', heading)
sheet.write('I7', 'Price Total', heading)
lst = []
for rec in report_data_main[0]:
lst.append(rec)
row = 6
col = 0
sheet.set_column(3, 0, 15)
sheet.set_column(4, 1, 15)
sheet.set_column(5, 2, 15)
sheet.set_column(6, 3, 15)
sheet.set_column(7, 4, 15)
sheet.set_column(8, 5, 15)
sheet.set_column(9, 6, 15)
sheet.set_column(10, 7, 15)
sheet.set_column(11, 8, 15)
sheet.set_column(12, 9, 15)
for rec_data in report_data_main:
row += 1
sheet.write(row, col, rec_data['name'], txt_normal)
sheet.write(row, col + 1, rec_data['date_order'], txt_normal)
sheet.write(row, col + 2, rec_data['partner'], txt_normal)
sheet.write(row, col + 3, rec_data['salesman'], txt_normal)
sheet.write(row, col + 4, rec_data['default_code'], txt_normal)
sheet.write(row, col + 5, rec_data['product'], txt_normal)
sheet.write(row, col + 6, rec_data['price_unit'], txt_normal)
sheet.write(row, col + 7, rec_data['sum'], txt_normal)
sheet.write(row, col + 8, rec_data['amount_total'], txt_normal)
if filters.get('report_type') == 'report_by_product':
sheet.merge_range('B5:C5', 'Report Type: ' +
'Report By Product', txt_l)
if filters.get('date_from') and filters.get('date_to'):
formatted_date_from = datetime.strptime(
filters['date_from'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
formatted_date_to = datetime.strptime(
filters['date_to'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
sheet.write('B6', 'Start Date:', txt_l)
sheet.write('C6', formatted_date_from, txt_l)
sheet.write('D6', 'End Date:', txt_l)
sheet.write('E6', formatted_date_to, txt_l)
sheet.write('A7', 'Category', heading)
sheet.write('B7', 'Product Code', heading)
sheet.write('C7', 'Product Name', heading)
sheet.write('D7', 'Qty', heading)
sheet.write('E7', 'Amount Total', heading)
lst = []
for rec in report_data_main[0]:
lst.append(rec)
row = 6
col = 0
sheet.set_column(3, 0, 15)
sheet.set_column(4, 1, 15)
sheet.set_column(5, 2, 15)
sheet.set_column(6, 3, 15)
sheet.set_column(7, 4, 15)
for rec_data in report_data_main:
row += 1
sheet.write(row, col, rec_data['name'], txt_normal)
sheet.write(row, col + 1, rec_data['default_code'], txt_normal)
sheet.write(row, col + 2, rec_data['product'], txt_normal)
sheet.write(row, col + 3, rec_data['qty'], txt_normal)
sheet.write(row, col + 4, rec_data['amount_total'], txt_normal)
if filters.get('report_type') == 'report_by_categories':
sheet.merge_range('B5:C5', 'Report Type: ' +
'Report By Categories', txt_normal)
if filters.get('date_from') and filters.get('date_to'):
formatted_date_from = datetime.strptime(
filters['date_from'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
formatted_date_to = datetime.strptime(
filters['date_to'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
sheet.write('B6', 'Start Date:', txt_l)
sheet.write('C6', formatted_date_from, txt_l)
sheet.write('D6', 'End Date:', txt_l)
sheet.write('E6', formatted_date_to, txt_l)
sheet.write('B7', 'Category', heading)
sheet.write('C7', 'Qty', heading)
sheet.write('D7', 'Amount Total', heading)
lst = []
for rec in report_data_main[0]:
lst.append(rec)
row = 6
col = 1
sheet.set_column(3, 1, 15)
sheet.set_column(4, 2, 15)
sheet.set_column(5, 3, 15)
for rec_data in report_data_main:
row += 1
sheet.write(row, col, rec_data['name'], txt_normal)
sheet.write(row, col + 1, rec_data['qty'], txt_normal)
sheet.write(row, col + 2, rec_data['amount_total'], txt_normal)
if filters.get('report_type') == 'report_by_purchase_representative':
sheet.merge_range('B5:C5', 'Report Type: ' +
'Report By Purchase Representative', txt_l)
if filters.get('date_from') and filters.get('date_to'):
formatted_date_from = datetime.strptime(
filters['date_from'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
formatted_date_to = datetime.strptime(
filters['date_to'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
sheet.write('B6', 'Start Date:', txt_l)
sheet.write('C6', formatted_date_from, txt_l)
sheet.write('D6', 'End Date:', txt_l)
sheet.write('E6', formatted_date_to, txt_l)
sheet.write('A7', 'Purchase Representative', heading)
sheet.write('B7', 'Total Order', heading)
sheet.write('C7', 'Total Qty', heading)
sheet.write('D7', 'Total Amount', heading)
lst = []
for rec in report_data_main[0]:
lst.append(rec)
row = 6
col = 0
sheet.set_column(3, 0, 15)
sheet.set_column(4, 1, 15)
sheet.set_column(5, 2, 15)
sheet.set_column(6, 3, 15)
for rec_data in report_data_main:
row += 1
sheet.write(row, col, rec_data['name'], txt_normal)
sheet.write(row, col + 1, rec_data['order'], txt_normal)
sheet.write(row, col + 2, rec_data['qty'], txt_normal)
sheet.write(row, col + 3, rec_data['amount'], txt_normal)
if filters.get('report_type') == 'report_by_state':
sheet.merge_range('B5:C5', 'Report Type: ' +
'Report By State', txt_l)
if filters.get('date_from') and filters.get('date_to'):
formatted_date_from = datetime.strptime(
filters['date_from'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
formatted_date_to = datetime.strptime(
filters['date_to'], '%Y-%m-%d %H:%M:%S').strftime(
'%d/%m/%y')
sheet.write('B6', 'Start Date:', txt_l)
sheet.write('C6', formatted_date_from, txt_l)
sheet.write('D6', 'End Date:', txt_l)
sheet.write('E6', formatted_date_to, txt_l)
sheet.write('A7', 'State', heading)
sheet.write('B7', 'Total Count', heading)
sheet.write('C7', 'Quantity', heading)
sheet.write('D7', 'Amount', heading)
lst = []
for rec in report_data_main[0]:
lst.append(rec)
row = 6
col = 0
sheet.set_column(3, 0, 15)
sheet.set_column(4, 1, 15)
sheet.set_column(5, 2, 15)
sheet.set_column(6, 3, 15)
for rec_data in report_data_main:
row += 1
if rec_data['state'] == 'draft':
sheet.write(row, col, 'RFQ', txt_normal)
elif rec_data['state'] == 'sent':
sheet.write(row, col, 'RFQ Sent', txt_normal)
elif rec_data['state'] == 'purchase':
sheet.write(row, col, 'Purchase Order', txt_normal)
sheet.write(row, col + 1, rec_data['order'], txt_normal)
sheet.write(row, col + 2, rec_data['qty'], txt_normal)
sheet.write(row, col + 3, rec_data['amount'], txt_normal)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
Once the RPC call completes, it wraps the fetched list and sends the data to the QWeb template for rendering. The next step involves creating the template files inside the static/src/xml directory. These templates define the structure and visual layout of the report within the user interface, including the filter section, report table, and interactive buttons for actions such as applying filters, printing, or exporting data.
<?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" t-on-change="onchangeFromDate">
<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" t-on-change="onchangeEndDate">
<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>
<option value="report_by_product">Report By
Product
</option>
<option value="report_by_categories">Report By
Categories
</option>
<option value="report_by_purchase_representative">
Report By Purchase Representative
</option>
<option value="report_by_state">Report By
State
</option>
</select>
<span id="report_res" t-out="reportLabel"/>
</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="stripHtml(dynamic_purchase_report['note'])"/>
</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['price_subtotal']"/>
</span>
</td>
</tr>
</t>
</tbody>
</table>
</div>
</div>
<!-- Report for product -->
<div t-if="state.order_by == '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-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>
<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>
<!-- Report for Categories -->
<div t-if="state.order_by == 'report_by_categories'">
<div class="table_main_view">
<table cellspacing="0" width="100%">
<thead>
<tr class="table_pr_head">
<th>Category</th>
<th class="mon_fld">Qty</th>
<th class="mon_fld">Amount Total</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 style="border: 0px solid black;">
<t t-esc="dynamic_purchase_report['name']"/>
</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>
<!-- Report for purchase_representative -->
<div t-if="state.order_by == 'report_by_purchase_representative'">
<div class="table_main_view">
<table cellspacing="0" width="100%">
<thead>
<tr class="table_pr_head">
<th>Purchase Representative</th>
<th class="mon_fld">Total Order</th>
<th class="mon_fld">Total Qty</th>
<th class="mon_fld">Total Amount</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"
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;">
<span>
<t t-esc="dynamic_purchase_report['name']"/>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['order']"/>
</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']"/>
</span>
</td>
</tr>
</t>
</tbody>
</table>
</div>
</div>
<!-- Report for State -->
<div t-if="state.order_by == 'report_by_state'">
<div class="table_main_view">
<table cellspacing="0" width="100%">
<thead>
<tr class="table_pr_head">
<th>State</th>
<th class="mon_fld">Total Count</th>
<th class="mon_fld">Quantity</th>
<th class="mon_fld">Amount</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"
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;">
<span>
<t t-if="dynamic_purchase_report['state'] == 'sent'">
RFQ Sent
</t>
<t t-if="dynamic_purchase_report['state'] == 'purchase'">
Purchase Order
</t>
<t t-if="dynamic_purchase_report['state'] == 'draft'">
RFQ
</t>
</span>
</td>
<td style="text-align:center;">
<span>
<t t-esc="dynamic_purchase_report['order']"/>
</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']"/>
</span>
</td>
</tr>
</t>
</tbody>
</table>
</div>
</div>
</div>
</t>
</templates>
Below is the Dynamic Purchase Report interface includes several filtering options that allow users to narrow down and analyze the displayed information effectively.
This view presents the products along with their filtered details and allows users to refine the data using a date range filter. In Odoo 19, dynamic reporting takes flexibility and interactivity even further, empowering businesses to gain deeper insights and make data-driven decisions through enhanced customization and real-time analysis.
To read more about How to Create a Dynamic Report in Odoo 18, refer to our blog How to Create a Dynamic Report in Odoo 18.