how to get previous month first_date & last_date in bi publisher? - Page 2 — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

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

2»

Answers

  • Diane G123
    Diane G123 Red Ribbon

    @Jahnvee Sharma we had a similar issue with scheduling an extract with a BI template. We needed to run the extract in the early hours of the morning with data for that day but the daylight savings pushed the report before midnight so we got the data for the previous day. As far as I'm aware it is a known issue. You can work around it by either updating the schedule twice a year or choosing a different time to run the schedule. I think there was a suggestion in the ideas area to fix the issue.

  • RoyKC
    RoyKC Gold Trophy
    https://community.oracle.com/customerconnect/discussion/comment/352480#Comment_352480

    When the built-in functions are not sufficient, you can use a SQL query to calculate the default date. This is common for requirements like "the first day of the previous month."


    To use a SQL-based default, you must change the parameter's Parameter Type to Menu and create a List of Values (LOV) based on a SQL query.


    Example: First Day of Previous Month
    A SQL query to get the first day of the previous month would be:

    SELECT TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'), 'YYYY-MM-DD') as display_value,
    TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') as data_value
    FROM DUAL

    image.png image.png

    Hope this helps.

    Thanks,

    RJ