Trying to pass first day of previous month dynamically

BSM

Summary

Trying to pass first day of previous month dynamically

Content

Hello BIP experts,

I need to create dynamic date parameters From: and To:

Last day of previous month: {$FIRST_DAY_OF_MONTH()-1$}

Can you please tell me how can i achieve From: First day of previous Month  ?

Thanks a lot for your help.

P.S: I did check all the previous discussions based on this topic, but didnt find a resolution. Can you please help?

Thanks,

Pravin

Sherry George

Do not think it is possible.

Brajesh Shukla-95078

LAST_DAY(ADD_MONTHS(sysdate,-2))+1

Sherry George

What is the data model of your publisher report, is it a SQL query ? If it is , I think then you can take care of the date filter value in SQL itself.

BSM

Thanks for the input.

I am trying to schedule a BIP report which runs in the background and fetches the data from previous month. I am able to get the Last day of previous month using the value {$FIRST_DAY_OF_MONTH()-1$}. But im unable to achieve getting the first day of previous month.

BI Publisher supports not only the current date (sysdate) function but also other types of functions for commonly used dates.

• SYSDATE()   - Today’s date
• 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

Daniel Willis-92031

Do want the user to pick a month and then your query runs for the last day of the previous month?

I only use BIP within OBIEE so might not be on the right track but in my scenario I could have a prompt/parameter for the value and then use it in the query

e.g. MyDateColumn < LAST_DAY(ADD_MONTHS(:PROMPTDATE,-1)) to get last day of prior month

BSM

I wish there is a way

Nivya Mathire

SELECT  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)  FIRST_DAY ,LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)) LAST_DAY from dual