As Published In January/February 2008

DEVELOPER: Browser-Based

Reporting from Your Browser

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.

Overview

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 

• Adding headers, footers, colors, and page sizing

• Inserting logos

• Fully controlling pagination and other breaks

• Embedding charts

• Generating standard forms

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.
2. Click SQL Scripts.
3. Click Upload.
4. Click Browse ; select the md_pdf_orders_create.sql script, available with the sample code for this column at o18browser.zip; click Open; and click Upload.
5. On the resulting page, click the md_pdf_orders_create.sql icon.
6. Click Run , and then click Run on the Confirmation screen.

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.)
2. From the application's Shared Components menu, click Reports -> Report Queries .
3. Click Create.
4. Enter Orders in the Name field, and copy the following into the SQL Query box: 

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.)
6. Click Test Query to ensure that at least one record is retrieved. (You should see five retrieved records for this example.) Close the results screen.
7. Click Next . If you need to include additional session-state values in a report (not necessary for this example), you select them on this page from the list of values and then click Add.
8. Click Next to bring up the Create Report Query Layout page. Select Standard from the XML Structure list, click Download XML , and save the XML file to disk. (The Advanced option lets you include additional information such as session-state variables and application items, which are not required for this example.) Don't exit from the Report Queries wizard, because you must return to this same page in Step 5.

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.
2. If you included additional elements in your XML structure in Step 1, you can incorporate them by selecting Insert -> Field from the Oracle BI Publisher menu. (Otherwise, proceed to Step 3.) In the Field dialog box, select each item you want and click Insert . Then click Close . All the fields you selected appear in the document in a string, so separate the fields and add labels, boxes, and so on to suit your needs.

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 .
2. For Grouping Field , select ROWSET/ROW , and click Next.
3. The page that appears asks you to select the fields you want to show in the report. Select all the items, and then click Finish . You should see the unpopulated table structure at the top of the Word document. (You've skipped the wizard's last two pages, which let you specify, respectively, fields to group and sort on and labels for each field. They're not necessary for this example.)

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.
2. From the Row folder in the Data box on the left, drag Order Total to the Values box and Customer Name to the Labels box.
3. Select the Sum option for Aggregation , select the Pie Chart option for Type , and enter Orders by Customer in the Title box.
4. Click OK to both generate the "Orders by Customer" pie chart and insert it into the template. Assuming that you did not insert any additional XML elements at the end of Step 1, your template should look like Figure 1.
5. Optionally, customize the report by using standard Word capabilities to add elements such as a company logo, a watermark, or a header and/or a footer.
6. Select File -> Save As , enter Orders in the File name box, select the Rich Text Format (*.rtf) option for Save as type , and click Save. 

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.
2. Click the Browse button to find the Orders.rtf file you saved in Step 4. Click Next.
3. On the Create Shared Query page, select PDF as the Output Format . Click the Test button to preview the resulting PDF output.

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 

f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=Orders 

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.

Figure 1: Microsoft Word document displaying the report template

Figure 2: Finished report in PDF format

Conclusion

Often when organizations implement new applications, printing comes last in the list of concerns. Yet even in the internet age, printed reports remain a key decision-making tool. With the tight integration of Oracle Application Express and Oracle Business Intelligence Publisher, requirements for generating attractive reports in PDF, Word, Excel, and HTML formats can be fully satisfied for Web applications.

Send us your comments