APEX Users! Why not Integrate with BI Publisher 11g Today!

image

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.

apex_admin_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’.

image

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.

image

Now go back to your Application home page.

image

 

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

apex_integration

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

image

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.

image

Click ‘Create Report Query’ to finish.

image

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.

image

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’

image

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

image

 

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

apex_report_query

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

image

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’

image

 

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.

image

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’.

image

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

image

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.

image

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.

image

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

image 

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.

image

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.

image

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. 

Hope you find this post useful, and please share your experience, feedback, question at the comment section or tweet @bipublisher !

Useful Link:

Comments:

Why not? Let me think. How about, cost!

Posted by Matt Ball on April 18, 2011 at 06:41 PM MDT #

Hi Matt, BI Publisher is not a free product. However, it can compliment your APEX applications with great added values. For example, you can just use your MS-Word to design your report layout to achieve any type of reporting requirements commonly found in organizations very easily and quickly. Also, once the report templates are created it would be much easier to maintain the templates. If you need to change the color of table header you can do so just using the MS-Word menu so you can ask anybody who knows MS-Word to maintain the report. Well, I might have talked a lot here! :) But, why don't you try it out by downloading it from here ? http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html Downloading and trying it out is no cost! :) Hope you'll like it.

Posted by Kan Nishida on April 19, 2011 at 02:11 AM MDT #

Would be good if their was a template builder for a word processor other than MS word (i.e. open office for instance)

Posted by Bob on April 19, 2011 at 08:53 AM MDT #

Kan, BIP price is 46000USD/proc, it's very far from free, I think. BIP is well part in an Oracle Application pack. Regards, LL

Posted by LL on April 19, 2011 at 08:52 PM MDT #

As long as there is no entry level model for BI Publisher, most of our customers will look for alternatives. If you would just make the rendering engine of BI Publisher free of charge for usage by APEX only (without the administrative user-interface), people at least start to use the product and will automatically buy licenses for the product as their demands require it. It is analog to the strategy behind OracleXE, so why not do this for BI Publisher as well?

Posted by Niels de Bruijn on April 20, 2011 at 10:32 PM MDT #

I like BI but not all of the features described in the link above "11g New Features" are actually available. It seems that they removed some functionality when going from 10 to 11... why would they may make a new build but not include patches?

Posted by Frus on April 21, 2011 at 11:08 PM MDT #

I like BI but not all of the features described in the link above "11g New Features" are actually available. It seems that they removed some functionality when going from 10 to 11... why would they may make a new build but not include patches?

Posted by Frus on April 21, 2011 at 11:11 PM MDT #

Hi Frus, can you let me know which features from the '11g New Features' are not available ? They all should be available, but I can check.

Posted by Kan Nishida on April 22, 2011 at 02:45 AM MDT #

Why not? The BI Publisher license cost does not match with budgets the Customer profile whom use Oracle Apex. Oracle Apex is free. BI Publisher has a hefty enterprise level license. Why not introduce a free cut down version of BI Publisher like Niels de Bruijn has mentioned in a prior comment? Regards, Gareth

Posted by Gareth Roberts on April 28, 2011 at 07:50 AM MDT #

Yes, BI Publisher is not a free of product. But we believe that the TCO (total cost of ownership) including reports development, maintenance, is much lower than any other tools to deliver advanced reports, the reprots that require pixel-perfect layout and formatting especially. Please take a look at the benefit section for the detail above on the post. BTW, thanks for the comments around licensing, it helps us to improve our product delivery and strategy.

Posted by Kan Nishida on April 28, 2011 at 08:04 AM MDT #

Kan, Is there any way to store the Report Layout on the BI Publisher Server rather than in the Apex application? If you want to change a layout of a report, you have to re-release the apex application, which in our environments adds a whole load of regression testing. It would be better if you could load the report (and the sql) onto the BIP server and call it from the apex app. Gus..

Posted by Gussay on June 06, 2011 at 08:35 PM MDT #

