Home » Developer & Programmer » Reports & Discoverer » Year to Date Totals (Oracle Discoverer 10g)
Year to Date Totals [message #404431] Thu, 21 May 2009 15:46
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Hello
I have the following query which gives me the Organization Name, Supplier name, Invoice Amount for Year to date, Total Invoice Amount for current Month and Total Invoice Amount for Previous Month.

This works fine if I am to run this query as is.
But what I need to do is, substitute the Invoice Date as a parameter. I will setup a parameter for Month and Year.
If the user selects Mar 2009, then my query should output results for YTD till Mar, Sum(Invoice Amount) for MARCH and Sum(Invoice_AmounT) for February i.e Previous Month.

If I am to add the invoice date, then I would have to add it to the group by clause which will give incorrect results.

Can someone guide me on how to do this ?
SELECT   name, 
         remit_to_supplier_name, 
         (SELECT Sum(invoice_amount) sum_year 
          FROM   ap_invoices_all c, 
                 hr_operating_units d 
          WHERE  c.legal_entity_id = d.organization_id 
                 AND c.remit_to_supplier_name = a.remit_to_supplier_name 
                 AND c.invoice_date BETWEEN Trunc(SYSDATE,'YYYY') AND SYSDATE 
                 AND c.legal_entity_id = a.legal_entity_id) YEAR, 
         (SELECT Sum(invoice_amount) 
          FROM   ap_invoices_all e, 
                 hr_operating_units f 
          WHERE  e.legal_entity_id = f.organization_id 
                 AND e.legal_entity_id = a.legal_entity_id 
                 AND e.remit_to_supplier_name = a.remit_to_supplier_name 
                 AND e.invoice_date BETWEEN Trunc(SYSDATE,'Month') AND SYSDATE) curr_month, 
         (SELECT Sum(invoice_amount) 
          FROM   ap_invoices_all e, 
                 hr_operating_units f 
          WHERE  e.legal_entity_id = f.organization_id 
                 AND e.legal_entity_id = a.legal_entity_id 
                 AND e.remit_to_supplier_name = a.remit_to_supplier_name 
                 AND e.invoice_date BETWEEN (Trunc(Trunc(SYSDATE,'MM') - 1,'MM')) AND Last_day(Trunc(Trunc(SYSDATE,'MM') - 1,'MM'))) prev_month 
FROM     ap_invoices_all a, 
         hr_operating_units b 
WHERE    a.legal_entity_id = b.organization_id 
         AND a.legal_entity_id = 332 
GROUP BY NAME, 
         remit_to_supplier_name, 
         legal_entity_id 

Example of output:

TEST Corporation,XYZ Company,2098514.05,152000,152000

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 21 May 2009 16:00] by Moderator

Report message to a moderator

Previous Topic: hide runtime parameter form
Next Topic: Oracle Report File in Cache Directory
Goto Forum:
  


Current Time: Thu Dec 08 20:35:49 CST 2016

Total time taken to generate the page: 0.13528 seconds