Skip to main content

We currently have a quick report that has 12 monthly columns, one for each month:

 

I’ve hard-coded each of the 12 months as noted below. Now they would like the months to dynamically start based on the selected Starting Date, so if they enter 5/1/2020 it’ll load the 12 months starting with June-

JUN  JUL  AUG  SEPT  NOV  DEC  JAN  FEB  MAR  APR   MAY

is that possible with quick report SQL?

(the RW_FORECAST_BY_MONTH is an IAL that contains the extracted Month column and is ordered by Date; the quick report’s SQL groups it into the months)

 

 

 

Hi @DevBob ,

 

Take a look at this query and output to see if it’s close to what you are looking to do.

SELECT TO_CHAR(coh.invoice_date,'YYYY') AS "YEAR",
       TO_CHAR(coh.invoice_date,'MM') AS "MONTH",
       SUM(coh.net_amount) AS "NET_AMOUNT"
FROM IFSAPP.CUSTOMER_ORDER_INV_HEAD_UIV coh
WHERE UPPER(coh.company) = UPPER('&COMPANY')
  AND UPPER(coh.series_id) in ('CD','CR')
  AND coh.INVOICE_DATE BETWEEN TO_DATE('&FROM_DATE','mm/dd/yyyy') AND ADD_MONTHS ( TO_DATE('&FROM_DATE','mm/dd/yyyy'), 12)
GROUP BY TO_CHAR(coh.invoice_date,'YYYY'), TO_CHAR(coh.invoice_date,'MM')
ORDER BY TO_CHAR(coh.invoice_date,'YYYY'),TO_CHAR(coh.invoice_date,'MM')

The above query would produce the output below if you were to enter a starting date of 10/1/2018.

 

Regards,

William Klotz

That looks awesome, William! Thx!

We’ll be able to use this and adapt it to our needs.

Terms & ConditionsCookie settings