Oracle ApEx and BI Publisher

April 21st, 2008 by

I’m running a BI Publisher course this week, and one of the areas the client was really interested in was integration of BI Publisher with Oracle ApEx. As the notes on OTN and various blogs are sometimes a bit confusing, I thought I’d do a quick walkthrough of how the integration works. Setting up BI Publisher and ApEx integration consists of two steps; first you have to configure ApEx to work with BI Publisher (and if you’re using Oracle 11g as your ApEx database, configure the database to allow outbound network connections), and then you need to generate your report within ApEx and BI Publisher Desktop.

In this example I’m using Oracle BI Publisher 10.1.3.3.2 (as packaged along with OBIEE 10.1.3.3.2) along with Oracle Database 11.1.0.6 and ApEx 3.0. The first step in configuring ApEx is to log in to the Apex Administration application and bring up the Manage Environment Settings > Instance Configuration page, which we’ll use to tie in BI Publisher to this ApEx installation.

apex_bip_1.jpg

Selecting this administration page allows us to enter the connection details for the BI Publisher server. Using this dialog, you enter the hostname of the BI Publisher server, the port number and the page details of the BI Publisher interface. Note also that you radio button is set to “Advanced Printing” which signifies that we’re going to use BI Publisher; the Standard Support option above it requires you to use an Apache module that also prints to PDF but doesn’t have as much functionality as BI Publisher – you’d use this if you want BI Publisher to print to PDF but you haven’t licensed BI Publisher.

apex_bip_2a.jpg

