Step 21 – Apex Developer Day - Calling BI Publisher reports from APEX

Note: For this LAB you need Microsoft Office installed on your Local PC/Laptop

There are many way to integrate BI Publisher and APEX let try one a simple way.

1. Start BI Publisher in case it is down.

You will see BI publisher up and running when it display the initialized in the last row.

1. Click BI publisher Enterprise Login

1. You can login as Administrator/Administrator. Notice letter “A”in upper-case.

Let’s setup the connection to the database

1. Click on Admin Tab

1. Click on Jdbc Connection

1. Add a new data source. Click over the button “Add Data Source”

1. Complete the information

Data Source Name: LocalConnection

Connection String: jdbc:oracle:thin@localhost:1521:orcl

Enter the Username used in Step 5 from Section 1

Username: <YourName>

Password: <Your Password>

Database Driver Class: oracle.jdbc.driver.OracleDriver

Click Test Connection, a confirmation message “Connection established successfully” will be displayed above of the parameters.

1. Click “Apply”

1. Because we don’t want to complicate the lab with the users validation (Just for this lab)

Under Security Center Click on Security Configuration

1. Under Guest Access Section Select the Check Mark Allow Guest Access and enter Guest on the Guest Folder Name field. Click Apply

1. Click on Reports TAB

1. Go to Reports > Shared Folders click Create a new folder and create a new Folder "Guest"

1. Enter Guest in the Folder Name Field and click Create

1. Open the new Guest Folder

1. Click on Create a New Report

1. Enter ExampleReport and Click Create

1. A new report is created click edit link.

1. Enter Example Report for be displayed in APEX in the description Field

Select LocalConnection as Default Data Source

1. Click Data Model in the left frame, and Click in New Tab

1. Enter ApexEmployeeDataSet in the Name Field, select SQL Query as a Type and select LocalConnection as Data Source. Click Query Builder Button.

1. Select APEX_EMPLOYEES and APEX_TASKS. These tables must be displayed in the right frame.

1. Click over EMPLOYEE_ID in APEX_EMPLOYEES table, keep the click pressed and drag and drop it over ASSIGNED_TO column in APEX_TASK Table

It must be created a link(“Foreign Key”) between those two tables

1. Select First_Name, Last_Name, Email, Hire_Date on APEX_EMPLOYEES table and Task_Name, Status on APEX_TASK table. Click Save

1. Convert the date format to char as: to_char(APEX_EMPLOYEES.HIRE_DATE, ‘DD-MON-YY’) as HIRE_DATE,

Click Save on left side of the page to save the changes made.

1. Click View and View again in the output example shown.

1. Because we have not defined any template to the report. It is displayed in XML Format.

Creating Report Template

1. Because the next step must be done in the HOST operating system (Your PC/Laptop) not inside the Guest Operating System, we need to know the IP Guest.

1. In the terminal windows change to the root user, enter ” su – “ oracle as password. If you are connected as root the symbol # must be the prompt. Now write ifconfig and press enter. An IP will be displayed an it will use on your personal PC/Laptop . (In my case 192.168.1.4)

1. In you pc Open a browser (iExplorer, Firefox, Chrome) .

Enter http://192.168.1.4:9704/xmlpserver/

1. Now we are getting access to our BI Publisher externally from our laptop.

Login as Administrator/Administrator Click Sign in and click on Guest Folder

1. Click on Template Builder

1. Download the file BIPublisherDesktop.exe and run it. Microsoft tools(Word, excel, etc) must be closed. Install the tool selecting the default parameters

1. Now you can Open Microsoft Word. A new menu must be in the right top of MS Word with name And-Ins. Click On it, you will see the Oracle BI Publisher menu. Click on Oracle BI publisher menu and

1. A window with show up, click on Shared Folders -> Guest and select ExampleReport, Click Open Report

1. Click on Insert -> Table Wizard menu

1. Select Table and click Next

1. Click Next

1. Click on to select all fields and click Next

1. Group by Status and Click Next

1. Select Hire Date as sort By and click Next

1. Click Finish

1. The template should be displayed as the picture below.

1. Save the template click on Oracle BI Publisher -> Upload Template As.

Before to Upload the file it will require to Save as a .rft File

1. We can test the report Click on Preview -> HTML

1. A Browser session will appear and show us an example of the report.

1. We have the new template Saved in the BI Publisher repository. Now we can Swith Back to the Oracle Enterprise Linux Guest Operating System. And try to run the report. Click View

1. Notice now the template EmployeeTask is displayed as a template and multiples Formats to display the report are available. You can try some of them if you want. At the end Select HTML and click VIEW

1. Now in the right side of the page click on link this report and click Document Only.

1. A URL will be displayed. http://host01.example.com:9704/xmlpserver/Guest/Example.............. Copy it

1. Now go back to APEX, Create a new TAB, and Clcik on APEX 3.2 Button and login as <YourName> user

1. In application Builder Click on View Applications, and Select Project tasks Application - <N>

1. Click on Home Image

1. Click on Create Buttons

Enter BIPUBLISHER REPORT in Button Name Field and Label. Select Redirect to URL without submitting page. Click Next

Click Next

Click next