Use these topics to increment date parameters and to define a destination file name dynamically.
This section includes the following topics:
If the scheduled report includes date parameters, when you enter the values for the dates for the schedule, you cannot change the date values. Every time a scheduled instance of the report is run, the same date parameters are used.
If changing the date parameters is required for each run, you can enter an expression in the date parameter field of the scheduler to calculate the date each time the report job executes.
For example, if you create a schedule for a report that runs every Monday to capture data from the previous week, you need to update the date parameters for the report to increment to the first and last days of the previous week.
Enter one of the following functions using the syntax shown to calculate the appropriate date at the scheduled runtime for the report:
{$SYSDATE()$} - current date, the system date of the server on which BI Publisher is running.
{$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
The date function calls in the parameter values are not evaluated until the report job is executed by the Scheduler.
You can also enter expressions using the plus sign (+) and minus sign (-) to add or subtract days as follows:
{$SYSDATE()+1$}
{$SYSDATE()-7$}
For this example, to capture data from the previous week, each time the schedule runs, enter the following in the report's date parameter fields:
Date From: {$SYSDATE()-7$}
Date To: {$SYSDATE()-1$}
Note:
You can set up the date functions as default parameter values in the data model. In this case, every time a user views the report from the report viewer, the date parameter is calculated according to the expression supplied for the default value. See Adding Parameters and Lists of Values in Data Modeling Guide for Oracle Business Intelligence Publisher.
When entering the remote file name for a Web folder or FTP destination, you can enter a date expression to have BI Publisher dynamically include a date expression in the file name. The date is set at runtime, using the server time zone.
These expressions are described in the following table.
| Expression | Description |
|---|---|
|
%y |
Displays the year in four digits: Example: 2011 |
|
%m |
Displays the month in two digits: 01-12 (where 01 = January) |
|
%d |
Displays the date in two digits: 01-31 |
|
%H |
Displays the hour in two digits based on 24-hour day: 00-24 |
|
%M |
Displays the minute in two digits: 00 - 59 |
|
%S |
Displays the number of seconds in two digits: 00 - 59 |
|
%l |
Displays milliseconds in three digits: 000 - 999 |
Use these steps to create a file name that appends a date and a file name that prefixes a date and appends a time.
To create a file name that appends the day, month, and year such as:
myfile_01_11_2010.pdf
Enter the following:
myfile_%d_%m_%y.pdf
To create a file name that prefixes the day, month, and year and appends the hour and minute such as:
01_01_2010_myfile_22_57.pdf
Enter the following:
%d_%m_%y_myfile_%H_%M.pdf
If the file name includes an undefined expression such as my_file_%a%b%c.pdf, the file is created as named my_file_%a%b%c.pdf.