Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Date parameter default date as financial quarter start date

Received Response
237
Views
4
Comments

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

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    If your data is like

    image

    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

    image

    or below from second sql

    image

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    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
    Nmahajan Rank 2 - Community Beginner

    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. 

  • Nmahajan
    Nmahajan Rank 2 - Community Beginner

    You mean to create a new data set and calculate financial start date and end date from database ?