Listening Oracle Technology: May 2010

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.

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.

Click BI publisher Enterprise Login

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

Let’s setup the connection to the database

Click on Admin Tab

Click on Jdbc Connection

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

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.

Click “Apply”

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

Under Security Center Click on Security Configuration

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

Click on Reports TAB

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

Enter Guest in the Folder Name Field and click Create

Open the new Guest Folder

Click on Create a New Report

Enter ExampleReport and Click Create

A new report is created click edit link.

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

Select LocalConnection as Default Data Source

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

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

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

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

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

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.

Click View and View again in the output example shown.

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

Creating Report Template

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.

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)

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

Enter http://192.168.1.4:9704/xmlpserver/

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

Click on Template Builder

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

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

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

Click on Insert -> Table Wizard menu

Select Table and click Next

Click Next

Click on to select all fields and click Next

Group by Status and Click Next

Select Hire Date as sort By and click Next

Click Finish

The template should be displayed as the picture below.

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

We can test the report Click on Preview -> HTML

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

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

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