In oracle BI report publisher, I need to set querter start and end date as from date and to date par — Oracle Analytics

Oracle Analytics Cloud and Server

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

In oracle BI report publisher, I need to set querter start and end date as from date and to date par

Received Response
222
Views
6
Comments

Summary

In oracle BI report publisher, I need to set querter start and end date as from date and to date parameter.

Content

I am a newbie to Oracle Bi Publisher reports. I have FromDate and ToDate parameters. I need to set it Quarter Start and End Date.

Please help.

What I have tried:

I tried to calculate the querter start end date using functions below but failed.



[Date Parameters in OBIEE]

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    If I understand correctly ... you want the quarter start and end as your defaults and the user can change it or you want the report to automatically run for the most recently closed quarter?

    You won't be able to do it with the Date (Calandar) picker -- I don't think ... BUT using a drop down and list_of_values you can acheive something functional:

    using select trunc(sysdate,'Q') curr_qrtr_strt from dual -- reduces today's date to start of quarter so ...

    select add_months(trunc(sysdate,'Q'),-3) prev_qrtr_strt from dual  <-- 01-APR-2017

    select (trunc(sysdate,'Q')-1) prev_qrtr_end from dual <-- 30-JUN-2017

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    If your data model is a SQL query and would like have the report output alone default to quarter start and end, you can do it in the SQL query (data model) itself. But the default values that  could be set and displayed in parameter section is limited to what is described in the blog you attached.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Calendar picker won't work for you ... in that you can't drive the date from a query; so you can't specify a default of specific quarter start and end dates.

    Best you get is a dynamic range of dates:

    {$SYSDATE()-90$}   to {$SYSDATE()$}

    or

    {$FIRST_DAY_OF_MONTH()-90$}   to {$FIRST_DAY_OF_MONTH()$}

  • Pgoel
    Pgoel Rank 3 - Community Apprentice

    @Thomas Dodds, Thanks for the help. Actually I need quarter start and end date as default value for Date parameter which user can change using the Calendar. I can not go for DDL or List_Of_Values option as this way user will not be able to change dates as per his/her choice.

  • Pgoel
    Pgoel Rank 3 - Community Apprentice

    @Thomas, the second option what I also I was thinking. Although this will not give actual quart date but its somewhat close to it.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Term it a Running Quarter ... and see if they bite!