how to get previous month first_date & last_date in bi publisher?
Answers
-
@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.
1 -
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 DUALHope this helps.
Thanks,
RJ
1