Scheduled report jobs current & previous month formula for a specific date each month

Lisa O'Kelley

Hello!

We are trying to find a formula for a scheduled report job to run monthly on the 28th of each month.

Currently the report parameters contain formulas as follows:

Current Month Date: {LAST_DAY_OF_MONTH()$}

Prior Month Date: {FIRST_DAY_OF_MONTH()-1$}

However, we need a formula that shows the 28th (or a specific date of each). i.e. 28th of current month and the 28th of the prior month.

Any suggestions on how to write this? Or would it be possible? Thank you in advance!

Sumanth V -Oracle

@Lisa O'Kelley - Schedule Report Job already provides this functionality. Any specific requirement why this needs to be through a formula and the below won't work. Thanks.

Lisa O'Kelley

The current/prior month is part of the report parameters. In the scheduled job it shows in the General tab. We have the schedule set to run on the 28th but we need the data to be as of the 28th of the current month and the 28th of the prior month.

Gianni Ceresa

You said "report" a few times: is this a Publisher report? Or is it an agent and your "report" is an analysis?

Lisa O'Kelley

It is a publisher report, with a schedule report job. NOT an analysis.

Gianni Ceresa

Thanks @Lisa O'Kelley for the details.

The question has been moved to the Publisher category so you can get a better answer.

Kevin M-Oracle

Kevin M-Oracle

See Increment Date Parameters

https://docs.oracle.com/middleware/bi12214/bip/BIPUG/GUID-22F94743-8AB7-43E8-AEE8-3D022AB306B2.htm#GUID-ADEE63B7-0FF7-45F8-B4B6-DE8915B3242A

Rgds,

Kevin

Lisa O'Kelley

Thank you @Kevin M-Oracle, yes, I found that page. It looks like there isn't an existing "formula" for specific date. All of the ones listed have the +/- 1, 7, etc. #of days but that will not work for our needs since each month has a different number of days and we need the report to run monthly with a specific date for the data each month.

I'm going to circle back with the original creator of the report to see if we can modify this report parameter.

Thank you everyone.

Kevin M-Oracle

You can create three schedules to cover months with 30 and 31 days, and February.

The use {$SYSDATE()-30$}, or {$SYSDATE()-31$} or February.

Kevin

Lisa O'Kelley

@Kevin M-Oracle now that's an idea/good option as well. 🤔

Thank you. I'll share this with the requestor. 😁