OBIEE Schedule Report Job - monthly report for last month

Question

Our department needs to generate reports of previous month on the first day of each month (i.e. run report of August on September 1st).

Users need to set the From Date & To Date for each monthly report individually as these two fields are static. Is there any way to make it dynamic so that users can set something like below once and for all?

From Date = First_Date_OF_Last_Mth 

To Date = Last_Date_of_Last_Mth

Frequency = monthly (1st day of the month) from 2017 to 2027

This is the schedule report job setting page:

Answer 1

You could add the filters in the OBIEE analysis itself.

For first day of last month use this formula TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

And for last day of last month use this formula TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

So your filter in the report will look like below

• Edit/Update

The screen looks like BI publisher scheduler page, if that is the case and your Data Model is SQL, then you can handle it in the query itself. If it is an OBIEE report then the above formula can be used.