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

Naresh KnK

User_BO6Q8

try this:

SELECT

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

FROM DUAL

Prudence K

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 DUAL
DD: Last day of the previous month	SELECT Last_Day(ADD_MONTHS(SYSDATE,-1)) Last_day FROM DUAL
DD: 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$}

Janardhana K

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

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

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

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

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

Bhaskar Konar

Hi Amy,

Please find below an example.

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

Hope this help.

Cheers,

Amy Wilson

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

hi All,

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

Anna Kryszak

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:

MaartenDIF

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

Mark Stoker

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

@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

Hi @MaartenDIF,

Can you see if the below works?

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

Thanks

Mark

Satyam Roy

Thanks for the information all.

MaartenDIF

@Mark Stoker

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).

Hua_Wang

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.

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.