Oracle BI – Dashboard Prompts & BI Publisher

Situation:
One of the common questions that generally come up with regard to BI EE and BI Publisher is ‘how does one pass parameters to BI Publisher reports in Dashboards?’. One of the solution is to create a BI Publisher Report using HR-scheme as data source. Define a parameter for the BI Publisher report and then match this parameter name while assigning the presentation variable for the dashboard prompt.

Solution:
This method is generally the most used since most of the BI Publisher reports would have database as the data source.

We create a simple BI Publisher report with data coming in from HR-schema of the database.

Report-details:

  • Default Data Source: HR
  • Parameters per line: 1
  • Show Controls: unchecked

Overview BI Publisher Report:


First we will create a new data model, called DS_HR. SQL query will be:

Now add a parameter to the report. In our case, we will have ‘pDept’ as the parameter name.


Create a new RTF-template with following fields:


At least add a template to our report. Upload the template you’ve created before and create a new template name ‘TemplateHR’.


Save Report and test your BI Publisher report to be sure everything is OK.
Then go to the BI Answers and create a dashboard prompt on ‘department name’.

Note: The name of the presentation variable must exactly match that of BI Publisher parameter name.

We set default value to ‘IT’ and assign presentation variable to ‘pDept’.


Include this dashboard prompt and the BI Publisher report into the dashboards.


Final result in Oracle BI Dashboard:


When you checked ‘show controls’ in the BI Publisher report:


With this option enabled, you can choose different templates and output formats.