Utilizing Advanced Printing in Oracle Application Express 3.0

Purpose

This tutorial shows you how to add a PDF report and Microsoft Word chart to an Oracle Application Express application.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Importing and Installing the Packaged Application
 Creating a Report and Master Detail Form
 Adding a PDF Report
 Adding a Microsoft Word Chart
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

The web and your browser are great at displaying content.  Where it falls short is when you want to print something or save it as a file to include in another document or as an email attachment.  If you want a real-time chart in your application, you use a Flash Chart.  If you need to submit a monthly status report to an external organization and want to include that information, you might want to include a Word Chart as output to make that external document easier to craft.  The output type (PDF, Word, Excel) is totally dependent on the end-users requirements.  With application design, you should display all the information in a useful way within the base application (viewed by the browser).  Then, if needed, you can craft some special reports that would output to pdf, word or excel.  These would be items that would need to exist outside of the application (such as invoices from email, hardcopy or mailed, performance reviews that are filed in someone's personnel file or charts of downloads for the last fiscal year).

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g.

2.

Install Oracle BI Publisher release 10.1.3.2.0. See document PDF Printing.

3.

Install Oracle BI Publisher desktop 10.1.3.2.0.

4.

Configure Oracle Application Express for Advanced Report Printing. See document PDF Printing.

5.

Download and unzip the apexpdf.zip into your working directory.

6.

Install Microsoft Word. Note that this OBE is only supported on Windows because of this requirement.

7.

SVG Plugin for your browser.

Note: Within your development environment, in order for Oracle BI Publisher Desktop to work, Microsoft Word needs to be installed.  For end users to run the resulting application, the Oracle BI Publisher Desktop is not needed. Just the appropriate application to open the file your application produces is needed; PDF reader, MS Word, MS Excel, etc.

Back to Topic List

Importing and Installing the Packaged Application

Before you begin to create the Report and Master Detail Form that you will base your report from, you need to import the packaged application and install the supporting objects. Perform the following steps:

1.

Enter the following URL to log in to Oracle Application Express (change the <hostname> to localhost, your specific hostname or apex.oracle.com).

http://<hostname>:8080/pls/apex

 

2.

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

 

3.

On the Workspace home page, click Application Builder .

 

4.

Click the Import button.

 

5. Select the Browse... button to search for the import file.

 

6.

Select the md_pdf_howto_installer.sql file from your working directory (i.e. c:\wkdir\apexhol) and click Open.

 

7.

Click Next.

 

8.

For File Import Confirmation, click Next to install the imported file.

 

9.

In the Install Application Wizard, select the appropriate schema and click Install.

 

10.

For Supporting Objects, make sure Yes is selected and click Next .

 

11.

Confirm your selections and click Install .

 

12.

The supporting objects have been installed. Click Install Summary.

 

13.

Review the objects that were installed. You are ready to create your Master Detail Form. Click the Application Builder breadcrumb.

 

Back to Topic List

Creating a Report and Master Detail Form

To create the report and master detail form, which will be used to launch your advanced reports, perform the following steps:

1.

Select the How To Create a Master-Detail PDF Report application

 

2.

Click Create Page .

 

3.

Select Form and click Next .

 

4.

Select Master Detail Form and click Next

 

5.

Select MD_PDF_ORDERS (table) for the Table/View Name and select the  icon to select all the columns.

 

6.

Click Next.

 

7.

Select MD_PDF_ORDERS_ITEMS (table) for the Table/View Name and select the  icon to select all the columns.

 

8.

Click Next.

 

9.

For the Primary Key Column ORDER_ID, accept the default, Existing Trigger and click Next .

 

10.

For the Primary Key Column ORDER_ITEM_ID, accept the default, Existing Trigger and click Finish .

 

11.

Click Create . Note that the wizard adds two pages to the application, a report on the master table and a master detail form that references both tables.

 

12.

To execute the Master Detail form, click Run Page.

 

13.

The PDF Orders report appears. Click the  icon for one of the orders.

 

14.

The Master Detail page appears. You will begin by creating a report that incorporates all the order information in a particular format. Scroll to the bottom of the page.

 

15.

Select the Application link in the developer toolbar.

 

Back to Topic List

Adding a PDF Report

You could enable region printing on the tabular form to display just the order items records for the selected order. To create a report that incorporates all the order information, along with the Order Items, you will need create a report query and report layout. The report query identifies the data to be extracted from the application. The report layout identifies where and how it should be displayed. The tasks to add these components include the following:

 Create a Report Query
 Create a RTF Template
 Upload the RTF File
 Link the PDF Report to the Application

Back to Topic List

Create a Report Query

The report query identifes the data to be retrieved both from the database and from session state. To create a report query, perform the following

1.

Click Shared Components.

 

2.

Under Reports, click Report Queries .

 

3.

Click Create.

 

4.

Enter order_details for the Name and enter the following SQL Query and click Next. Note: :P3_ORDER_ID refers to the selected order for the current page.

                               
SELECT LINE_ITEM_ID,
       QUANTITY,
       ORDER_ITEM_ID,
       PRODUCT_NAME,
       to_char(UNIT_PRICE,'$9,999.99') unit_price
  FROM MD_PDF_ORDER_ITEMS
 WHERE ORDER_ID = :P
                                
3_ORDER_ID
                              
                            

 

5. For XML to be generated by a query, the query must retrieve at least one record. Your query contains a bind variable so you must enter a valid value to be used when your XML is generated. To ensure that records are returned, you need to test your query. For Test Query, enter 2359 for :P3_ORDER_ID and click Test Query .

 

6.

Five records should display for that Order ID. Click Close.

 

7.

Click Next.

 

8.

Now that you have the order item data, you need to include the session state for the items contained on page 3. This is used to display the order details. Click the up arrow next to Select Items.

 

9.

Select P3_CUSTOMER_NAME .

 

10.

Click Add.

 

11.

Repeat steps 8-10 for the following items and click Next.

                               
P3_ORDER_DATE
P3_ORDER_ID
P3_ORDER_MODE
P3_ORDER_STATUS
P3_ORDER_TOTAL
P3_SALES_REP
                            

 

12.

For XML Structure, select Advanced (include session state information) and the click Download XML button.

 

13.

Select Save to Disk and click OK.

 

14.

Enter order_details.xml for the File Name and click Save.

 

15.

At this point, you could stay on this page and go use the BI Publisher Desktop Add-in to create your RTF template and then upload it. Alternatively, you can save the query and edit it later to reference the completed template. In this case, you will save the report query. Click Next.

 

16.

Notice the URL that displays. This is the URL that will be used to call this report from within your application. Click Finish.

 

17.

The query was created. Click the Shared Components breadcrumb.

 

Back to List

Create a RTF Template

To edit the XML produced from your report query, you need the Oracle BI Publisher Desktop release 10.1.3.2.0 (see prerequisites). In this section, you load the XML details in Microsoft Word, insert fields and include line items and save the changes as an RTF file. Perform the following steps:

1.

Open Microsoft Word.

 

2.

Select Oracle BI Publisher > Data > Load Sample XML Data .

 

3.

Select order_details.xml and click Open.

 

4.

The file was loaded successfully. Click OK.

 

5.

Note that nothing appears on the page. You want to add the order columns to your report. Select Oracle BI Publisher > Insert > Field .

 

6. Select P3 Customer Name and click Insert .

 

7.

Notice that the field appears in the document. Click at the end of the field and press the Enter key to go to the next line. Then in the Field window, select P3 Order Date and click Insert .

 

8.

Repeat the previous step for the following fields:

                               
P3 Order ID
P3 Order Mode
P3 Order Status
P3 Order Total
P3 Sales Rep
                            

Once you have added all the necessary fields. Click Close in the Field window.

 

9.

Add labels to the fields as seen in the screenshot.

 

10.

You also want to add a table with all the line items for the order in a table. Insert a few blank lines below your order details. Select Oracle BI Publisher > Insert > Table Wizard .

 

11.

Accept the default, Table , and click Next .

 

12.

For Grouping Field, select DOCUMENT/REGION/ROWSET/ROW and click Next .

 

13.

Select the  icon to move all the fields to the right side.

 

14.

Click Next.

 

15.

You do not need to make any grouping selections since your report will select just one order. Click Next.

 

16.

For Sort By, select Line Item Id and click Next .

 

17.

For Labels, change Line Item Id to Line Item and Order Item Id to Order Item and click Finish.

 

18.

Your document should look like the screenshot.

 

19.

To add a header and footer, select View > Header and Footer .

 

20.

Add My Order Report to the header and change the font and size, then click Close from the header and footer toolbar.

 

21.

You want to save the document as an RTF file. Select File > Save As.

 

22.

Select Rich Text Format (*.rtf) for Save as type and enter order_details.rtf for the File Name and click Save.

 

23.

Exit Microsoft Word.

 

Back to List

Upload the RTF File

Once you complete your report layout template, you need to upload it to your Application and associate it with your report query. Perform the following steps:

1.

Switch to your Oracle Application Express window and navigate to your Application.

 

2.

Click Shared Components.

 

3.

Under Reports, click Report Layouts .

 

4.

The Report Layouts page appears. Click Create.

 

5.

For Layout Type, select Named Columns (RTF) and click Next .

 

6. For Layout Name, enter Order details and click Browse...

 

7.

select order_details.rtf and click Open.

 

8.

Click Create Layout .

 

9.

Now you can associate the report layout and report query. Click the Shared Components breadbrumb.

 

10.

Under Reports, click Report Queries .

 

11.

Select the order_details report query.

 

12.

Under Report Query Attributes, for Report Layout, select order_details . Note the following URL that is automatically populated and then click Apply Changes.

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

 

13.

You completed loading the RTF template and associating it with the Report Query you created earlier in this tutorial. Now you can link the report to your application. Click the Application breadcrumb.

 

Back to List

Link the PDF Report to the Application

In this section, you link the PDF report to the application by creating a button and a branch. Perform the following steps:

1.

Select the Master Detail page in your application.

 

2.

Under Buttons, click the Create icon.

 

3.

For Button Region, select Edit Md Pdf Orders and click Next .

 

4.

This selection positions the button in the top (or master) region. For Button Position, accept the default, Create a button in a region position , and click Next .

 

5. Enter PRINT for the button name, enter Print PDF of Order for the label, make sure Submit Page and Redirect to URL for Action is selected and click Next.

 

6.

Accept the default and click Next.

 

7.

To position the button to the right of the Apply Changes button, select Region Template Position #CREATE# for Position and click Next.

 

8.

For Branching, accept the default (leave this option blank) and click Next .

 

9.

You do not want the print button to display when you are creating a new order so you need to add a condition. Select Value of Item in Expression 1 Is NOT NULL for the Condition Type and enter P3_ORDER_ID for Expression 1. Then click Create Button.

 

10.

Under Branches for Page Processing, click the Create icon.

 

11.

Accept the defaults and click Next.

 

12.

Select URL for Branch Target and enter the following URL in the URL Target field. Then click Next.

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

 

13.

For Branch Conditions, enter .5 for the Sequence and select PRINT (Print PDF of Order) for When Button Pressed. The sequence is important so that the new branch is considered before the default branch on the page. If the sequence falls after the existing branches, the new branch is never invoked. Then click Create Branch.

 

14 .

Press the left arrow to navigate to the Md Pdf Orders page.

 

15 .

Click the Run Page 2 icon to run the page.

 

16 .

Click the  icon next to one of the orders.

 

17 .

Click the Print PDF of Order button.

 

18 .

You can either Open the file with Adobe Acrobat or Save it to a file and then open the file later. Click OK.

 

19 .

The PDF report shown is a result of both your report query and report layout.

 

Back to List

Adding a Microsoft Word Chart

With Oracle Application Express, you can include regions that display charts. If you want to include a chart that can be written to a file or printed nicely, you can include a Microsoft Word or PDF chart. You will follow similar steps as you did with the PDF report.

 Add a Flash Chart Region
 Create a Report Query
 Create a RTF Template
 Upload the RTF File
 Link the Word Chart into the Application

Back to Topic List

Add a Flash Chart Region

Before you create your Word Chart, you will add a region with a chart to your application. You will create a flash chart with a similar query to what you will use for the Word Chart. Perform the following steps:

1.

Navigate to page 2 Md Pdf Orders in your Application page. Under regions, Select the Create icon.

 

2.

Select Chart for type of region and click Next.

 

3.

Select Flash Chart and click Next.

 

4.

Enter Sales by Sales Rep for the Title and click Next.

 

5.

Select 2D Pie for Chart Type. Then click Update.

 

6.

Notice that the preview changed to a pie chart. Click Next.

 

7.

Enter the following SQL query and click Create Region.

                               
                                 
SELECT null link,
       nvl(SALES_REP, 'No Rep') SALES_REP,
       ORDER_TOTAL
  FROM MD_PDF_ORDERS
                              
                            

 

8.

Click the run page icon.

 

9.

Your chart has been added to the page. You will now create the Word Chart and create a button in this region to run the Word Chart.

 

Back to List

Create a Report Query

The report query for the Word chart is simplier than the last report query because there is no need to reference session state. Perform the following steps:

1.

Navigate back to your Application page and click Shared Components.

 

2.

Under Reports, click Report Queries .

 

3.

Click Create.

 

4.

Enter sales_pie_chart for the Name and enter the following SQL Query and click Next.

                               
SELECT nvl(SALES_REP, 'No Rep') SALES_REP,
       ORDER_TOTAL
  FROM MD_PDF_ORDERS
                            

 

5.

Unlike the previous Report Query, there is no need to test the query because there are no bind variables that need to be populated. Click Next.

 

6.

There are no session state variables needed, click Next.

 

7.

For XML Structure, you can keep the default of Standard and then click Download XML. Note that you can keep the default Standard because you don't need to access session state information.

 

8.

Select Save and click OK.

 

9.

Keep the default of sales_pie_chart.xml for the File Name and click Save.

Rather than saving the Report Query now, as you did when creating the PDF report, you can leave your browser on this page. In the next section of this tutorial, you will create your RTF template and load it, using this page, to create your Report Layout.

 

Back to List

Create a RTF Template

In this section, you again load the XML details into Microsoft Word but will create a pie chart. Then you will save the changes as an RTF file. Perform the following steps:

1.

Open Microsoft Word.

 

2.

Select Oracle BI Publisher > Data > Load Sample XML Data .

 

3.

Select sales_pie_chart.xml and click Open.

 

4.

The file was loaded successfully. Click OK.

 

5.

Note that nothing appears on the page. Select Oracle BI Publisher > Insert > Chart .

 

6. From the left, under Data, you see the end of the data hierarchy; Row with Sales Rep and Order Total below. Drag Sales Rep onto the Labels field (bottom center, below the large white region).

 

7.

Drag the Order Total onto the Values field (top center). For Aggregation, you will leave the default of Sum.

 

8.

For Title (on the top right), enter Sales by Sales Rep.

 

9.

For Type, select Pie Chart.

 

10.

For Style, you can leave the default or select a color scheme (the example application uses Glass). Then click OK.

 

11.

You will immediately see a preview of your chart. Select File > Save As.

 

12.

Select Rich Text Format (*.rtf) for Save as type and enter sales_pie_chart.rtf for the File Name and click Save.

 

13.

Exit Microsoft Word.

 

Back to List

Upload the RTF File

You can now switch back to Oracle Application Express and upload the RTF file on the Create Report Query Layout page (which you left open previously) creating both the Report Query and Report Layout at the same time. Perform the following steps:

1.

Switch to your Oracle Application Express window.

 

2.

Under Step 3 Upload RTF file, click Browse.

 

3.

Select sales_pie_chart.rtf and click Open.

 

4.

Click Next.

 

5.

For Output Format, select Word. Note that you can use the Test button to preview the resulting output in any select Output Format. Copy the URL. This will be the target of a branch you will add in the next section to call the chart. Click Finish.

 

7.

Click the Application breadcrumb.

 

Back to List

Link the Word Chart into the Application

In this section, you link the Chart to the application by creating a button and a branch. Perform the following steps:

1.

Select the Md Pdf Orders page in your application.

 

2.

Under Buttons, click the Create icon.

 

3.

For Button Region, select Sales by Sales Rep and click Next .

 

4.

For Button Position, accept the default, Create a button in a region position , and click Next .

 

5. Enter PIE CHART for the button name, enter Pie Chart of Orders for the label. For Action, keep the default of Redirect to URL without submitting page and click Next. Note: In this case, you do not need to create a branch since you do not need to write any session state for the report query to read.

 

6.

Accept the default and click Next.

 

7.

To position the button in the upper right, above the report, select Region Template Position #CREATE# for Position and click Next.

 

8.

Select URL for the Target. For URL Target, paste in the URL copied from the Create Report Query Wizard (which should look as follows). Then click Create Button.

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

 

9 .

Click the Run Page icon to run the page.

 

10 .

Click the Pie Chart of Orders button.

 

11 .

Select Open with Microsoft Word for Windows and click OK.

 

12 .

You see your chart displayed within Microsoft Word. This can now be printed, saved, or emailed as an attachment.

 

Back to List

Summary

In this tutorial, you learned how to:

 Create a Report and Master Detail Form
 Create a Report Query for a PDF Report and Word Chart
 Create an RTF Template for a PDF Report and Word Chart in Microsoft Word
 Load the RTF Template as the Report Layout
 Link the PDF Report and Word Chart to a page in your application

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document