Dynamic Date Parameter – Current Date

This is actually really not known trick maybe it is not documented in the user’s guide yet, but BI Publisher supports a ‘dynamic date parameter’.

WHAT???

Ok, so don’t you want to set the default date for the date parameter of the report and make it to be a today’s date or a week before or something ? Or, don’t you want to schedule your report and want it to generate always the last 7 days of the data ?

Yes, here comes the dynamic date parameter!

For example, you can use a current date function (sysdate()) to set a today’s date for your date parameter so that the date will be set always to today’s date depending on when you open the report. So if you open the report today the date would be 11/18/09, if you open tomorrow then it would be 11/19/09.

This would be even more critical when you want to set the date parameter values for your scheduling report. For example, you might want to run a report on every Monday to display the data for the last 7 days. You can’t hard-code any date to the date parameter of the report because then the date range will get too old and invalid. In order to get the last 7 days of the data in SQL world, typically you set a start date to be ‘sysdate – 7’ and an end date to be ‘sysdate’. Yes, you can do the same thing with the BI Publisher’s scheduled report by doing exact the same but setting such parameter values from the scheduler edit window. BI Publisher generate the appropriate dates based on the dynamic date function and pass them to the underlying sql query.

BI Publisher supports not only the current date (sysdate) function but also other types of functions for commonly used dates.

• SYSDATE()   - Today’s date
• 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

And here is some example…

Set Default Date to Current Date

In order to use the above date functions you need to enclose it with curly bracket and dollar sign such as ‘{$SYSDATE()$}. You can simply type this in the ‘Default Value’ text filed in the report edit window.

You can also have some simple calculation with the date functions. For example, if you want to set the default date to be 7 days before the current date, then you can type ‘{$SYSDATE()-7$}’. Plus (+) and minus (-) are supported for the calculation.

Set Dynamic Date for Scheduled Report

You can also set the date parameter values using the above functions. For example, if you want to schedule a report and want the report to generate the last 7 days of the data, then you can set the From Date to be ‘{$SYSDATE()-7$}’ and To Date to be ‘{$SYSDATE()$}’

Or, if you want to run the report to always return the month-to-date data then you can set the From Date to be ‘{FIRST_DAY_OF_MONTH()}’ and To Date to be ‘{$SYSDATE()$}’.

Just a useful trick if you haven’t known!