Report snippets

allow an arbitrary date range

iDempiere by default contains reports e.g. to sum up quarterly or monthly sales of product. This is because you cannot create a view using arbitrary date ranges without having a table containing exactly this date range. But there is help: when running a report the table AD_Pinstance_Para contains this date range.

To use this, the name of the view has to start with T_ and it has to contain a column AD_Pinstance_ID. Here is an example (a bit shortened):

 create or replace
view T_invoice_product_date_range
as
select
	app.ad_pinstance_id,
	app.p_date,
	app.p_date_to,
	il.ad_client_id,
	il.ad_org_id,
	il.m_product_id,
	min(il.dateinvoiced) firstsold,
	max(il.dateinvoiced) lastsold,
	sum(il.linenetamt) as linenetamt,
	sum(il.linelistamt) as linelistamt,
	sum(il.linelimitamt) as linelimitamt,
	sum(il.linediscountamt) as linediscountamt,	
	il.issotrx,
from
	ad_pinstance_para app 
join rv_c_invoiceline il on
	app.p_date <= il.dateinvoiced
	and app.p_date_to >= il.dateinvoiced
join c_invoice i on
	i.c_invoice_id = il.c_invoice_id
where i.docstatus = 'CO'
group by
	il.ad_client_id,
	il.ad_org_id,
	il.m_product_id,
	il.issotrx,
	i.c_currency_id,
	app.ad_pinstance_id,
	app.p_date,
	app.p_date_to