Capability to create a sales report based on rental_start and rental_end dates in Sales Order Lines?

[kwoychesko]

Hey gang, struggling with how to create a sales report: the sales order lines have additional date fields for rental_start and rental_end. I'm trying to create a sales report weighted based on these dates.
For example: a rental starts May 15 and ends June 5, and the price_subtotal is $1,000. I am trying to create a sales report that shows the current month's rental sales amount. In this example, for May it should show $762 (the weighted amount of $1000 contributed by the May dates). I have another field (rental_days) that will help get the daily value of the rental per day (i.e. price_subtotal / rental_days), but not sure how to proceed.
Any thoughts? Thanks!

[HaraldPanten]

I think that you should use queries for mapping this model and fields.

[kwoychesko]

@HaraldPanten Can I create complex SQL queries within the Queries tab of Report Templates? Or do you mean I should use bi_sql_editor or a custom view like in the mis-builder-demo? Thank you for your response!

[sbidoul]

In the Queries tab, you can define a domain on an Odoo model. If you need joins and such you need to bring it into a model, either with a custom module or something like bi_sql_editor indeed.

[kwoychesko]

Hi @sbidoul thank you very much for your suggestion. I think I will need joins, etc. and am going to try bi_sql_editor. Thank you again!