One more thing that you have to do, if you’re using ApEx as part of Oracle 11g, is to configure the database to allow outward network connections, which is disabled by default (on earlier database releases this step isn’t needed. To do this, you run a PL/SQL script that uses the DBMS_NETWORK_ACL_ADMIN built in package to administer the database network access control list. This script assumes you’re using ApEx 3.0; if you’re using 3.1 you’ll need to change the references to FLOWS_030000 to FLOWS_030100

Run this script as the SYS or SYSTEM user, and you should then be ready to go.

To create an ApEx report that uses BI Publisher, log on to a workspace and create a new Shared Component > Report Query.

apex_bip_3.jpg

When the new Report Query dialog comes up, press the Create button to create the query, and then enter the SQL for the query you’re going to run. In this case, I’m using a query against the database tables used by the ApEx sample application.

apex_bip_4.jpg

On the next page of the dialog you can test the query – do this and make sure it comes back with some data. Next, you can tie your ApEx parameters to the report, and then a dialog is displayed where you can download the XML for your report and use it, with BI Publisher Desktop, to lay our your report template.

apex_bip_5.jpg

Once you’ve used BI Publisher Desktop to create your template RTF file, you can use the same dialog to upload the RTF file to ApEx so that it can then send it to BI Publisher when your report is displayed. One thing to note here is that the BI Publisher references only appear once you’ve done the configuration steps I mentioned beforehand – if you don’t do these, ApEx just makes reference to FO and XML files which is how it handles PDF printing when BI Publisher isn’t around.

Once you’ve uploaded the RTF file, ApEx on the next page displays a link that you can use when calling the report from elsewhere in your ApEx application, and you can press the Test Report button to try out the report and check it displays OK.

apex_bip_6.jpg

Pressing the Test Report button brings up a dialog asking if you want to open the PDF document, once you accept this the BI Publisher report is displayed as expected.

apex_bip_7.jpg

So all in all, it’s a relatively straightforward process, once you’ve worked out how to do the initial configuration step and if you’re using 11g, how to enable outbound network connections from the database. Thanks to Borkur for working most of this out for our course examples.

Comments

  1. Manish Says:

    Mark,

    While Oracle is marketing BIP heavily, I have heard/read comments in various avenues about how using BIP Desktop (MS-Word) to layout reports is NOT really production-ready (to put it mildly).

    In your travels, have you seen BIP successfully being used in place of Oracle Reports for production reporting to generate customer facing as well as operational reports?

    Manish

  2. Tyler Muth Says:

    Manish,

    I just finished my first REAL project with BI Publisher and APEX (internal app at Oracle). I have to say that I was really impressed with how easy it was to produce exactly the output I was looking for. Now, anyone who knows me knows that I work for Oracle (at least my badge still worked today), but all BS and marketing aside, this product really works.

    Tyler

  3. Francis Says:

    Hi Mark,

    Very interesting.

    In order to use BI Publisher with APEX, what version of BIP do we need ?

    Can we have a 1 user license to develop the templates and use a runtime version for production ?

    Thanks

    Francis.

    P.S. Talking to Oracle Sales about APEX is sometimes complicated since they don’t really know the product.

  4. Mark Rittman Says:

    Hi Manish,

    I’ve seen BIP used on a few client sites now, my understanding is that most of them are very happy with BI Publisher. They often tend to manually add tags and so on to templates rather than rely on the wizards etc, but for general client-side reporting, it’s fine (and they’re generally much happier than when they used Reports).

    Francis – not sure about licensing, I think you’ll find that there are minimum named user levels, it’s usually around a minumum of five named users, but it can often be higher (10,25 or so) – unfortunately your Oracle sales rep is the best source of this information. In terms of version, I’ve been using BIP 10.1.3.2 and higher for ApEx integration, not sure about earlier releases.

    regards, Mark

  5. Sander Says:

    Hi Mark,

    Currently I am using BIP happily in an APEX application. Although my setup is a bit different, BIP indeed gives me great reports.
    But how does one formulate a query that returns a XML structure that contains multiple nested groups?

    For example something line this:

    bla
    bla

    1
    bla
    bla

    2
    bla
    bla

    Right now I use a stored procedure for this but a single query is perhaps (!?) more convenient.

    BTW: Have you ever tried to generated a Table Of Contents using a RTF file? My experience is that it always pops up at the bottom of the document and not were I put it: at the top of te document.

    Thx,
    Sander

  6. Sander Says:

    Oops, the XML of my example in the previous post is ‘interpreted’ …

    I meant something like this:
    G_INVOICE_LIST
    G_INVOICE
    TAG1
    TAG2
    G_INVOICE_LINE_LIST
    G_INVOICE_LINE
    LINENUMBER
    TAG3
    TAG4
    /G_INVOICE_LINE
    G_INVOICE_LINE
    LINENUMBER
    TAG3
    TAG4
    /G_INVOICE_LINE
    /G_INVOICE_LINE_LIST
    /G_INVOICE
    /G_INVOICE_LIST

    Sorry :)

    Thx,
    Sander

  7. Linda Says:

    I have a similar question as Sander. I am new to apex and bi pub. I see in apex that I can have multiple queries on the report query section in shared components. How can I link those 2 queries together using the join field as a master detail so that I can print a master/detail one per page for multiple masters sent from apex?

  8. Linda Says:

    I forgot to say that I was able to get this to work by joining the 2 tables in my query 1 and group by the id in query 1. I pulled the “master” columns out of the grouped table and placed them in the section above the table with the ID column. The mutliple details for each master then displayed in a table below the master. My concern though, is that in the next iteration of our product, there will be additional types of details that will also need to display. I did not think that I could join all of these in query 1 and do multiple group by’s. So I was wondering if I could have multiple queries instead that are linked to the master query within bi pub. thx, Linda

  9. Nathan Morgan Says:

    Francis, for APEX and BIP integration the minimum versions are APEX 10.3 with BIP 10.1.3.2
    “Application Express 3.0 introduced the ability to export a report region to PDF – essentially, printing a report.”
    ” The minimum required version of BI Publisher, that supports integration with Oracle Application Express, is version 10.1.3.2. XML Publisher was renamed to BI Publisher with the 10.1.3.2 release. XML Publisher 5.6.2 does not support integration with Oracle Application Express, it cannot be used as the PDF rendering engine.”
    from
    http://www.oracle.com/technology/products/database/application_express/html/configure_printing.html

Website Design & Build: tymedia.co.uk