Hello Jose

How did you solve sql queries to create powerbi report?

Hi Mayur,

thank you very much for your response.

Could you help me with the following query:

I want to obtain the orders invoiced with the sales lines:

- Invoice number

- Customer

- Article

- sale price

- Cost price (or Margin)

- batch number of the item sold.

I have thought about linking [account_move] with [sale_order] and [sale_order_line], to extract the sales lines, which I have achieved but my question is for this example:

   ++I have an order with 2 sales lines

   ++I have invoiced the two lines separately in 2 invoices

Where do I find the information (field filter) to know that this order line has gone to invoice 1 and the other order line has gone to invoice 2?

many tks

Hi Jose, 

Basic PSQL query is here. 

select * from public.sale_order a LEFT JOIN public.sale_order_line b on a.id = b.order_id where a.name =xxxx (Replace order number);

You may required to explore more tables and join them.

- stock_picking (sale_order.id = stock.picking.sale_id)

- stock_move (stock_picking.id = stock_move.picking_id) (sale_order.name = stock_move.origin)

Stock Move Line table  will provide detailed info of lot number and final quantities with status done.

-stock_move_line (stock_move.id = stock_move_line.move_id) 

- account_move (Invoice information) (journal_id = pick the journal id from your system)

http://localhost:0000/web#cids=2%2C3%2C1&menu_id=114&action=236&model=account.journal&view_type=list

- account_move_line (Invoice lines)

Hi Jose, 

I specialise in creating Power BI reports for Odoo using Techneith's direct connector. If you want to check out my portfolio you can find it at bepbi.com 

All of the reports showcased have been created using the Odoo data model, so the relationships have already been established. Some require joins from over 20 tables. If you need any assistance, I would love to help. If not, I'd definitely recommend checking out Techneith's connector if you want to create the reports yourself.