Pages

Monday, May 24, 2010

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.

clip_image002

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

clip_image004

  1. Click BI publisher Enterprise Login

clip_image006

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

clip_image008

Let’s setup the connection to the database

  1. Click on Admin Tab

clip_image010

  1. Click on Jdbc Connection

clip_image012

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

clip_image014

  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.

clip_image016

  1. Click “Apply”

clip_image018

  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

clip_image020

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

clip_image022

  1. Click on Reports TAB

clip_image024

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

clip_image026

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

clip_image028

  1. Open the new Guest Folder

clip_image030

  1. Click on Create a New Report

clip_image032

  1. Enter ExampleReport and Click Create

clip_image034

  1. A new report is created click edit link.

clip_image036

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

Select LocalConnection as Default Data Source

clip_image038

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

clip_image040 clip_image042

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

clip_image044

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

clip_image046

  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

clip_image048

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

clip_image050

  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.

clip_image052

clip_image054

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

clip_image056

clip_image058

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

clip_image060

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.

clip_image062

  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)

clip_image064

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

Enter http://192.168.1.4:9704/xmlpserver/

clip_image066

  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

clip_image068

  1. Click on Template Builder

clip_image070

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

clip_image072

  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

clip_image074

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

clip_image076

  1. Click on Insert -> Table Wizard menu

clip_image078

  1. Select Table and click Next

clip_image080

  1. Click Next

clip_image082

  1. Click on clip_image084 to select all fields and click Next

clip_image086

  1. Group by Status and Click Next

clip_image088

  1. Select Hire Date as sort By and click Next

clip_image090

  1. Click Finish

clip_image092

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

clip_image094

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

clip_image096

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

clip_image098

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

clip_image100

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

clip_image102

  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

clip_image104

  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

clip_image106

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

clip_image108

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

clip_image110

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

clip_image112

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

clip_image114

  1. Click on Home Image

clip_image116

  1. Click on Create Buttons

clip_image118

clip_image120

clip_image122

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

clip_image124

Click Next

clip_image126

Click next

clip_image128

clip_image130

clip_image132

clip_image134

clip_image002[4]

No comments:

Post a Comment