how to get previous month first_date & last_date in bi publisher? — Cloud Customer Connect
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?

User_2025-02-05-14-11-28-072
User_2025-02-05-14-11-28-072 Green Ribbon
edited Mar 9, 2020 5:05PM in Reporting and Analytics for HCM 32 comments
Tagged:
«1

Answers

  • User_2025-01-28-05-11-13-165
    User_2025-01-28-05-11-13-165 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 K
    Janardhana K 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
  • User_2025-02-05-14-11-28-072
    User_2025-02-05-14-11-28-072 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 K
    Janardhana K 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 Silver Trophy

    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?

  • Julian Challenger-Oracle
    Julian Challenger-Oracle Jadeite

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

  • Amy Wilson
    Amy Wilson Silver Trophy

    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!

  • Sumedh Kumar-Oracle
    Sumedh Kumar-Oracle Green Ribbon

    hi All,


    I need the first day of the last month in the OBTI parameter, can anyone help me please?

  • Anna Kryszak
    Anna Kryszak Silver Trophy

    Hi @Sumedh Kumar-Oracle,

    I have used the below which I found online:

    First day of last month

    SELECT TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

    FROM "Workforce Management - Historical Processed Time Cards Real Time"


    Last day of last month

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

    FROM "Workforce Management - Historical Processed Time Cards Real Time"

    Please substitute the subject area name to the one you are using. When I added the above as prompts for the between dates and ran it today (2nd April) I can see the following as expected:

    image.png


  • MaartenDIF
    MaartenDIF Gold Trophy

    Is there any way to get the first day of the previous quarter?

  • Mark Stoker
    Mark Stoker Gold Medal

    Hi @MaartenDIF,

    The below should give you first day of previous quarter.

    TIMESTAMPADD( SQL_TSI_QUARTER , -1, TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_QUARTER FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

    Thanks

    Mark

  • MaartenDIF
    MaartenDIF Gold Trophy

    @Mark Stoker It is giving me an error (Invalid date format). I think this is OTBI format, not BIP.

    I've tried a lot of different variations and I cannot find any working code for the 'EXTRACT(DAY_OF_QUARTER…' part. Also, BIP only accepts SYSDATE() and not CURRENT_DATE. TRUNC(SYSDATE(), 'Q') also not working.. I have no idea how to resolve this.

  • Mark Stoker
    Mark Stoker Gold Medal

    Hi @MaartenDIF,

    Can you see if the below works?

    Select ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) AS First from Dual

    Thanks

    Mark

  • Satyam Roy
    Satyam Roy Gold Trophy
    edited Jul 12, 2024 5:38AM

    Thanks for the information all.

  • MaartenDIF
    MaartenDIF Gold Trophy

    @Mark Stoker

    This works well when putting it directly in the data model. However, the BIP parameters are a different beast:

    image.png

    So far I've only got specific system parameters subtraction/addition to work, so no functions (like add_months).
    There's some other data parameters that work aside from the SYSDATE. @Janardhana K linked them earlier in this thread.

    If there's no other workaround I will need to duplicate the DataModel and hard-code this parameter for the schedule. I'd rather not, because it will increase maintenance whenever there's changes to this report (and that happens quite often).

  • Hua_Wang
    Hua_Wang Green Ribbon

    Hi, MaartenDIF

    Have you found better way?

    We are facing similar issues right now as

    I need to give dynamic value as first date of last month since user only wants to see actual shipment out last month on report run date.

    And that report will be scheduled to run once per month but even that I do not want to schedule it 12 times either.

  • Ileana B.
    Ileana B. Red Ribbon

    I managed to default the date parameter in the Data Model to the first day of month for 6 months back like this:

    • the date format string is yyyy-MM
    • the default expression is either {$sysdate()-180$} or {$first_day_of_month()-180$} (they both work)

    Hope this helps.

  • Gemma Alvey
    Gemma Alvey

    Hi there, was there any response to @MaartenDIF and @Hua_Wang last messages? I'm having the same issues!

  • MaartenDIF
    MaartenDIF Gold Trophy

    I have not resolved my requirement yet. I did find out recently that you can also default OTBI prompts using the following: @{system.currentTime}['yyyy-MM-dd']

    That might give some extra options, but doing standard subtractions may cause it to fail when going from December to Jan and looking for the previous month..

  • Diane G123
    Diane G123 Red Ribbon
    edited Mar 13, 2025 11:19AM

    I'm looking for default date parameters in a BIP report for the first and last day of the previous year, is this possible? The world won't actually end if I have to run it manually and pick the dates but if I can default them and save some brain power…

  • Z-Man-Oracle
    Z-Man-Oracle Green Ribbon

    Hi Diane
    See if you can utilize this code for your issue

    SELECT sysdate "Today"
    ,sysdate - 365 "A year ago today"
    ,to_char(trunc(sysdate, 'mm'), 'MM-DD-YYYY') "First of this month"
    ,to_char(trunc(LAST_DAY(trunc(sysdate, 'mm'))), 'MM-DD-YYYY') "Last of this month"
    ,to_char(trunc(sysdate - 365, 'mm'), 'MM-DD-YYYY') "First of this month last year"
    ,to_char(trunc(LAST_DAY(trunc(sysdate - 365, 'mm'))), 'MM-DD-YYYY') "Last of this month last year"
    FROM dual

  • Diane G123
    Diane G123 Red Ribbon
    edited Mar 13, 2025 2:02PM

    Thanks I'm looking for the code for a default for a parameter in a BIP report.

    DD: First day of the current year

    {$FIRST_DAY_OF_YEAR()$}

    ie 1/1/2024 and 31/12/2024. The above gives me 1/1/2025. It may not be possible but worth asking. It makes my head hurt that it isn't just standard code..

  • Diane G123
    Diane G123 Red Ribbon

    {$FIRST_DAY_OF_YEAR()-366$} will be right 75% of the time, it will do

  • Z-Man-Oracle
    Z-Man-Oracle Green Ribbon

    Check out this link
    https://docs.oracle.com/middleware/12212/bip/BIPUG/GUID-BC0A8B60-1BB5-4D85-85BB-B199C1A5C418.htm#BIPUG219

    • {$SYSDATE()$} - current date, the system date of the server on which BI Publisher is running.
    • {$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

    You can also enter expressions using the plus sign (+) and minus sign (-) to add or subtract days as follows:

    • {$SYSDATE()+1$}
    • {$SYSDATE()-7$}

    For this example, to capture data from the previous week, each time the schedule runs, enter the following in the report's date parameter fields:

    • Date From: {$SYSDATE()-7$}
    • Date To: {$SYSDATE()-1$}

    Note:

    You can set up the date functions as default parameter values in the data model. In this case, every time a user views the report from the report viewer, the date parameter is calculated according to the expression supplied for the default value. See Adding Parameters and Lists of Values in Data Modeling Guide for Oracle Business Intelligence Publisher.

  • Mark Stoker
    Mark Stoker Gold Medal

    Hi @Diane G123, if the date will always be defaulted and will never allow the user to change this date, then perhaps create a list of values with a sql query that will bring back the data you require and reference this in the parameter.

  • Jahnvee Sharma
    Jahnvee Sharma Green Ribbon

    Hi All,

    We are using the {$FIRST_DAY_OF_MONTH()$} and {$LAST_DAY_OF_MONTH()$} functions while scheduling a report. However, we are encountering an issue with the time zone. The report is supposed to run at 11:55 PM EST on the last day of the month. While we’ve selected the correct time zone in the schedule, the dates being passed are from the next month. This is because, by the time the report runs, it’s already the next day in UTC.

    We’ve verified that the preferences are set up correctly. Does anyone have suggestions on how to handle this while scheduling? We're aiming to avoid making changes in the Data Model.

    Thanks

  • Mark Stoker
    Mark Stoker Gold Medal

    Hi @Jahnvee Sharma,

    Not sure if this is exactly what you require but this document may help you in some context.

    Oracle Fusion OTBI: Why are the Dates Off By one Day for some users and not for others (Doc ID 2097586.1)

    Thanks

    Mark

  • Jahnvee Sharma
    Jahnvee Sharma Green Ribbon

    Hi @Mark Stoker ,

    Thanks for the response. But we are facing this issue while scheduling a BIP report.

    Thanks