Is there an entry level version of bi/XML publisher? 46k is just too much for clients that get hooked via oracle express -> Apex. Oracle express/apex is an easy sell with a clear upgrade path. Perhaps offer a 'no support' options (can't file SRs)? I doubt I could get any of my current clients to license anything that costs more than 5k. Jd

Posted by JD on June 11, 2011 at 07:18 AM MDT #

In order to call the reports stored in BIP catalog you need to call them through the URL or use Web Service. The out-of-the-box BIP/Apex integration is available only with the reports inside APEX model.

Posted by Kan Nishida on June 13, 2011 at 03:00 PM MDT #

Hi Nishida san,

Do I still need to set up the Oracle BI schemas first with RCU or I just simply follow your instructions above and fill out the hostname, port, etc to allow APEX to use BI Publisher right away?

I mean, we are about to install Oracle 11g BIEE to another unix node and I want to take advantage of the Oracle BI Publisher there...

Thanks,
Bill

Posted by Bill on August 11, 2011 at 02:55 AM MDT #

Hi Bill,

In order to install BIP 11g you need to setup the BI schema with RCU first.

Posted by Kan Nishida on August 11, 2011 at 03:10 AM MDT #

Thanks again for your prompt reply, Nishida-san.

I hope you don't mind me asking two more questions.

Today, we are installing an Oracle BIEE 11g on a Unix node for a separate application. I have requested the BI Publisher module to be installed there. Yet, we currently have our APEX 4 running on another Unix node.

Q1:
Now, I would like to integrate both BI Publisher and APEX 4. Would it be okay to install my OBIEE 11g MDS and BIPLATFORM schemas to the database where APEX is running? My strategy is to keep the OBIEE schemas and APEX schemas in one database. Does it sound okay to you?

Q2:
I know that RCU 11.1.1.5 supports Oracle database 10.2.0.4 or above but my APEX database is at 10.2.0.3. The RCU still allows me to ignore the warning, is it safe to proceed or am I better off with the database upgraded from 10.2.0.3 to 10.2.0.4?

Thanks,
Bill

Posted by Bill on August 12, 2011 at 01:22 AM MDT #

Q1: Yes, it can be in the same database.

Q2: Technically you might be able to get away with installing it in the 10.2.0.3 database environment, but it's not officially supported, which means it hasn't been tested so there might be some unknown issue. And if there was some issue you encountered Oracle won't be able to help... So I'd recommend you upgrade the database to the versions that are officially supported...

Posted by Kan Nishida on August 12, 2011 at 01:48 AM MDT #

Q1: Yes, it can be in the same database.

Q2: Technically you might be able to get away with installing it in the 10.2.0.3 database environment, but it's not officially supported, which means it hasn't been tested so there might be some unknown issue. And if there was some issue you encountered Oracle won't be able to help... So I'd recommend you upgrade the database to the versions that are officially supported...

Posted by Kan Nishida on August 12, 2011 at 01:49 AM MDT #

In another article, it doesn't seem to explicitly require RCU to be installed for BI Publisher to be functional with APEX. I am still a little bit confused.

http://www.oracle.com/technetwork/developer-tools/apex/application-express/configure-printing-093060.html#4.3

I see a few articles about installing and upgrading OBIEE but none that talks about configuring an installed BI Publisher with APEX that requires RCU and probably running OBIEE 11g Installer again.

If you don't mind...again...one more time, I would like to know how the RCU schemas I installed on the APEX databases be configured to the BI Publisher via Oracle BIEE 11g Installer. Or I simply plug in the parameters as follows:

After you complete the BI Publisher installation, you need to start OC4J. After starting OC4J, you should open the BI Publisher start page to verify that BI Publisher is up and running. If you are unsure what host address and port you need to use when configuring this in Application Express, please reference the host address and port shown in your browser's URL field after opening the BI Publisher start page.

4.3 Configuring Application Express to use BI Publisher

To use BI Publisher as your Report Server, first, access the Report Printing parameters:

Access the Administration Services home page
Select Manage Service
Select Instance Settings, under Manage Environment Settings
Click Report Printing to focus on just the Report Printing attributes

Now, specify the following attributes:

Oracle BI Publisher: Advanced Support
Print Server Protocol: HTTP or HTTPS (depends on your configuration)
Print Server Host Address: the hostname for the machine where you installed BI Publisher - e.g. myhost.mycompany.com
Print Server Port: the port given at the end of the BI Publisher installation - e.g. 9704
Print Server Script: /xmlpserver/convert

Posted by Bill on August 15, 2011 at 08:13 AM MDT #

I don't think u can do Report Bursting using the Report Query/Report Layout method. Has anybody got that to work for APEX (without using webservice to BI Pub)?

Posted by guest on February 16, 2012 at 07:37 AM MST #

Cost is to high for customers. Their should be a cheaper option for Oracle Express Users.

Posted by Alex Guedez on January 31, 2013 at 06:10 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today