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.
Comments (1)
Hi Mark
Thanks for the feedback. I have been cavassing some opinion and thoughts from some colleagues here around the security.
This was a comment from Bryan who is a long time apex developer/sales person
The new APEX integration gets around this point and in my opinion its a
non issue. They way I was doing things before (and it sounds like Mark
was too), was to use URL integration and have APEX just call a URL.
The new APEX integration doesn't do this. APEX takes a XML data set an
RTF and sends it to BIP using a web service for processing. APEX has
already protected the the mechanism to invoke this process (you can
require both APEX authentication and authorization on the button that
will invoke the report). I don't think it is an issue. If you want to
be able to call BIP reports that are stored completely on the BIP side
that is a different issue. However, the current implementation keeps
everything (the query and the RTF) in the APEX metadata (very clean
from an APEX development standpoint).
and born out by Marc on the Apex dev team
You're correct, in APEX, this is not an issue. You can use whichever
authentication you like to authenticate your APEX application (APEX
auth, SSO, LDAP, etc). And then when you request a report, we post the
XML report data along with the RTF or XSL-FO template to BI Publisher
and get the PDF back. This is all happening on the database layer, the
database does the post and retrieves back the PDF as a BLOB, which then
is downloaded by the client.
Of course this does not solve the issue of what to do about a report
actually stored in BI Publisher. There's been some discussion earlier
this year about having a single repository for storing reports and
templates, and not storing this in different places (BI Publisher and
APEX in this case). But I think this is missing the point of how APEX
customers use this. They want the seamless integration with their own
custom built APEX web applications, and don't want to have to come up
with some fancy authentication model to retrieve their reports from
another system. They also want to have tight integration with their
application's session state. So having the ability to include any
application and page item values in their PDF documents, addresses this
requirement.
I hope this makes things a little clearer on the question of security with the new integration. As has been mentioned the situation where a report is stored on the BIP server and called from Apex is one that we are looking at.
Can you take advantage of the integration to execute your reports i.e. create them on the Apex side?
Posted by Tim Dexter | June 7, 2007 1:06 PM
Posted on June 7, 2007 13:06