Reporting from Your BrowserBy David Peake
Publish high-fidelity business intelligence reports with Oracle Application Express.
Web applications are of limited value if users can view the information they need on the screen but have no way to put that information into a suitable format for sharing with others. If your application displays 15 records at a time out of a total of 50 but users can't e-mail all 50 in a printable attachment or distribute them in slick hard copy at a critical meeting . . . Houston, we have a problem! Oracle Application Express solves this dilemma by tightly integrating with Oracle Business Intelligence Publisher to provide high-fidelity printing capabilities.
With Oracle Business Intelligence Publisher installed, your Oracle Application Express application can generate reports in Adobe PDF, Microsoft Word, Microsoft Excel, and HTML formats from any report region. You can also define report templates and customize a report's graphical aspects by
These capabilities stem from Oracle Business Intelligence Publisher's ability to reference report layouts you develop in Microsoft Word with the Oracle Business Intelligence Publisher Word Template Builder plug-in. You then load the layout documents into Oracle Application Express and marry them with query results to produce the desired output.
This column takes you through the procedure for generating a report in PDF format that includes an Oracle Application Express report region and a pie chart. (Note that the report's Word-generated chart will not look like online charts created using Adobe Flash, because the charting engines are not identical.)
You can run through the steps in this column on the hosted instance of Oracle Application Express at apex.oracle.com. (You must request a free workspace to use this hosted instance.) The hosted instance includes Oracle Business Intelligence Publisher, which is required for the process described, but you must install Oracle Business Intelligence Publisher Desktop for Microsoft Windows, which adds Oracle Business Intelligence Publisher Word Template Builder to your Microsoft Word instance.
You can also run through this column's steps on a local instance of Oracle Application Express, but Oracle Business Intelligence Publisher must also be installed. All Oracle Business Intelligence Publisher downloads are available at oracle.com/technetwork/software/products/publishing .
Preliminary Table Setup
This column's example uses a table that holds sales order data. To generate the table, log in to an Oracle Application Express workspace and run md_pdf_orders_create.sql to create the MD_PDF_ORDERS table. To run the script,
1. Click SQL Workshop.
Steps for Generating the Example Report
The following steps describe how to build a report template and generate an example report.
Step 1: Create a report query. Define a report query within Oracle Application Express to incorporate the data you are including in the report:
1. In your Oracle Application Express workspace, select an existing application from the Application Builder menu. (You must start this process from within an application.)
SELECT ORDER_ID, ORDER_DATE, ORDER_MODE, ORDER_STATUS, CUSTOMER_NAME, SALES_REP, ORDER_TOTAL FROM MD_PDF_ORDERS
5. Click Next to continue to the wizard's Test Query Report page. If a report query incorporates bind variables, they appear as fields at the bottom of this page. The query must retrieve at least one record, so you must insert values for any bind variable(s). (The example doesn't use bind variable values.)
Step 2: Create a report template. Using Oracle Business Intelligence Publisher Word Template Builder, load the XML data into a Word document:
1. Open a new blank document in Microsoft Word. From the Oracle BI Publisher menu in Word, select Data -> Load Sample XML Data... , browse to the XML file you saved in Step 1, and click Open . Word should display a success message (but not the contents of the XML file). Click OK to close the message.
Step 3: Insert the table component. Use the Table wizard to add a table to the Word template:
1. From the Oracle BI Publisher menu, select Insert -> Table Wizard . Select the Table option in the wizard, and click Next .
Step 4: Insert the chart component, and finish the template. Add a pie chart showing customer orders to the template, and save the template as an RTF file:
1. Select Insert -> Chart from the Oracle BI Publisher menu to bring up the Chart dialog box.
Step 5: Upload the RTF template file. Relate the RTF template you saved in Step 4 to the report query you created in Oracle Application Express in Step 1:
1. Go back into Oracle Application Express to the same wizard page you left open at the end of Step 1.
It should look like the report in Figure 2.
4. While you are on this page, copy the contents of the URL field that appears at the bottom. The URL for the example is
5. Click Finish.
Finally, use the URL you just copied to add a link within the application that invokes the report. Most implementations use buttons, links on an item within a report region, or a navigation-item link. Once you've added the link, simply run the application, navigate to the appropriate form, and click the link to produce the PDF report.