Date parameter default date as financial quarter start date

Question

Summary

Content

Hello,

I am working on BI publisher reports where I have two date parameter, start  date and end date . I want default date as financial quarter start date and end date. How Can i set that .

I only know SYSDATE(),FIRST_DAY_OF_MONTH(),LAST_DAY_OF_MONTH(),FIRST_DAY_OF_YEAR() and LAST_DAY_OF_YEAR() function.

Then how can I set as financial quarter start date and end date

Brajesh Shukla-95078 · Answer

If your data is like

You can write below sql

SELECT QUARTER_NUM,

MIN(START_DATE),

MAX(END_DATE)

FROM <Period_table>

WHERE PERIOD_NAME LIKE '%17'

AND application_id = 200

AND ledger_id      = 2021

GROUP BY QUARTER_NUM

order by QUARTER_NUM;

Alternatively if you want to get start date and end date of current quarter, you can add more where clause  as below

SELECT QUARTER_NUM,

MIN(START_DATE),

MAX(END_DATE)

FROM gl_period_statuses gps1

WHERE PERIOD_NAME LIKE '%17'

AND application_id = 200

AND QUARTER_NUM   IN

(SELECT QUARTER_NUM

FROM gl_period_statuses gps2

WHERE sysdate BETWEEN START_DATE AND END_DATE

AND gps2.application_id =  gps1.application_id

AND gps2.ledger_id =  gps1.ledger_id

)

AND ledger_id = 2021

GROUP BY QUARTER_NUM

ORDER BY QUARTER_NUM;

Output will be

or below from second sql

Brajesh Shukla-95078 · Answer

This is possible in database side only.....

From your period table you can select the Min(Start_date) and max(end_date) group by each quarter.

Nmahajan · Answer

Correct me if understood correctly, in order to set start and end date parameter default date to financial quarter start and end date, only way is to get current quarter start date and end date from database as different dataset.

Then bind that dataset of start and end date parameter default date.