APEX Users! Why not Integrate with BI Publisher 11g

Great news for Oracle Application Express (Oracle APEX) users! Integration with BI Publisher 11g has been certified, that means you can start taking advantage of BI Publisher 11g new features today!
I’m sure many of you have already heard or known that BI Publisher can be integrated with Oracle APEX, which is a rapid web application development tool for Oracle database. It allows you to design your APEX reports in a pixel perfect and highly flexible formatting and layout. This is another great example of two great and innovative Oracle products integrating seamlessly to compliment and complete to deliver solutions to address our customers problems.

Benefit for Oracle APEX users

If you haven’t integrated yet, here is a list of the benefits, why you want to integrate with BI Publisher for Oracle APEX users.

• Multiple Output Formats: Can generate PDF, Word, Excel, and HTML report outputs in a pixel perfect and high fidelity format.
• Highly Flexible Report Layout Design: Report Designers and Developers can use RTF templates providing significantly greater control over layout and formatting including page/section breaks, headers, footers, various types of charts, fonts type and formatting, custom conditional formatting, table of contents, etc.  
• Seamless: Print capabilities are fully integrated into Application Express.
• Support for non-western European fonts: Superior localization capabilities, including full support for CJK, BiDi, Unicode, and Multi Language Support.
• Single file Export/Import: Report layouts are part of your application definition so they are exported and imported along with the application.
• Supported by Oracle: With BI Publisher, you are using a supported Oracle product.

In addition to the above advantages, BI Publisher can also benefit your organization in many other ways.

• Extract from multiple data sources once, then Join and Aggregate/Calculate at BI Publisher server instance. - Move your complex business logic from your transaction database to reporting middleware servers
• Heterogeneous Database and other data source types Support
• Insight Driven Interactive Reporting – Click any data point to link and filter the data to gain insights instantly
• Reports Scheduling & Delivery – Out of box highly scalable reports scheduling and delivery
• Multiple reports delivery channels such as Email, Printer, Fax, FTP/HTTP, Content Management Server, Local File System
• Reports Bursting – Extract data once then split into multiple reports outputs and deliver to different delivery destinations
• Highly Scalable and Performance for reports data processing, reports outputs generation, and delivery

If you have already integrated with BI Publisher 10g you can just upgrade the BI Publisher to 11g. I have posted a post about the upgrade recently. If you haven’t, then you can start with 11g. I will show you a very simple configuration step and take you through the steps to create your first BI Publisher report integrated within Oracle APEX quickly.

Configuration for BI Publisher 11g Integration

First, you need to login to APEX Admin page. The URL is something like ‘http://<hostname>:<port>/apex/apex_admin’
And select ‘Instance Settings’ under ‘Manage Instance’ menu.

Go to ‘Report Printing’ section, select ‘Advanced (requires Oracle BI Publisher) and fill the other text fields with your BI Publisher server instance information. The ‘Print Server Script’ would be something like ‘/xmlpserver/convert’.

And, that’s it for the configuration! Now you can start creating reports with BI Publisher for your APEX applications.

Create a Report with BI Publisher

There are 3 steps create your APEX application report with BI Publisher.

1. Create Report Query
2. Design Report Layout
3. Upload Layout

Create Report Query

Login to your Oracle APEX workspace and open your application.
Open a Region that contains a report that  you want to create BI Publisher report for and copy the SQL query for the table inside the region.

Now go back to your Application home page.


Click ‘Shared Components’ and click ‘Report Queries’ under ‘Reports’ section to create a query.

Type ‘Report Query Name’ (anything is fine) then select a ‘Output Format’ from the list, and click ‘Next’

Paste the SQL query that you copied in the previous step, or you can use the Query Builder to build the query. Click ‘Next’ to continue.

Click ‘Create Report Query’ to finish.

Now you’ve created a Report Query. The next step is to design a report layout for the query.

Design Report Layout

You can design the report output with MS-Word, but before doing so you need to download a XML data to your local machine. BI Publisher provides you a MS-Word Add-ins called ‘Template Builder’, which helps you to design the reports and preview the result easier and quicker.
1. Download XML Data
Open the Report Query that has just been created and click ‘Download’ button under ‘Source Queries’ section.

You can save the generated XML data on your local machine.
2. Design with RTF Template (MS-Word)
This would be a regular step for BI Publisher users. Are you not familiar with RTF Template ? RTF Template is one of the report layout options that BI Publisher provides in addition to other options such as BI Publisher template, Excel template, Flex template (for Flash), PDF template, etc. You can use MS-Word to design the report layout from simple reports to very complex pixel perfect high fidelity reports. You can take a look at the BI Publisher Report Designer’s guide for the detail.

Upload Layout

Once you finish designing the report layout with the RTF template, you can upload it to Oracle APEX and associate it to the report query.
1. Login to Oracle APEX and go to your application. Go to ‘Shared Component’ and click ‘Report Layout’ and click ‘Create’ button.
2. Select ‘Named Column (RTF) and click ‘Next’

3. Type ‘Layout Name’ and select the RTF template, and click ‘Create Layout’ to finish.


Now, open the Report Query, which was created before and select the report layout that has just been created.

Now the report layout is associated with the report query. The next step is to add a button in the APEX application page so that the users can click to open the BI Publisher report.

Add Button to APEX Application Page to Open BI Publisher Report

You can simply create a new button and a branch to map the report query so that users can click the button to launch the BI Publisher report.

Create New Button

1. Select ‘Create Button’ from the menu

2. Fill the fields to create a button. In my example, I created ‘P1_PRINT’ button and set both the Label name and the Request name to be ‘Print’

Create New Branch

Before you create the Branch you need to get a URL to call the Report Query. You can get this by opening the Report Query and copy the URL.

Once you copied the URL you can go back to the application page Edit window, then select ‘Create Branch’ from the menu. You can keep the default values there and click ‘Next’.

Select ‘URL’ as ‘Branch Target’ and Paste the URL that you just copied in the previous step to the BIP report.

Change the Sequence to make it as the first option
And select the button that you just created at the previous step. In my example it’s ‘P1_PRINT’. Click ‘Create’ button to create the branch.

And that’s it! Now you’re ready to generate a BI Publisher report from your APEX application. When you run the application you should see the ‘Print’ button.

Click the button, you should see the report output generated by BI Publisher! 
 

Make Report Output Option Dynamic

With the above example the report output type is always hard-coded to the value that you set in the Report Query page. But you might want to let your users to select which report output type to be used. You can do so by creating an Item with ‘Select List’ type and associate that to the Report Query.
When you create the ‘Select List’ item here is a list of the values that you can use as actual values.
STATIC2:PDF;PDF,HTML;HTM,Word;RTF,Excel;XLS,XML;XML

• PDF – for PDF output
• HTM – for HTML output
• RTF – for RTF (MS-Word) output
• XLS – for Excel output
• XML – for XML output

Once you created the ‘Select List’ item, open the Report Query and select ‘Derive from Item’ as the ‘Output Format’ and specify the ‘Select List’ item that you just created.

Now when you run the application you should see the Select List and you can select one of the values to generate the report in the format.

Start Exploring BI Publisher 11g!

BI Publisher 11g integration for Oracle APEX users to expand its reporting capability to meet different reporting needs seamlessly and rapidly with minimal steps. Report designers can use MS-Word to design the report layout flexibly with its ease-of-use RTF template design option. And of course you can start taking advantage of many of the great BI Publisher 11g features to extract data from many different types of data sources, join and aggregate and calculate, and present the report in a highly insight driven interactive reporting view in addition to other static report output formats.