You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

how to get previous month first_date & last_date in bi publisher?

Received Response
21
Views
9
Comments
Naresh KnK
Naresh KnK Analyst 1 Green Ribbon
edited Mar 9, 2020 5:05PM in Reporting and Analytics for HCM 9 comments
Tagged:

Answers

  • User_BO6Q8
    User_BO6Q8 Silver Medal
    edited Mar 9, 2020 5:17PM

    try this:

    SELECT

    Last_Day(ADD_MONTHS(SYSDATE,-2))+1 First_day,
    Last_Day(ADD_MONTHS(SYSDATE,-1)) Last_day

    FROM DUAL

  • Janardhana
    Janardhana Silver Crown
    edited Mar 10, 2020 12:50PM

    You will try and see what are all the below options possible for defaulting date parameters for BI Report.

    Expression

    Description

    Date Format String

    {$SYSDATE()$}

    Current Date / System Date

    MM-dd-yyyy

    {$SYSDATE()+1$}

    Day next to System date

    MM-dd-yyyy

    {$SYSDATE()-7$}

    7 days prior to System date

    MM-dd-yyyy

    {$FIRST_DAY_OF_MONTH()$}

    First day of Current Month

    MM-dd-yyyy

    {$LAST_DAY_OF_MONTH()$}

    Last day of Current Month

    MM-dd-yyyy

    {$FIRST_DAY_OF_YEAR)$}

    First day of Current Year

    MM-dd-yyyy

    {$LAST_DAY_OF_YEAR)$}

    Last day of Current Year

    MM-dd-yyyy

    {$SYSDATE()$}

    Current Period

    MM

    {$FIRST_DAY_OF_MONTH()-1$}

    Previous Period

    MM

    {$FIRST_DAY_OF_YEAR)$}

    Current Year

    yyyy

    {$FIRST_DAY_OF_YEAR)-1$}

    Previous Year

    yyyy

  • Naresh KnK
    Naresh KnK Analyst 1 Green Ribbon
    edited Mar 10, 2020 1:13PM

    Hi Janardhan,

    When i select this function {$FIRST_DAY_OF_MONTH()-1$},It is picking previous month last date. 

    How to get first day of previous month?

    Thanks,

    Naresh

  • Janardhana
    Janardhana Silver Crown
    edited Mar 10, 2020 1:45PM

    I assume the First Day of Previous Month functions are not supported in BI Date parameters.

    Please fallow the below workaround methods;

    1. First day of the previous month:

    The only option you have is:
    Create an LOV:
    SELECT ADD_MONTHS((LAST_DAY(SYSDATE)+1),-2) FROM DUAL
    and a Menu parameter based on the above LOV.

    The restriction you will have are:
    It returns data in online mode and only if it is setup as CSV format by default
    If you try to schedule the report with other format than CSV, it will fail with “Invalid parameters requested”
    You will not have the option to change the parameter value from default First day of last month to a user selected value from the calendar.

    Regarding the CSV format:
    CSV is a data format.
    Scheduling has to use the exact same parameter values as online.
    The value is static, will be the same for each run, unlike the {$SYSDATE()$} dynamic values.
    This means the only way you can make this work is to create individual scheduled jobs for each month.

     

    2. Last day of the previous month:

    {$FIRST_DAY_OF_MONTH()-1$}

  • Amy  Wilson
    Amy Wilson Sr ERP Business Partner Silver Medal

    Hi all - are there a similar set of functions for OTBI? I am trying to convert a date to the last day of the month it falls in?

  • Yes, this is possible in OTBI, look for TIMESTAMPADD function.

  • Bhaskar Konar
    Bhaskar Konar Data & Analytics Specialist Silver Trophy

    Hi Amy,

    Please find below an example.

    TIMESTAMPADD (SQL_TSI_DAY, DAYOFMONTH (CURRENT_DATE) *-1, CURRENT_DATE)

    Hope this help.

    Cheers,

  • Amy  Wilson
    Amy Wilson Sr ERP Business Partner Silver Medal

    Thank you - we didn't realize we should use that function for all of the dates in OTBI and I really appreciate the help and quick replies!