Making the Most of Date-Related Variables in Oracle OTBI Reports
In Oracle OTBI reporting for Oracle Fusion Cloud Applications, date handling is a cornerstone for accurate, timely analytics. Oracle provides powerful repository and session variables designed to dynamically reflect the current date and calendar periods, enabling reports that always stay current without manual updates.
Key Date Variables You Should Know
CURRENT_DATE_REP_OTBI
Returns the current date from the system clock when the report runs. It’s commonly used as a baseline for date comparisons or dynamic filters.CURRENT_MONTH_START_DATE_REP_OTBI
Delivers the first day of the current Gregorian month, allowing reports to easily filter or group data by month without hardcoded date boundaries.CURRENT_MONTH_END_DATE_REP_OTBI
Returns the last day of the current Gregorian month, completing the date range for monthly period reporting.
How to Use These Variables in Your Reports
Using these variables in filters or criteria ensures your reports dynamically align with the current date context, without requiring manual date changes each month.
Example filter to include only data from the current month:
"Transaction Date" BETWEEN VALUEOF(NQ_SESSION.CURRENT_MONTH_START_DATE_REP_OTBI)
AND VALUEOF(NQ_SESSION.CURRENT_MONTH_END_DATE_REP_OTBI)
Example usage for reporting as of today’s date:
"Effective Date" = VALUEOF(NQ_SESSION.CURRENT_DATE_REP_OTBI)
This technique is essential for:
Building monthly performance reports that automatically adjust periods.
Comparing current month data to previous months or same month last year with relative date functions.
Filtering transactions executed on or before today, maintaining accurate as-of reporting snapshots.
Why Use Repository and Session Variables?
Automation: No need to update date ranges manually when reports run.
Consistency: All analyses based on these variables align on the same date logic.
Performance: Efficient query plans with well-defined date boundaries minimize data volume.
Summary
Using date repository variables like CURRENT_DATE_REP_OTBI and month start/end equivalents in OTBI reports ensures your analytics remain timely, accurate, and easy to maintain key success factors in any Oracle Fusion Cloud Applications reporting strategy.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
