How to integrate BI Publisher in Oracle Forms – Part I

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 (12.2.1.3.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 12.2.1.3.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:

http://<Servername>:<Port>/xmlpserver

Add your database by clicking on “Administration”>”JDBC-Connection”:

Unbenannt

Click on “Add Data Source” and configure your datasource based on your installation.

Unbenannt2.PNG

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!

Unbenannt3

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”.

Unbenannt4

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.

Advertisements

Oracle Forms 12c & BI Publisher

Today I started to have a look at the possibilities of integrating the BI Publisher in Forms 12c.

2

Sadly I didn’t find anything regarding this topic in the online documentation – so I created a report object and had a look at the properties.

We found out, that there are several new properties in the report object. So I created a forms module with a simple button and tried to call a BI-Publisher Server. As expected, my first try was not successful – I got an “FRM-41223: BI Publisher integration error”. So I had a look at the online help under “SET_REPORT_OBJECT_PROPERTY” to see if there are any new properties – and yes there are quite a lot new BI-Properties:

  • BIP_SSL_CONNECTION: Indicates whether or not SSL is mandatory or optional.
  • BIP_SERVICE_LOCATION: The URL to the Oracle BI_Publisher server Web Services WSDL for ScheduleServices.
  • BIP_REPORT_PATH: The absolute path to the report to be run (on the BI_Publisher server).
  • BIP_REPORT_LOCALE: Preferred report locale.
  • BIP_REPORT_FORMAT: Desired report output format.
  • BIP_REPORT_TEMPLATE: A template registered with the report specified in BIP_REPORT_PATH. It is common practice to provide this property along with report name/path but BI-Publisher gives the option of setting a default value when creating/editing a report, therefore this is an optional property in Forms. Value is used and validated by BI-Publisher.
  • BIP_REPORT_PARAMETERS: A list of name/value pairs separated by a space.
  • BIP_USER: The BI-Publisher username required to access the Web Service. This value cannot be set in the Property Palette.
  • BIP_PASSWORD: The BI-Publisher user password required to access the Web Service. This value cannot be set in the Property Palette.
  • FTP_SERVER: FTP server name as configured in BI-Publisher.
  • FTP_SECURED: Determine if FTP will be secured (i.e. SFTP) or unsecure.
  • FTP_USER: FTP server username.
  • FTP_PASSWORD: FTP server user password.
  • FTP_FILE_NAME: Output file name to be stored on FTP server.
  • FAX_NUMBER: Fax number. Format as required by your server configuration.
  • FAX_SERVER: Name of Fax server as configured in BI-Publisher.
  • MAIL_TO: The mailto email address.
  • MAIL_FROM: The mailfrom email address.
  • MAIL_CC: The mailcc email address.
  • MAIL_BCC: The mailbcc email address.
  • MAIL_REPLYTO: The replyto email address.
  • MAIL_SUBJECT: The email subject string.
  • MAIL_BODY: The email body string.
  • MAIL_SERVER: Name of the email server (SMTP) configured in BI-Publsher.
  • LOCAL_FILE_NAME: Absolute path and filename of the output file.
  • PRINT_COPIES: Number of copies to print.
  • PRINT_ORIENTATION: Page orientation.
  • PRINT_RANGE: Page range to print.
  • PRINT_SIDE: Enable two-sided printing. This setting will be ignored if the target printer does not support two-sided printing
  • PRINT_TRAY: The tray number. If you do not specify this option, the default media of the target printer will be used. It will be ignored if the target printer doesn’t support the media option.
  • PRINTER_NAME: Printer name on the BI-Publisher host to send the documents to.
  • WEBDAV_AUTH_TYPE: The authentication type.
  • WEBDAV_USER: The WebDAV username.
  • WEBDAV_PASSWORD: The WebDAV user password.
  • WEBDAV_FILE_NAME: The remote filename.
  • WEBDAV_SERVER: The WebDAV server name.

After setting the properties BIP_USER & BIP_PASSWORD I was able to run the BI-Report:3