In the next couple of blog entries I would like to give a step by step introduction on how to integrate Oracle’s offspring BI Publisher into Oracle Forms. Since the latest release of Oracle Forms (220.127.116.11.0) will be the last release with Oracle Reports there is the neccessary to move to another technology. Maybe in further posts I will also explain other possibilities like Jasper Reports etc.
I assume that you’ve installed Oracle Forms and the BI Publisher in the version 18.104.22.168.0 and will not explain how they need to be installed. For the demo I will use Scott/tiger to get some data into Oracle Forms and the BI Publisher.
Datasource & Datamodell
First we will create a Demo report in the BI Publisher. To do so login to the BI console by opening following URL in your Browser:
Add your database by clicking on “Administration”>”JDBC-Connection”:
Click on “Add Data Source” and configure your datasource based on your installation.
Next we need to create a new datamodel for the report that we want to call from Forms. To do so we go back to the main window and click on “datamodell”. Here you can create your datamodell directly by adding a new SQL-Query and write your SQL or you can use the SQL builder. I’ve created a new SQL-Query named “Employees_in_department” with the following SQL:
select "DEPT"."DNAME" as "DNAME", "EMP"."ENAME" as "ENAME", "EMP"."JOB" as "JOB", "EMP"."HIREDATE" as "HIREDATE", "EMP"."SAL" as "SAL" from "SCOTT"."EMP" "EMP", "SCOTT"."DEPT" "DEPT" where "DEPT"."DEPTNO"="EMP"."DEPTNO" group by "DEPT"."DNAME", "EMP"."ENAME", "EMP"."JOB", "EMP"."HIREDATE", "EMP"."SAL"
Make sure to use your new datasource “FormsDemo”. Now we can click on “Data” and produce some demodata, that we can use to see how our report will look like. Select the amount of rows you want to query and click on “Save as sample Data”. Don’t forget to save your datamodell!
Create Report & Layout
Now we can create the actual report. For this I will use the online builder rather then the word plugin. Go to the main window and click on “report”. You can either use the wizard or build the report manually. Select your datamodell, click on “Use Report Editior” and “Finish”. You will be asked, where on the server you would like to save the report. Store it in the same folder as your datamodell.
Now you can create a Layout for your report. Select “Chart and Table”. Switch the demo chart to a pie chart and add the employee name column as value and the department name as series.
In the table section add the departmant name, employee name, job, and salary and group the table by the department column by selecting the column and choose “group left”.
Click on save. Et voila – you’ve created a demo BI Publisher Report. You can click on “Done” and “View Report” to get a preview of the report.
In the next blog post I will explain how to build a demo form, which executes the report in Forms 12c.