This is a follow up on an earlier blog that we did on Date Parameters in BI Publisher.
Oracle BI Publisher offers more Date Expressions such as:
{$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
This has been documented in Oracle’s Business Intelligence Publisher New Features Guide.
The expression is entered in the “Default Value” field for the Date Parameter in “General Settings”.
Here are the steps to implement a “Date” parameter type in BIP. I have also included some additional Java Date formats that can be used to format the dates.
- Open (Edit) the report using date parameter in BIP.
- Click on the “Parameters” node. Click the New button in the toolbar to create parameter.
- Use the Parameter Type of “Date” and set the remaining values for the Parameter (as displayed):
Define a Date Parameter (Calendar Widget) in Oracle BI Publisher
Following is a description of the important fields used with the “Date” parameter type:
| Default Value |
This is the default date that will show up in the parameter field at run time. We can set this value to the system date by using “{$SYSDATE()$}”. We can also perform calculations such as {$SYSDATE()-7$} to set default value to last week’s date. |
| Display Label |
This is the label that will display next to the Calendar Widget at run time. This should be a meaningful label for the users. |
| Date Format String |
The format in which the date will be displayed and stored. This should be java date format (see java date format table below for more examples). I am used to Oracle date formats so I had to look up Java date formats in a book. The equivalent of Oracle “DD-Mon-YYYY” in java is “dd-MMM-yyyy”. |
| Date From |
The minimum date a user will be able to select from the calendar widget at run time. |
| Date To |
The maximum date a user will be able to select from the calendar widget at run time. |
Note: We can perform calculation on “Default Value”. Example: {$SYSDATE()-7$}
Also, I found the Date From and To fields very helpful. It allowed me to keep users within a pre-defined range of dates. Please note when using these two fields, specify the dates in the format defined in the “Date Format String” field. If you don’t use the exact same format, BI Publisher will not give you any errors. But at run-time, it will not display the date correctly.
Some additional Java Date formats that I got from a Java Book:
| Format |
Meaning |
Result |
| yy or yyyy |
Year |
08 or 2008 |
| M or MM |
Month Number |
7 or 07 |
| MMM or MMMM |
Month Text |
Dec or December |
| d or dd |
Day in Month |
3 or 03 |
| hh or HH |
Hour |
04 (1-12) or 16 (0-23) |
| m or mm |
Minute |
6 or 06 |
| s or ss |
Second |
7 or 07 |
| EEE or EEEE |
Day in Week |
Tue or Tuesday |
| zzz or zzzz |
Time Zone |
EST or Eastern Standard Time |
Finally, run your BI Publisher report and click on the calendar icon to invoke the Calendar Widget.
Here is a run time image of the calendar widget for the report date parameter