Solved

Quick Report: Dynamic Rolling 12 Month Columns Based on Starting Date parm

  • 4 February 2021
  • 2 replies
  • 423 views
D
Rank
Userlevel 4
Badge +10
  • DevBob
  • Sidekick (Customer)
  • 119 replies

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)

 

 

 

icon

Best answer by william.klotz 5 February 2021, 02:48

View original
This topic has been closed for comments

2 replies

william.klotz
Rank
Userlevel 7
Badge +21

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

D
Rank
Userlevel 4
Badge +10

That looks awesome, William! Thx!

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

Terms & ConditionsCookie settings