Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Date parameter default date as financial quarter start date

Summary
Date parameter default date as financial quarter start date
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
Answers
-
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
0 -
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.
0 -
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.
0 -
You mean to create a new data set and calculate financial start date and end date from database ?
0