https://wiki.idempiere.org/w-en/index.php?action=history&feed=atom&title=Report_snippets 2026-05-04T10:04:44Z Revision history for this page on the wiki MediaWiki 1.35.2 https://wiki.idempiere.org/w-en/index.php?title=Report_snippets&diff=21826&oldid=prev 2023-11-09T22:12:30Z

<p><span dir="auto"><span class="autocomment">allow an arbitrary date range</span></span></p> <table class="diff diff-contentalign-left diff-editfont-monospace" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="en"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 22:12, 9 November 2023</td> </tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l17" >Line 17:</td> <td colspan="2" class="diff-lineno">Line 17:</td></tr> <tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> il.ad_org_id,</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> il.ad_org_id,</div></td></tr> <tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> il.m_product_id,</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> il.m_product_id,</div></td></tr> <tr><td class='diff-marker'>−</td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"> mp.m_product_category_id,</del></div></td><td colspan="2"> </td></tr> <tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> min(il.dateinvoiced) firstsold,</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> min(il.dateinvoiced) firstsold,</div></td></tr> <tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> max(il.dateinvoiced) lastsold,</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> max(il.dateinvoiced) lastsold,</div></td></tr> <tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l32" >Line 32:</td> <td colspan="2" class="diff-lineno">Line 31:</td></tr> <tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>join c_invoice i on</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>join c_invoice i on</div></td></tr> <tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> i.c_invoice_id = il.c_invoice_id</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> i.c_invoice_id = il.c_invoice_id</div></td></tr> <tr><td class='diff-marker'>−</td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;">join m_product mp on mp.m_product_id = il.m_product_id </del></div></td><td colspan="2"> </td></tr> <tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>where i.docstatus = 'CO'</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>where i.docstatus = 'CO'</div></td></tr> <tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>group by</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>group by</div></td></tr> </table>

Schnbeck https://wiki.idempiere.org/w-en/index.php?title=Report_snippets&diff=21825&oldid=prev 2023-11-09T22:10:34Z

<p>Created page with &quot;__TOC__ = 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 yo...&quot;</p> <p><b>New page</b></p><div>__TOC__<br /> <br /> = Report snippets =<br /> == allow an arbitrary date range ==<br /> 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. <br /> <br /> 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):<br /> &lt;syntaxhighlight lang=&quot;sql&quot;&gt;<br /> create or replace<br /> view T_invoice_product_date_range<br /> as<br /> select<br /> app.ad_pinstance_id,<br /> app.p_date,<br /> app.p_date_to,<br /> il.ad_client_id,<br /> il.ad_org_id,<br /> il.m_product_id,<br /> mp.m_product_category_id,<br /> min(il.dateinvoiced) firstsold,<br /> max(il.dateinvoiced) lastsold,<br /> sum(il.linenetamt) as linenetamt,<br /> sum(il.linelistamt) as linelistamt,<br /> sum(il.linelimitamt) as linelimitamt,<br /> sum(il.linediscountamt) as linediscountamt, <br /> il.issotrx,<br /> from<br /> ad_pinstance_para app <br /> join rv_c_invoiceline il on<br /> app.p_date &lt;= il.dateinvoiced<br /> and app.p_date_to &gt;= il.dateinvoiced<br /> join c_invoice i on<br /> i.c_invoice_id = il.c_invoice_id<br /> join m_product mp on mp.m_product_id = il.m_product_id <br /> where i.docstatus = 'CO'<br /> group by<br /> il.ad_client_id,<br /> il.ad_org_id,<br /> il.m_product_id,<br /> il.issotrx,<br /> i.c_currency_id,<br /> app.ad_pinstance_id,<br /> app.p_date,<br /> app.p_date_to <br /> &lt;/syntaxhighlight&gt;<br /> <br /> [[Category:Development]]<br /> [[Category:Code snippets]]</div>

Schnbeck