X

An Oracle blog about BI Publisher

Apex Integration I

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:



Apex:



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



Apex1:



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



Apex2:



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"


 Apex3:



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.

Apex4:

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.

Apex5:



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

Join the discussion

Comments ( 3 )
  • Tim Dexter Thursday, June 7, 2007
    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?

  • red microwave oven Sunday, August 15, 2010
    i appreciate beneficial piece of writing. I ended up being thrilled while i examined ones posting and have made a membership into it. You should update this article when you are able because I'd like more information.
  • Gangsta Luv Lyrics Monday, September 13, 2010
    Awesome, Lady Gaga got eight VMA awards last night! I'm extremely excited for her achievements and I love that Born This Way piece she belted also.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.