How to Create a Dynamic Report in Odoo 17

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.

1. How to Create a Dynamic Report in Odoo 17 Enterprise

2. Introduction Enterpr ise 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.

3. Enterprise Step 1 : To initiate this, begin by creating a new menu through an XML file in the view's directory, specifically located at (views/views.xml). In this setup, the "Dynamic Purchase Report" menu is created within the reporting section of the purchase order. <?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>

4. Enterprise <?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>

5. Enterprise Step 2 : 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. Firstly, import the dependencies. /** @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

6. Enterprise 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 } }

7. Enterprise 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), }); }

8. Enterprise 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).

9. Enterprise Step 3 : 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"

10. Enterprise Step 4 : Next, to show the data on the website, we'll use JavaScript. /** @odoo-module */ import PublicWidget from "@web/legacy/js/public/public_widget" ; import { jsonrpc } from "@web/core/network/rpc_service" ; import { renderToElement } from "@web/core/utils/render" ; export function _chunk(array, size) { const result = []; for (let i = ; i < array.length; i += size) { result.push(array.slice(i, i + size)); } return result; } var TopSellingProducts = PublicWidget.Widget.extend({ selector: '.best_seller_product_snippet' , willStart: async function () { const data = await jsonrpc( '/top_selling_products' , {}) const [products, categories, website_id, unique_id] = data Object.assign(this, { products, categories, website_id, unique_id }) }, start: function () { const refEl = this.$el.find( "#top_products_carousel" ) const { products, categories, current_website_id, products_list} = this const chunkData = chunk(products, 4 ) refEl.html(renderToElement( module_name.products_category_wise' , { products, categories, current_website_id, products_list, chunkData })) } }); PublicWidget.registry.products_category_wise_snippet = TopSellingProducts; return TopSellingProducts;

11. Enterprise _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[ ])]) 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

12. Enterprise def get_filter_data (self, option) : """ Function for get filter data in report """ record = self.env[ 'dynamic.purchase.report' ].search([( 'id' , '=' , option[ ])]) 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.

13. Enterprise 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) 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 """

14. Enterprise 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)[ ] 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.

15. Enterprise <?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 : 20 px; ">Purchase Report</h1> </center> </div> </div> <div class=" print -btns "> <div class=" sub_container_left " style=" width: 285 px; margin-left: 36 px; "> <div class=" report_print "> <button type=" button " class=" btn btn-primary " id=" pd f" style=" float: left; margin-right: 9 px; " t-on-click=" printPd f"> 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 "> Now, let's proceed to define the templates, which will be located in static/src/xml.

16. Enterprise <button class=" btn btn-secondary dropdown-toggle time_range_p r" type=" button " id=" date_chose " data-bs-toggle=" dropdown " aria-expanded=" false "> <span class=" fa fa-calenda r" title=" Dates " role=" img " aria-label=" Dates "/> Date Range </button> <div class=" dropdown-menu my_custom_dropdown " role=" men u" 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>

17. Enterprise <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 : 70 vh; padding: 10 px "> <div t-if=" state.order_by == 'report_by_order'"> <div class=" table_main_view "> <table cellspacing=" " width=" 100 % "> <thead> <tr class=" table_pr_head ">

18. Enterprise <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.5 px 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>

19. Enterprise <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=" " 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.5 px solid black; " class=" pr-line " t-att-data-account-id=" dynamic_purchase_report[ 'id' ] " t-attf-data-target=" .a{{dynamic_purchase_report[ 'id' ]}} "> <td>

20. Enterprise <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 > </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>

21. Enterprise 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.

22. For More Info. Check our company website for related blogs and Odoo book. Check our YouTube channel for functional and technical videos in Odoo. Enterprise www.cybrosys.com