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

User_BO6Q8 · 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

Prudence K · Mar 9, 2020 5:24PM

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