How-To Document

Integrating Oracle Application Express (formerly HTML DB) and Oracle Portal with the OmniPortlet:
Publishing an Application Express Report as a Chart on a Portal Page


Date: 15-NOV-2003

After completing this How-To, you should be able to understand:

  • How to create a chart in Oracle Portal based on data from an HTML DB Report

Introduction

In Oracle Portal, the OmniPortlet is a powerful mechanism to capture and display data from a variety of external sources. One type of source which the OmniPortlet works with is XML. Since Oracle HTML DB can automatically format any report as an XML document, it is possible to integrate it with Oracle Portal by setting up an OmniPortet.

Software Requirements

Steps Required

In this example, we will create a chart in Oracle Portal using an OmniPortlet.

Steps in Oracle HTML DB

  • Create a public application in HTML DB (See How to make an HTML DB Application Public for details)
  • On a new page with no other regions, create a new Region
  • Select Report and click Next
  • Select SQL Report and click Next
  • Give it any title you wish and click Next
  • For the SQL Query, enter:
    SELECT b.product_name, a.product_id, SUM(a.quantity * b.list_price)
                  FROM DEMO_ORDER_ITEMS a, DEMO_PRODUCT_INFO b
                  WHERE a.product_id = b.product_id
                  GROUP BY b.product_name, a.product_id
    

    Note: The demo_order_items & demo_product_info table are installed as part of the Sample Application, in the schema that your workspace is based on. If you have removed this application or the tables, you can reinstall it by clicking on the Home icon, and then Review Demonstration Applications in the Tasks region in HTML DB.

  • Select export: XML for the Report Template and click Create Region.
  • Once you have created the report, click on the " Q" next to it, and set the Heading Type to Custom.
  • You must rename each column name so that you do not use any spaces or underscores. For example: Product ID becomes ProductID
  • Save your changes by clicking on Apply Changes.
  • You may want to test the XML report to see if it is error-free before proceeding to the Oracle Portal steps. This can be done simply be running the page on which the report is located.

Steps in Oracle Portal

  • Sign on to Oracle Portal as a user with privileges to create pages and assemble content.
  • Create a new page or identify an existing page where you would like to display the OmniPortlet.
  • On that page, add a new Portlet by selecting the Add Portlet icon.
  • Locate the portlet called OmniPortlet by entering OmniPortlet in the Search box and clicking on Go.
  • Once you have found it, click on the name OmniPortlet to move a copy over to the Selected Portlets box and then click on OK.
  • Click on Define your OmniPortlet.
  • Select XML for the data source of the OmniPortlet and click on Next to continue.
  • In the XML URL field, paste the URL to your HTML DB Application.

    For example: If your HTML DB application is on a server called myserver.com, the application ID is 100, and the page which your XML report is on is 1, and your DAD is /pls/htmldb/, then you would enter: http://myserver.com/pls/htmldb/f?p=100:1

  • On the next page, you do not need to set any parameters. However, note that the column names should be the same as the ones you specified in your HTML DB report. Click on Next to continue.
  • You can optionally add Header and Footer text on this page. You can also select different styles of reports - tabular, chart, etc. For this example, select Chart and then click on Next.
  • Select the type of Chart you wish to display. Also, select a position in which the Legend will appear in relation to the chart.
  • Under the Column Layout section, set the columns as: Group: <None> Category: ProductName Value: Sales
  • Click on Finish to complete the OmniPortlet configuration process.
  • At this point, you should see the data from HTML DB rendered in Oracle Portal as illustrated below in Figure 1.

  
Figure 1: An example of a chart rendered in Oracle Portal using an OmniPortlet, which takes an XML feed from Oracle HTML DB

Additional Resources

Discuss this how-to in the Oracle HTML DB Discussion Forum