Hey guys this is a quick post about how we can
- Set default value for date Parameter in the Data Model
- Pass a default value for the date Parameter while scheduling the BI Report
Set Default Value for Date Parameter in BI Report
Create a data model and add a parameter to the data model with type as Date.
In the Default Value field, you have an option to use the following variables:
- $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
You can add variations of the above expressions like {$SYSDATE() + 1 $}. I have used {$FIRST_DAY_OF_MONTH()$} as the default value in the Data Model.
Run the Report
Let’s run the report to see how it shows the default value.
You will see the date has been defaulted to the 1st day of the month. In our case, the first day of March.
Set Default Value while Scheduling the Report
If you are scheduling the report we need to make sure we pass dynamic date values to the BI Report while scheduling. Lets see how we can achieve that. Open an already existing schedule or click on Actions > Schedule.
When you try to schedule the report, you will see that the value defaults to the first day of the month. This is good if you are only running it for the month of March. What if you want to schedule the report to run once every month? So in April it should be 04-01-2019, May it should be 05-01-2019 and so on. We have to use the system variables while scheduling. In the example, I want the date value to be the 1st day of the month. So I will be using {$FIRST_DAY_OF_MONTH()$} as the parameter value while scheduling the report.
Let’s submit the schedule and see how it looks once the scheduled job is completed. While you submit the report you will see the parameter value is populated as {$FIRST_DAY_OF_MONTH()$}.
Open the completed schedule and you will see the default value that was passed was “03-01-2019“. When the report runs in April, it will be 04-01-2019. When it runs in August, it will be 08-01-2019.
I hope this is helpful to some of you out there. Let me know if you have questions.