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

Answers
-
try this:
SELECT
Last_Day(ADD_MONTHS(SYSDATE,-2))+1 First_day,
Last_Day(ADD_MONTHS(SYSDATE,-1)) Last_dayFROM DUAL
1 -
Naresh - Justin provided SQL coding, alternatively I know there are fields you can use in parameters (I added Justin's code to my list, so you'll see it there too, not sure if it can be used in parameters yet, haven't tested it):
Name Formula or Field Name DD: 7 days ago {$SYSDATE()-7$} DD: Data from the previous week Date From: {$SYSDATE()-7$}
Date To: {$SYSDATE()-1$}DD: First day of the current month {$FIRST_DAY_OF_MONTH()$} DD: First day of the current year {$FIRST_DAY_OF_YEAR()$} DD: First of the previous month SELECT
Last_Day(ADD_MONTHS(SYSDATE,-2))+1 First_day
FROM DUALDD: Last day of the previous month SELECT
Last_Day(ADD_MONTHS(SYSDATE,-1)) Last_day
FROM DUALDD: Last day of the current month {$LAST_DAY_OF_MONTH()$} DD: Last day of the current year {$LAST_DAY_OF_YEAR()$} DD: Today {$SYSDATE()$} DD: Tomorrow {$SYSDATE()+1$} 9 -
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
2 -
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
0 -
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$}
1 -
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?
1 -
Yes, this is possible in OTBI, look for TIMESTAMPADD function.
1 -
Hi Amy,
Please find below an example.
TIMESTAMPADD (SQL_TSI_DAY, DAYOFMONTH (CURRENT_DATE) *-1, CURRENT_DATE)
Hope this help.
Cheers,
5 -
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!
0 -
hi All,
I need the first day of the last month in the OBTI parameter, can anyone help me please?
0 -
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:
1 -
Is there any way to get the first day of the previous quarter?
0 -
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
0 -
@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.
0 -
Hi @MaartenDIF,
Can you see if the below works?
Select ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) AS First from Dual
Thanks
Mark
0 -
Thanks for the information all.
0 -
This works well when putting it directly in the data model. However, the BIP parameters are a different beast:
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).
1 -
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.
0 -
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.
2 -
Hi there, was there any response to @MaartenDIF and @Hua_Wang last messages? I'm having the same issues!
0 -
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..
0 -
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…
0 -
Hi Diane
See if you can utilize this code for your issueSELECT 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 dual0 -
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..
0 -
{$FIRST_DAY_OF_YEAR()-366$} will be right 75% of the time, it will do
0 -
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.
0 -
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.
0 -
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
0 -
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
0 -
Hi @Mark Stoker ,
Thanks for the response. But we are facing this issue while scheduling a BIP report.
Thanks
0