Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Trying to pass first day of previous month dynamically

Received Response
485
Views
7
Comments
BSM
BSM Rank 3 - Community Apprentice
in Oracle Analytics Cloud and Server

Summary

Trying to pass first day of previous month dynamically

Content

Hello BIP experts,

I need to create dynamic date parameters From: and To:

Last day of previous month: {$FIRST_DAY_OF_MONTH()-1$}

Can you please tell me how can i achieve From: First day of previous Month  ?

Thanks a lot for your help.

P.S: I did check all the previous discussions based on this topic, but didnt find a resolution. Can you please help?

Thanks,

Pravin

Answers

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Do not think it is possible.

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

    LAST_DAY(ADD_MONTHS(sysdate,-2))+1

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    What is the data model of your publisher report, is it a SQL query ? If it is , I think then you can take care of the date filter value in SQL itself.

  • BSM
    BSM Rank 3 - Community Apprentice

    Thanks for the input.

    I am trying to schedule a BIP report which runs in the background and fetches the data from previous month. I am able to get the Last day of previous month using the value {$FIRST_DAY_OF_MONTH()-1$}. But im unable to achieve getting the first day of previous month.

    BI Publisher supports not only the current date (sysdate) function but also other types of functions for commonly used dates.

    • SYSDATE()   - Today’s date
    • FIRST_DAY_OF_MONTH() - First day of the current month
    • LAST_DAY_OF_MONTH()  - Last day of the current month
    • FIRST_DAY_OF_YEAR()  - First day of the current year
    • LAST_DAY_OF_YEAR()   - Last day of the current year
  • Daniel Willis-92031
    Daniel Willis-92031 Rank 3 - Community Apprentice

    Do want the user to pick a month and then your query runs for the last day of the previous month?

    I only use BIP within OBIEE so might not be on the right track but in my scenario I could have a prompt/parameter for the value and then use it in the query

    e.g. MyDateColumn < LAST_DAY(ADD_MONTHS(:PROMPTDATE,-1)) to get last day of prior month

  • BSM
    BSM Rank 3 - Community Apprentice

    I wish there is a way

  • Nivya Mathire
    Nivya Mathire Rank 3 - Community Apprentice

    SELECT  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)  FIRST_DAY ,LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)) LAST_DAY from dual