Oracle BI Publisher Blog

With the latest release of Applications Express you can now take advantage of direct BIP integration ... welcome APEX to the growing list of applications interfacing with BIP for reporting. I downloaded the latest and greatest and got it installed very easily. The integration relies on having the BIP server running somewhere in your stack and you will need the 10.1.3.2.x version:

Configuration

You need to log in as an admin to be able to set up the BIP server. You need to arm yourself with the server and port that BIP is running on. Once you're 'in' navigate to the Home > Manage Service > Instance Settings

Once on the page, get to the 'Report Printing' section about mid way down.

Fill the information required, the protocol, server and port are obvious, for the 'Print Server Script' you need to fill in the following '/xmlpserver/convert '. This is a servlet running on the BIP server that accepts template, data and the output format required and returns the output back to Apex for rendering.

The other important piece you need to check is the 'Standard Support / Advanced Support' radio button. Checking 'Standard' allows you to use the:

• Standard Support - This is the default setting. It allows you to print report regions and report queries using either the built-in templates provided with Application Express or other formats you create that are in XSL-FO format (not RTF).
  
• Advanced Support - If you select this option, for Report Layouts, you can upload your own customized RTF templates for printing reports within Application Express. This option requires the installation of a licensed version of BIP.

For this set of articles we're going to go for, strangely enough, the Advanced Support option.

Commit your changes and you can now log out and get back in as a regular developer in your workspace.

Building Report Queries

Open your application (I used the Demo app) and navigate to the Shared Components. You're going to create a Report Query, I went with a simple customer orders query:

select  "DEMO_CUSTOMERS"."CUST_LAST_NAME" as "CUST_LAST_NAME",
  "DEMO_CUSTOMERS"."CUST_CITY" as "CUST_CITY",
  "DEMO_CUSTOMERS"."CUST_STATE" as "CUST_STATE",
  "DEMO_CUSTOMERS"."CREDIT_LIMIT" as "CREDIT_LIMIT",
  "DEMO_ORDERS"."ORDER_ID" as "ORDER_ID",
  "DEMO_ORDERS"."ORDER_TOTAL" as "ORDER_TOTAL" 
 from  "DEMO_ORDERS" "DEMO_ORDERS",
  "DEMO_CUSTOMERS" "DEMO_CUSTOMERS" 
 where   "DEMO_CUSTOMERS"."CUSTOMER_ID"="DEMO_ORDERS"."CUSTOMER_ID"

The XML structure drop down has two choices:
 Standard - this is simple ROWSET/ROW data
 Advanced - this includes session data, you'll get something like this:

<?xml version="1.0" encoding="UTF-8"?>
<DOCUMENT>
    <DATE>05-JUN-07</DATE>
    <USER_NAME>TDEXTER</USER_NAME>
    <APP_ID>100</APP_ID>
    <APP_NAME>APEX - Application Builder</APP_NAME>
    <TITLE>CustomerOrders</TITLE>
    <REGION ID="0">
        <ROWSET>
            <ROW>
               ...
            </ROW>
        </ROWSET>
</DOCUMENT>

You can check the data format for yourself by clicking on the 'Export REport as XML' link in the Tasks pane on the right hand side.



You'll also notice the 'Test Report' link, based on the 'Format' drop list, when you click on the link Apex will make a call to the BIP server using a default layout. This is because we have not defined an RTF or a layout via the Apex interface, check the 'Report Layout' drop list, it only has a single entry, 'Use Generic Report Layout'. NOt the most awe inspiring layout.

Next we'll take a look at building layouts and deploying them.