Dynamic Date Parameter in BIP Scheduler

Scenario

I had created a BIP report with a dynamic date of sysdate -8 as a prompt. The report works fine and the dataset is all good. However when I schedule this report I get and error stating

‘IOException:[INSTANCE_JOB_ID=344978] Invalid parameters requested.’

I tired to look into the code and also the trigger which I had set up and for some reason I could not figure out the issue. After researching a bit I found it was because of the date prompt.

Fix

For some reason BIP cannot dynamically change the date based on the query while scheduling the 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 8 days of the data in SQL world, typically you set a start date to be ‘sysdate – 8’ 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.

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 8 days of the data, then you can set the Date to be ‘{$SYSDATE()-8$}’